This is a T-SQL script that uses OLE, ADO, Jet4 ISAM, and Linked Server to create and populate an Excel Workbook (XLS) file from T-SQL query. If the Excel Worksheet exists, the query will append to the "table". The code is designed to be used by SQL Agent and to append to the step output with verbose and minimal detail. Code is pretty well commented, including some hard won knowledge about Jet4 ISAM, OLE, ADO, and usage of the Excel table from T-SQL. |
Click here to copy the following block |
PRINT 'Begin CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' ' PRINT '' GO
SET NOCOUNT ON DECLARE @Conn int , @hr int , @src varchar(255) , @desc varchar(255) , @Path varchar(255) , @Connect varchar(255) , @WKS_Created bit , @WKS_Name varchar(128) , @ServerName nvarchar(128) , @DDL varchar(8000) , @SQL varchar(8000) , @Recs int , @Log bit
SELECT @Recs = 0 , @Log = 1
SET @Path = 'C:\TEMP\Test_'+CONVERT(varchar(10),GETDATE(),112)+'.xls'
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
SET @ServerName = 'EXCEL_TEST'
SET @WKS_Name = 'People'
SET @DDL = 'CREATE TABLE '+@WKS_Name+' (SSN Text, Name Text, Phone Text)'
SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_Name+' (SSN, Name, Phone) ' SET @SQL = @SQL+'SELECT au_id AS SSN' SET @SQL = @SQL+', LTRIM(RTRIM(ISNULL(au_fname,'''')+'' ''+ISNULL(au_lname,''''))) AS Name' SET @SQL = @SQL+', phone AS Phone ' SET @SQL = @SQL+'FROM pubs.dbo.authors'
IF @Log = 1 PRINT 'Created OLE ADODB.Connection object'
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END
IF @Log = 1 PRINT char(9)+'Assigned ConnectionString property'
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @Connect IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END
IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append'
EXEC @hr = sp_OAMethod @Conn, 'Open' IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END
IF @Log = 1 PRINT char(9)+'Execute DDL to create '''+@WKS_Name+''' worksheet'
EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129
IF @hr = 0x80040E14 OR @hr = 0x80042732 BEGIN IF @hr = 0x80040E14 BEGIN PRINT char(9)+''''+@WKS_Name+''' Worksheet exists for append' SET @WKS_Created = 0 END SET @hr = 0 END IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END
IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object'
EXEC @hr = sp_OADestroy @Conn IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT SELECT Error=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END
IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName) BEGIN IF @Log = 1 PRINT 'Created Linked Server '''+@ServerName+''' and Login' EXEC sp_addlinkedserver @server = @ServerName , @srvproduct = 'Microsoft Excel Workbook' , @provider = 'Microsoft.Jet.OLEDB.4.0' , @datasrc = @Path , @provstr = 'Excel 8.0' EXEC sp_addlinkedsrvlogin @ServerName, 'false' END
EXEC (@SQL) PRINT char(9)+'Populated '''+@WKS_Name+''' table with '+CONVERT(varchar,@@ROWCOUNT)+' Rows'
IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName) BEGIN IF @Log = 1 PRINT 'Deleted Linked Server '''+@ServerName+''' and Login' EXEC sp_dropserver @ServerName, 'droplogins' END GO
SET NOCOUNT OFF PRINT '' PRINT 'Finished CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' ' GO |
|