Thursday, December 13, 2012

Powershell script to Refresh Excel Data

The following sript opends Excel files one by one, refreshes the data, saves the file back and closes the file.

 Function RefreshExcelData($Excel){
  $App = new-object -comobject excel.application
  $App.Workbooks.Open($Excel)
  $App.Visible = $true
  $App.ActiveWorkbook.Refreshall()
  $App.Save()
  $App.Quit()
  $username = $env:username
  Remove-Item "C:\Users\$username\Documents\RESUME.XLW"
 }
 CLEAR-HOST
 $Host.UI.RawUI.ForegroundColor = "yellow"
 $Excel = 'R:\folder\file1.xlsx'
 RefreshExcelData $Excel
 $Excel = 'R:\folder\file2.xlsx'
 RefreshExcelData $Excel
 $Excel = 'R:\folder\file3.xlsx'
 RefreshExcelData $Excel
 $Host.UI.RawUI.ForegroundColor = "white"

If you find this script useful please donate generously.

Thursday, December 6, 2012

Powershell script to ftp MySQL backup file and restore it locally.

The following Powershell script can be customised and scheduled (
 powershell.exe -executionpolicy bypass -file E:\scripts\dbrestore.ps1
) on your server:

 #########################################################################
 # Purpose:  FTP down MySQL backup file and restore it locally.
 # Requirements: 1) Source backup file automatically created daily.
 #      The name is generated as YYYYMMDD_dbname.sql
 #      and located in /dbbackup folder.
 #     2) Indy.Sockets.dll exits on the destination system.
 #      Lets presume that it is located in e:\scripts
 #########################################################################

 function Open-FTPConnection($ftphost, $username, $password) {
  [void][Reflection.Assembly]::LoadFrom("e:\scripts\Indy.Sockets.dll");
   $ftp = new-object Indy.Sockets.FTP;
   $ftp.Disconnect();
   $ftp.Host = $ftphost;
   $ftp.Username = $username;
   $ftp.Password = $password;
   $ftp.Connect();
   $ftp.Passive=$true;
   return $ftp;
  }
 
 function Close-FTPConnection($ftp) {
  $ftp.Disconnect();
  }
 
 function Draw-DirectoryContents($ftp) {
  "Listing directory contents ..";
  $ls = new-object System.Collections.Specialized.StringCollection;
  $ftp.List($ls, "", $true);
  foreach($file in $ls){
   $file;
   }
   Get-FTPCurrentLocation $ftp
  "Complete";
  }
 
 function Get-FTPCurrentLocation($ftp) {
  return $ftp.RetrieveCurrentDir();
  }
 
 function Set-ParentLocation($ftp) {
  "Changing to Parent Directory ..";
  $ftp.ChangeDirUp();
  Draw-DirectoryContents;
  }
 
 function Download-FTPFile($ftp, $sourceFileName, $targetDir) {
  "Downloading {0} into {1}.." -f $sourceFileName, $targetDir;
  $ftp.Get($sourceFileName, ($targetDir + $sourceFileName), $true, $false);
  "Downloading of {0} into {1} is complete" -f $sourceFileName,
  [Environment]::CurrentDirectory;
  }
 
 function Upload-FTPFile($ftp, $sourceFileName, $targetDir) {
  "Uploading {0} into {1}.." -f $sourceFileName, $targetDir;
  $ftp.Put($sourceFileName, ($targetDir + $sourceFileName), $false);
  "Uploading of {0} into {1} is complete" -f $sourceFileName, $targetDir;
  }
 
 function Process-FTPItem($ftp, [string]$sel) {
   [string[]]$fields=[Regex]::Split($sel, " +");
   $startField=8; #the file/directory name starts after 8 fields
   [string]$name="";
 
  for ($field=$startField; $field -lt $fields.Length; $field++) {
    if ($field -eq $startField) {
    $temp = "";
    } else {
    $temp = " ";
    }
    $name += $temp + $fields[$field];
    #add aditional space for name split into multiple fields
   }
 
  if ($sel[0] -eq 'd') { #directory
    "Changing directoy to {0} .." -f $name;
    $ftp.ChangeDir($name);
    Draw-DirectoryContents $ftp #redraw contents after changing directory
  } else {
    if ($sel[0] -eq '-') { #plain file has '-' as first character
    return $name; #return the name of file to download
    }
  }
  }
 
 function datetime{
  $year = (Get-Date).Year;
  $month = Get-Date -format MM;
  $day = Get-Date -format dd;
  $hour = Get-Date -format hh;
  $minute = Get-Date -format mm;
  $second = Get-Date -format ss;
  return "$day/$month/$year $hour`:$minute`:$second";
 }
 $year = (Get-Date).Year;
 $month = Get-Date -format MM;
 $day = Get-Date -format dd;
 $hour = Get-Date -format hh;
 $minute = Get-Date -format mm;
 $second = Get-Date -format ss;
 CLEAR-HOST
 $Host.UI.RawUI.ForegroundColor = "yellow"
 [void][reflection.assembly]::LoadWithPartialName("microsoft.visualbasic")
 $date = datetime;
 "** Start process $date **";
 write-host "`r";
 $theDate = "$year$month$day";
 $word_to_delete = "_dbname.sql";
 $file = "/dbbackup/$theDate$word_to_delete";
 $dir = "e:\db_backup"; #Destination folder
 $from = "$dir\dbbackup";
 $to = "$dir\$theDate`_$hour$minute$second";
 $out = "$to\$theDate$word_to_delete";
 $procedures = "/dbbackup/curls_procedures_$year-$month-$day.sql"
 if (!(Test-Path -path "$dir\$theDate*")){
  if ((Test-Path -path "$dir\dbbackup")){
   Remove-Item "$dir\dbbackup";
  }
  New-Item "$dir\dbbackup" -type directory;
  $f = Open-FTPConnection "www.sitename.com" "ftpUserName" "ftpPassword";
  Download-FTPFile $f $file ($dir);
  Download-FTPFile $f $procedures ($dir);
  Close-FTPConnection $f;
  Rename-Item $from $to;
  #Global replace of the keyword
  (Get-Content $out) | Foreach-Object {$_ -replace "sourceDBname", "destinationDBname"} | Set-Content $out;
  Write-Host $to;
  Write-Host $from;
 } else {
  "File already downloaded!";
  "Processing local restore ...";
  $items = Get-ChildItem -Path "$dir\$theDate*"
  foreach ($item in $items){
   $val = $item.Attributes
     if ($item.Attributes -Like "Directory*"){
    $theDir = $item.Name
     }
  }
  $theDir = "$dir\$theDir"
  $fileEntries = [IO.Directory]::GetFiles($theDir);
  foreach($filename in $fileEntries){
   $file = $filename;
  }
 }

 $mysql = "mysql -u dbUserName --password=dbPassword destinationDBname --execute=""SELECT CONCAT('DROP TABLE ',GROUP_CONCAT(TABLE_NAME),';') AS stmt FROM information_schema.TABLES WHERE  TABLE_SCHEMA = 'destinationDBname' INTO OUTFILE 'e:\\db_backup\\dropAllTables.sql'"""
 Write-Host $mysql;
 & cmd /c $mysql;
 $mysql = "mysql -u dbUserName --password=dbPassword destinationDBname < e:\db_backup\dropAllTables.sql"
 Write-Host $mysql;
 & cmd /c $mysql;
 $mysql = "mysql -u dbUserName --password=dbPassword destinationDBname < $out"
 Write-Host $mysql;
 write-host "`r";
 $date = datetime;
 "** End of process $date **";

If you find this script useful please donate generously.

Tuesday, June 5, 2012

Reading live XML file to the SQL Server database by combining consumed and unconsumed XML data.

Task: Query the XML (LicenseKeys.xml) file from MS SQL Server database.


XML file fragment:




<?xml version="1.0" encoding="utf-8"?>
<licenseBatch xmlns="http://site/schemas/licensekey">
                <license batchID="1" batchStatus="Active" expireDays="60" admin="WESTERN_SYDNEY\rsakhamuri" adminEmail="Ramya.Sakhamuri@site.com" requester="rsakhamuri" requesterEmail="Ramya.Sakhamuri@site.com" creationTimeStamp="2010-02-04T15:46:02.525392+11:00" activationType="OfflinePossible">
                                <note>test</note>
                                <licenseKey sequence="1" key="3fea9da9-28b1-47f1-82ff-4198c96330d7" owner="ramya" ownerEmail="" status="ReActivated" lastModified="2010-02-04T05:03:40.663936Z" noOfActivation="3" maxActivationCount="50">
                                                <activator IP="127.0.0.1" userName="WESTERN_SYDNEY\rsakhamuri" email="" activationTimeStamp="2010-02-04T15:48:57.9026368+11:00">rams</activator>
                                                <activator IP="127.0.0.1" userName="WESTERN_SYDNEY\rsakhamuri" email="" activationTimeStamp="2010-02-04T15:49:11.5434364+11:00">rams</activator>
                                                <activator IP="172.24.108.48" userName="WESTERN_SYDNEY\rsakhamuri" email="ramya.sakhamuri@site.com" activationTimeStamp="2010-02-04T16:03:40.663936+11:00">ramya</activator>
                                </licenseKey>
                                <licenseKey sequence="2" key="acbebeeb-af3e-40d1-a9ca-f03dd0a5cce4" owner="" ownerEmail="" status="InActive" lastModified="2010-02-04T15:46:02.525392+11:00" noOfActivation="0" maxActivationCount="50" />
                                <licenseKey sequence="3" key="8efcc7c4-c367-4316-b8b3-8f5c8cfc244c" owner="" ownerEmail="" status="InActive" lastModified="2010-02-04T15:46:02.525392+11:00" noOfActivation="0" maxActivationCount="50" />
                                <licenseKey sequence="4" key="8b6e7919-7107-4025-bddb-5a746e1b2691" owner="" ownerEmail="" status="InActive" lastModified="2010-02-04T15:46:02.525392+11:00" noOfActivation="0" maxActivationCount="50" />
                                <licenseKey sequence="5" key="c5cf95d8-4b5a-458b-8704-4e8e569e2e07" owner="" ownerEmail="" status="InActive" lastModified="2010-02-04T15:46:02.525392+11:00" noOfActivation="0" maxActivationCount="50" />
                </license>
                <license batchID="2" batchStatus="Active" expireDays="180" admin="WESTERN_SYDNEY\rsakhamuri" adminEmail="Ramya.Sakhamuri@site.com" requester="rsakhamuri" requesterEmail="Ramya.Sakhamuri@site.com" creationTimeStamp="2010-04-20T18:30:33.998494+10:00" activationType="OnlineOnly">
                                <note>for OTEN users 20-04-2010</note>
                                <licenseKey sequence="1" key="96af9492-0f01-4689-9443-2b098a75f08b" owner="Julie Esson" ownerEmail="ramya.sakhamuri@site.com" status="ReActivated" lastModified="2010-04-21T06:21:53.4190295Z" noOfActivation="2" maxActivationCount="50">
                                                <activator IP="172.24.108.190" userName="WESTERN_SYDNEY\rsakhamuri" email="ramya.sakhamuri@site.com" activationTimeStamp="2010-04-21T08:44:23.8095566+10:00">ramya sakhamuri</activator>
                                                <activator IP="172.24.111.148" userName="SYDNEY\JEsson" email="julie.esson@site.com" activationTimeStamp="2010-04-21T16:21:53.4190295+10:00">Julie Esson</activator>
                                </licenseKey>
                                <licenseKey sequence="2" key="9c5e56b7-8733-46df-9f2d-8b91a57fd805" owner="Kuan Sin" ownerEmail="kuan.sin@site.com" status="Active" lastModified="2010-04-21T06:33:50.4079932Z" noOfActivation="1" maxActivationCount="50">
                                                <activator IP="172.24.107.251" userName="WESTERN_SYDNEY\ksin" email="kuan.sin@site.com" activationTimeStamp="2010-04-21T16:33:50.4079932+10:00">Kuan Sin</activator>
                                </licenseKey>
                                <licenseKey sequence="3" key="a350aff9-78c0-4446-a88e-8967771211c2" owner="Carmen Vallis" ownerEmail="carmen.vallis@site.com" status="ReActivated" lastModified="2010-07-22T23:52:25.3306929Z" noOfActivation="2" maxActivationCount="50">
                                                <activator IP="172.24.108.67" userName="WESTERN_SYDNEY\cvallis2" email="carmen.vallis@site.com" activationTimeStamp="2010-04-21T16:41:42.0516367+10:00">Carmen Vallis</activator>
                                                <activator IP="172.24.111.148" userName="WESTERN_SYDNEY\cvallis2" email="carmen.vallis1@site.com" activationTimeStamp="2010-07-23T09:52:25.3306929+10:00">Carmen Vallis</activator>
                                </licenseKey>
                                <licenseKey sequence="4" key="67865ae2-036e-4be3-a601-a55d378d7694" owner="Janet Coller" ownerEmail="janet.coller@site.com" status="Active" lastModified="2010-04-21T22:21:08.9738945Z" noOfActivation="1" maxActivationCount="50">
                                                <activator IP="172.24.110.20" userName="WESTERN_SYDNEY\jcoller" email="janet.coller@site.com" activationTimeStamp="2010-04-22T08:21:08.9738945+10:00">Janet Coller</activator>
                                </licenseKey>
                                <licenseKey sequence="5" key="bd927ebb-a846-4af7-a3fe-00a827ca69fb" owner="Rob Wales" ownerEmail="robert.wales@site.com" status="Active" lastModified="2010-04-21T23:51:30.0881764Z" noOfActivation="1" maxActivationCount="50">
                                                <activator IP="172.24.109.153" userName="WESTERN_SYDNEY\rwales2" email="robert.wales@site.com" activationTimeStamp="2010-04-22T09:51:30.0881764+10:00">Rob Wales</activator>
                                </licenseKey>
                </license>
</licenseBatch>



This file can be automatically imported into MS Excel and subsequently to MS SQL Server database. It can be used for solution verification.

The issue with reading such XML file using OPENXML is that not all data can be extracted in one go.  The licenseKey tag may consist of multiple activator tags however OPENXML can read only one set at the time.  The paragraph "I. Specifying the xml data type in the WITH clause" of Using OPENXML describes extracting unconsumed data into OverFlow column.

The solution below required enabling BULK insert for the database user and Ad Hoc Distributed Queries.  It can be achieved by running the following set of commands:

sp_addsrvrolemember @loginame = N' YourDatabaseUser', @rolename = N'bulkadmin'

go

sp_configure 'show advanced options',1

reconfigure with override

go

sp_configure 'Ad Hoc Distributed Queries',1

reconfigure with override

go

Solution (combines consumed and unconsumed data into the one recordset):




SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

PRINT 'drop procedure dbo.udpLicenceKeys'
GO

if exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udpLicenceKeys')
 and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.udpLicenceKeys
GO


PRINT 'CREATE PROC dbo.udpLicenceKeys'
GO

/******************************************************************************
   NAME:              udpLicenceKeys
   PURPOSE:

   REVISIONS:

   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE PROCEDURE dbo.udpLicenceKeys
AS
   DECLARE  @LicenceKeys TABLE(XmlCol xml)

                INSERT @LicenceKeys
                  SELECT CONVERT(xml, BulkColumn, 2) FROM
                                OPENROWSET(Bulk '\\servername\share\LicenseKeys.xml'
                                , SINGLE_BLOB) rowsetresults;

                DECLARE @Data XML

                SELECT @Data = XmlCol FROM @LicenceKeys              

                DECLARE @idoc int
                DECLARE @doc NVARCHAR(max)
                SET @doc = (SELECT CONVERT(NVARCHAR(max),XmlCol) FROM @LicenceKeys)
                SET @doc = REPLACE(@doc,'<?xml version="1.0" encoding="utf-8"?>','')
                SET @doc = REPLACE(@doc,' xmlns="http://site.com/schemas/licensekey"','')

                EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

                DECLARE @FindSubString VARCHAR(MAX)

                SET @FindSubString = '<activator'

                SELECT  T.*
                ,(CASE WHEN (LEN(convert(nvarchar(max),overflow)) -
                                                                LEN(REPLACE(convert(nvarchar(max),overflow), @FindSubString, '')))
                                                                /LEN(@FindSubString) > 0
                                                THEN REPLACE(convert(nvarchar(max),overflow)
                                                                ,'<licenseKey','<licenseKey batchID="'+CONVERT(NVARCHAR(10),BatchID)
                                                                +'" sequence="' + CONVERT(NVARCHAR(10),sequence) + '" ')
                  ELSE NULL END
                ) strXML

                FROM (
                SELECT *
                FROM OPENXML (@idoc, '/licenseBatch/license/licenseKey',10)
                WITH (
                                batchID float '../@batchID'
                                ,batchStatus nvarchar(255) '../@batchStatus'
                                ,expireDays float '../@expireDays'
                                ,[admin] nvarchar(255) '../@admin'
                                ,adminEmail nvarchar(255) '../@adminEmail'
                                ,requester nvarchar(255) '../@requester'
                                ,requesterEmail nvarchar(255) '../@requesterEmail'
                                ,creationTimeStamp nvarchar(255) '../@creationTimeStamp'
                                ,activationType nvarchar(255) '../@activationType'
                                ,note nvarchar(255) '../note'
                                ,sequence float '@sequence'
                                ,[key] nvarchar(255) '@key'
                                ,[owner] nvarchar(255) '@owner'
                                ,ownerEmail nvarchar(255) '@ownerEmail'
                                ,[status] nvarchar(255) '@status'
                                ,lastModified nvarchar(255) '@lastModified'
                                ,noOfActivation float '@noOfActivation'
                                ,maxActivationCount float '@maxActivationCount'
                                ,activator nvarchar(255)
                                ,IP nvarchar(255) 'activator/@IP'
                                ,userName nvarchar(255) 'activator/@userName'
                                ,email nvarchar(255) 'activator/@email'
                                ,activationTimeStamp datetime 'activator/@activationTimeStamp'
                                ,OverFlow nvarchar(max) '@mp:xmltext')) T

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO                        



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

PRINT 'drop view dbo.udvLicenceKeys'
GO

if exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udvLicenceKeys')
 and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.udvLicenceKeys
GO

PRINT 'CREATE VIEW dbo.udvLicenceKeys'
GO

/******************************************************************************
   NAME:              dbo.udvLicenceKeys
   PURPOSE:

   REVISIONS:

$Log$

   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/



CREATE VIEW dbo.udvLicenceKeys
AS
SELECT  *
FROM    OPENROWSET( 'SQLNCLI'
                    ,'Server=servername;UID=YourUserName;PWD=YourPassword;'
                    ,'SET FMTONLY OFF; SET NOCOUNT ON; Execute YourDatabaseName..udpLicenceKeys')


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

PRINT 'drop function dbo.udfGet_Overflow_XML'
GO

if exists (select * from sysobjects
where id = object_id(N'.dbo.udfGet_Overflow_XML')
 and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udfGet_Overflow_XML
GO

PRINT 'CREATE FUNCTION dbo.udfGet_Overflow_XML'
GO

/******************************************************************************
   NAME:              udvGet_Overflow_XML
   PURPOSE:

   REVISIONS:

$Log$

   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE FUNCTION dbo.udfGet_Overflow_XML()
RETURNS NVARCHAR(MAX)
AS
                BEGIN
                                DECLARE  @strXML NVARCHAR(MAX)
                                                                ,@finalXML NVARCHAR(MAX)

                                DECLARE crs CURSOR
                                                                FOR select strXML from dbo.udvLicenceKeys

                                SET @finalXML = '<root>'

                                IF @@error = 0 BEGIN
                                                OPEN crs
                                END

                                FETCH NEXT FROM crs
                                INTO   @strXML

                                WHILE @@FETCH_STATUS = 0 BEGIN
                                                IF @strXML IS NOT NULL BEGIN
                                                                SET @finalXML = @finalXML + @strXML
                                                END
                                                FETCH NEXT FROM crs
                                                INTO   @strXML
                                END
                                DEALLOCATE crs
                                SET @finalXML = @finalXML + '</root>'
                                RETURN (@finalXML)
                END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

PRINT 'drop procedure dbo.udpOverflow_Recordset'
GO

if exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udpOverflow_Recordset')
 and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.udpOverflow_Recordset
GO


PRINT 'CREATE PROC dbo.udpOverflow_Recordset'
GO

/******************************************************************************
   NAME:              udpOverflow_Recordset
   PURPOSE:

   REVISIONS:

   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE PROCEDURE dbo.udpOverflow_Recordset
AS

                DECLARE @DocHandle int
                DECLARE @XmlDocument nvarchar(MAX)

                                SET @XmlDocument = (select dbo.udfGet_Overflow_XML())
                                EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument

                                SELECT *
                                FROM OPENXML (@DocHandle, '/root/licenseKey/activator',1)
                                                  WITH (batchID float '../@batchID'
                                                                ,sequence float '../@sequence'
                                                                ,IP nvarchar(255) '@IP'
                                                                ,userName nvarchar(255) '@userName'
                                                                ,email nvarchar(255) '@email'
                                                                ,activationTimeStamp datetime '@activationTimeStamp'
                                                                ,activator nvarchar(255) '.')                
                                EXEC sp_xml_removedocument @DocHandle

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

PRINT 'drop view dbo.udvOverflow_Recordset'
GO

if exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udvOverflow_Recordset')
 and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.udvOverflow_Recordset
GO

PRINT 'CREATE VIEW dbo.udvOverflow_Recordset'
GO

/******************************************************************************
   NAME:              dbo.udvOverflow_Recordset
   PURPOSE:

   REVISIONS:

$Log$

   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/



CREATE VIEW dbo.udvOverflow_Recordset
AS
SELECT  *
FROM    OPENROWSET( 'SQLNCLI'
                                                                                ,'Server=servername;UID=YourUserName;PWD=YourPass;'
                    ,'SET FMTONLY OFF; SET NOCOUNT ON; Execute DatabaseName..udpOverflow_Recordset')


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

PRINT 'drop view dbo.udvOverflow_Full_Recordset'
GO

if exists (select * from dbo.sysobjects
 where id = object_id(N'dbo.udvOverflow_Full_Recordset')
 and OBJECTPROPERTY(id, N'IsView') = 1)
drop view dbo.udvOverflow_Full_Recordset
GO

PRINT 'CREATE VIEW dbo.udvOverflow_Full_Recordset'
GO

/******************************************************************************
   NAME:              dbo.udvOverflow_Full_Recordset
   PURPOSE:

   REVISIONS:

$Log$

   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE VIEW dbo.udvOverflow_Full_Recordset
AS
                SELECT DISTINCT
                                                O.batchID
                                                ,L.batchStatus
                                                ,L.expireDays
                                                ,L.admin
                                                ,L.adminEmail
                                                ,L.requester
                                                ,L.requesterEmail
                                                ,L.creationTimeStamp
                                                ,L.activationType
                                                ,L.note
                                                ,L.sequence
                                                ,L.[key]
                                                ,L.owner
                                                ,L.ownerEmail
                                                ,L.status
                                                ,L.lastModified
                                                ,L.noOfActivation
                                                ,L.maxActivationCount
                                                ,O.activator
                                                ,O.IP
                                                ,O.userName
                                                ,O.email
                                                ,O.activationTimeStamp
                                                 from dbo.udvLicenceKeys L
                INNER JOIN dbo.udvOverflow_Recordset O
                 ON L.batchID = O.batchID
                 WHERE L.sequence = O.sequence

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

PRINT 'drop function dbo.udfLicenceKeys'
GO

if exists (select * from sysobjects
where id = object_id(N'.dbo.udfLicenceKeys')
 and xtype in (N'FN', N'IF', N'TF'))
drop function dbo.udfLicenceKeys
GO

PRINT 'CREATE FUNCTION dbo.udfLicenceKeys'
GO

/******************************************************************************
   NAME:              udfLicenceKeys
   PURPOSE:

   REVISIONS:

$Log$

   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
 ******************************************************************************/
CREATE FUNCTION dbo.udfLicenceKeys()
RETURNS @tempTBL TABLE (
                                                                 batchID float
                                                                ,batchStatus nvarchar(255)
                                                                ,expireDays float
                                                                ,[admin] nvarchar(255)
                                                                ,adminEmail nvarchar(255)
                                                                ,requester nvarchar(255)
                                                                ,requesterEmail nvarchar(255)
                                                                ,creationTimeStamp nvarchar(255)
                                                                ,activationType nvarchar(255)
                                                                ,note nvarchar(255)
                                                                ,sequence float
                                                                ,[key] nvarchar(255)
                                                                ,[owner] nvarchar(255)
                                                                ,ownerEmail nvarchar(255)
                                                                ,[status] nvarchar(255)
                                                                ,lastModified nvarchar(255)
                                                                ,noOfActivation float
                                                                ,maxActivationCount float
                                                                ,activator nvarchar(255)
                                                                ,IP nvarchar(255)
                                                                ,userName nvarchar(255)
                                                                ,email nvarchar(255)
                                                                ,activationTimeStamp datetime)
AS
                BEGIN
                                INSERT INTO @tempTBL
                                SELECT batchID
                                                ,batchStatus
                                                ,expireDays
                                                ,admin
                                                ,adminEmail
                                                ,requester
                                                ,requesterEmail
                                                ,creationTimeStamp
                                                ,activationType
                                                ,note
                                                ,sequence
                                                ,[key]
                                                ,owner
                                                ,ownerEmail
                                                ,status
                                                ,lastModified
                                                ,noOfActivation
                                                ,maxActivationCount
                                                ,activator
                                                ,IP
                                                ,userName
                                                ,email
                                                ,activationTimeStamp
                                FROM  dbo.udvLicenceKeys

                                INSERT INTO @tempTBL
                                SELECT batchID
                                                ,batchStatus
                                                ,expireDays
                                                ,admin
                                                ,adminEmail
                                                ,requester
                                                ,requesterEmail
                                                ,creationTimeStamp
                                                ,activationType
                                                ,note
                                                ,sequence
                                                ,[key]
                                                ,owner
                                                ,ownerEmail
                                                ,status
                                                ,lastModified
                                                ,noOfActivation
                                                ,maxActivationCount
                                                ,activator
                                                ,IP
                                                ,userName
                                                ,email
                                                ,activationTimeStamp
                                FROM   dbo.udvOverflow_Full_Recordset
                                RETURN
                END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


 The final query:


SELECT * FROM dbo.udfLicenceKeys()

If you find this script useful please donate generously.