>>Script Language and Platform: SQL Server
Creating Excel using TSQ-L.
Author: Rahul Vairagi
PRINT 'Begin CreateXLS script at '+RTRIM(CONVERT(varchar(24),GETDATE(),121))+' ' PRINT '' GO SET NOCOUNT ON DECLARE @Conn int -- ADO Connection object to create XLS , @hr int -- OLE return value , @src varchar(255) -- OLE Error Source , @desc varchar(255) -- OLE Error Description , @Path varchar(255) -- Drive or UNC path for XLS , @Connect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM , @WKS_Created bit -- Whether the XLS Worksheet exists , @WKS_Name varchar(128) -- Name of the XLS Worksheet (table) , @ServerName nvarchar(128) -- Linked Server name for XLS , @DDL varchar(8000) -- Jet4 DDL for the XLS WKS table creation , @SQL varchar(8000) -- INSERT INTO XLS T-SQL , @Recs int -- Number of records added to XLS , @Log bit -- Whether to log process detail SELECT @Recs = 0 , @Log = 1 SET @Path = 'E:Rahul'+CONVERT(varchar(10),GETDATE(),112)+'.xls' SET @Path = 'E:RahulRecordsHistory_MobDW.xls' SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='[email protected]+';Extended Properties=Excel 8.0' SET @ServerName = 'EXCEL_TEST' SET @WKS_Name = CONVERT(varchar(10),GETDATE(),112) SET @DDL = 'CREATE TABLE '[email protected]_Name+' (TableName nvarchar, RowsCount int)' SET @SQL = 'INSERT INTO '[email protected]+'...'[email protected]_Name+' (TableName, RowsCount) ' 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 Rahul.dbo.Dim_Date_Test' IF @Log = 1 PRINT 'Created OLE ADODB.Connection object' -- Create the Conn 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), [email protected], [email protected] 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), [email protected], [email protected] RETURN END IF @Log = 1 PRINT char(9)+'Open Connection to XLS, for file Create or Append' -- Call the Open method to create the XLS if it does not exist, can't use parameters EXEC @hr = sp_OAMethod @Conn, 'Open' IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT SELECT Error=convert(varbinary(4),@hr), [email protected], [email protected] RETURN END -- %%% This section could be repeated for multiple Worksheets (Tables) IF @Log = 1 PRINT char(9)+'Execute DDL to create '''[email protected]_Name+''' worksheet' EXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 -- adCmdText + adExecuteNoRecords -- 0x80040E14 for table exists in ADO IF @hr = 0x80040E14 OR @hr = 0x80042732 BEGIN IF @hr = 0x80040E14 BEGIN PRINT char(9)+''''[email protected]_Name+''' Worksheet exists for append' SET @WKS_Created = 0 END SET @hr = 0 -- ignore these errors END IF @hr <> 0 BEGIN -- Return OLE error EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT SELECT Error=convert(varbinary(4),@hr), [email protected], [email protected] RETURN END IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object' -- Destroy the Conn object, +++ important to not leak memory +++ EXEC @hr = sp_OADestroy @Conn IF @hr <> 0 BEGIN -- Return OLE error EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT SELECT Error=convert(varbinary(4),@hr), [email protected], [email protected] RETURN END -- Linked Server allows T-SQL to access the XLS worksheet (Table) -- This must be performed after the ADO stuff as the XLS must exist -- and contain the schema for the table, or worksheet IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName) BEGIN IF @Log = 1 PRINT 'Created Linked Server '''[email protected]+''' 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 '''[email protected]_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 '''[email protected]+''' 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
Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided “as -is”, without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose… Disclaimer Continued
Back to Database Journal Home