Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted December 26, 2011

Creating Excel Using T-SQL

By DatabaseJournal.com Staff

>>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:\Rahul\RecordsHistory_MobDW.xls'
SET @Connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@Path+';Extended Properties=Excel 8.0'
SET @ServerName = 'EXCEL_TEST'
SET @WKS_Name = CONVERT(varchar(10),GETDATE(),112)


SET @DDL = 'CREATE TABLE '+@WKS_Name+' (TableName nvarchar, RowsCount int)'
SET @SQL = 'INSERT INTO '+@ServerName+'...'+@WKS_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), 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'

-- 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), Source=@src, Description=@desc
      RETURN
END

 

-- %%% This section could be repeated for multiple Worksheets (Tables)

IF @Log = 1 PRINT char(9)+'Execute DDL to create '''+@WKS_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)+''''+@WKS_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), Source=@src, Description=@desc
      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), Source=@src, Description=@desc
      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 '''+@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

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



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date