Create Excel XLS from T-SQL --------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------- CREATE XLS Script Dal - 04/24/2003 ---- Designed for Agent Scheduling, Turn ON "Append Output for Step History" ---- Search for %%% to Find Adjustable Constants and Other Options ---- Uses Ole for ado and ole db to create the xls file if it@ Not exist- - Linked Server Requires The xls Ole Ado To create the xls Worksheet for use as a table by t-sql - Usess linked server to allow t-sql access to xls table - Uses T-SQL TO Populate TE XLS Worksheet, Very Fast - Print 'Begin Createxls Script At' RTRIM (VARCHAR (24), Getdate (), 121) '' PRINT '' Go
Set Nocount OnDeclare @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
- Init variablesselect @Recs = 0 - %%% 1 = verbose output detail, helps find problems, 0 = minimal output detail, @log = 1 - %%% Assign THE UNC OR PATH AND NAME for THE XLS FILE, requires Read / Write access-- must be accessable from server via SQL Server service account-- & SQL Server Agent service account, if scheduledSET @Path = 'C: / TEMP / Test _' CONVERT (varchar (10), GETDATE () , 112) '. XLS' - Assign The Ado Connection String for the XLS CreationSet @Connect = 'provider = Microsoft.jet.OLEDb.4.0; data source =' @ Path '; extended Properties = Excel 8.0' - %%% assign the Linked Server name for the XLS populationSET @ServerName = 'EXCEL_TEST' - %%% Rename Table as required, this will also be the XLS Worksheet nameSET @WKS_Name = 'People' - %%% Table creation DDL Uses Jet4 Syntax, - Text Data Type = varchar (255) When Accessed from T-SQLSET @ddl = 'Create Table' @ WKS_NAME '(SSN TEXT, NAME TEXT, PhONE TEXT)' - %%% T- SQL for Table Population, Note The 4 Part Naming Required by Jet4 OLE DB - INSERT INTO SELECT, INSERT INTO VALUES, and EXEC sp types are supported-- Linked Server does not support SELECT INTO typesSET @SQL = 'INSERT INTO' @ ServerName '...' @ WKS_Name '(SSN, Name, Phone) 'Set @SQL = @ SQL ' SELECT AU_ID AS SSN'SET @SQL = @ SQL ', LTRIM (RTRIM (isnull (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 '- create the conn objectExec @hr = sp_oacreate' adoDb.connection ', @conn outif @hr <> 0 - Have to use <> as ole / ado can return negative error NumBegin - Return Ole Error EXEC SP_OAGETERRORINFO @Conn, @src out, @Desc out select error = convert (varbinary (4), @ hr), source = @ src, Description = @ Desc Returnend
IF @Log = 1 PRINT char (9) 'Assigned ConnectionString property' - Set a the Conn object's ConnectionString property-- Work-around for error using a variable parameter on the Open methodEXEC @hr = sp_OASetProperty @Conn, 'ConnectionString' , @Connectif @hr <> 0begin - Return Ole Error EXEC SP_OAGETERRORINFO @desc out select error = convert (varbinary (4), @ HR), source = @ src, description = @ Desc returnend
IF @log = 1 Print Char (9) 'Open connection to xls, for fas create or append' - call the open method to create the xls if it does not exist, can't us parametersexec @hr = sp_oamethod @conn , 'Open'IF @HR <> 0begin - Return Ole Error Exec sp_oageterrorinfo @conn, @src out, @Desc out select error = convert (varbinary (4), @ hr), source = @ src, description = @ DESC Returned
- %%% this section could be used for multiple worksheets (Tables) IF @log = 1 print char (9) 'Execute Ddl to create' '' ' @ WKS_NAME ' '' Worksheet '- Call The Execute Method To Create the work sheet with the @WKS_Name caption, - which is also used as a Table reference in T-SQL-- Neat way to define column data types in Excel worksheet-- Sometimes converting to text is the only work-around for Excel's General - Cell Formatting, Even Though The Cell Contains Text, Excel Tries To Format - It in A "Smart" Way, I Have Even Had to use the Single Quote Appended As The "1st Character in T-SQL TO Force) to leave it aloneEXEC @hr = sp_OAMethod @Conn, 'Execute', NULL, @DDL, NULL, 129 - adCmdText adExecuteNoRecords-- 0x80040E14 for table exists in ADOIF @hr = 0x80040E14 - kludge, skip 0x80042732 for ADO Optional parameters (Null) in sql7 or @hr = 0x80042732BEGIN - TRAP THESE OLE ERRORS IF @hr = 0x80040e14 Begin Print Char (9) '' '' @ WKS_NAME '' 'Worksheet Exists for append' Set @WKS_CREATED = 0 end set @hr = 0 - ignore these error eren - Return Ole Error EXEC SP_OAGETERRORINFO @Conn, @src out, @Desc out select error = convert (varbinary (4), @ hr ), Source = @ src, description = @ Desc Returnend
IF @Log = 1 PRINT 'Destroyed OLE ADODB.Connection object' - Destroy the Conn object, important to not leak memory EXEC @hr = sp_OADestroy @ConnIF @hr <> 0BEGIN - Return OLE error EXEC sp_OAGetErrorInfo @Conn, @src OUT, @desc OUT SELECT Error = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc RETURNEND-- 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 worksheetIF 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' - no login name or password area required to connect to the jet4 isam linked server exec sp_addlinkedsrvlogin @servername, 'false' end
- Have to EXEC THE SQL, OTHERWISE THE SQL IS Evaluated - for the Linked Server Before It ExistSexec (@SQL) Print Char (9) 'Populated' '' @ WKS_NAME '' 'TABLE with' Convert (Varchar , @@ rowcount) 'Rows'
- %%% Optional you may leave the Linked Server for other XLS operations-- Remember that the Linked Server will not create the XLS, so remove it-- When you are done with it, especially if you delete or move the fileIF 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'ENDGOSET NOCOUNT OFFPRINT' 'Print' Finished Createxls Script AT ' RTRIM (Convert (Varchar (24), Getdate (), 121)) ' Go
Purpose set @Path = 'C: / TEMP / TEST _' Convert (varchar (10), getdate (), 112) '. Xls'
Source 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'
