I following sp is a modification of one I found here some weeks back. Sorry - can't remeber the origional author. It is used like so..
DECLARE @strReportSQL VARCHAR(8000)
SET @strReportSQL = 'INSERT INTO ~XLWS~ ([Column Date], [Column String], [Column Integer]) ' +
'SELECT ' +
' GETDATE(), ' +
' ''Hello'', ' +
' 1234 '
EXEC spSaveToExcel @pPath = 'C:\ExportToExcel.xls',
@pWorksheetName = '[Sample_Sheet_1]',
@pExcelTableDefinition = '[Column Date] DATETIME, [Column String] TEXT, [Column Integer] INTEGER',
@pSQL = @strReportSQL
main body follows..
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE spSaveToExcel (
@pPath VARCHAR(255) -- Full Drive or UNC path for XLS file. Requires Read/Write access
-- and must be accessable from server via SQL Server service account
-- & SQL Server Agent service account, if scheduled
,@pWorksheetName VARCHAR(128) -- Name of the XLS Worksheet (table)
,@pExcelTableDefinition VARCHAR(255) -- Comma delimited list in the following format, using Jet data types
-- Column1Name Column1DataType, Column2Name Column2DataType, etc.
-- If @pWorksheetName does not exist then it is created using this
-- definition
,@pSQL AS VARCHAR(8000) -- The SQL to execute. Use ~XLWS~ aS a placeholder for the spreadsheet
) AS
SET NOCOUNT ON
DECLARE @intADOConnection int -- ADO Connection object to create XLS
DECLARE @intHR int -- OLE return value
DECLARE @strErrorSrc varchar(255) -- OLE Error Source
DECLARE @strErrorDesc varchar(255) -- OLE Error Description
DECLARE @strConnect varchar(255) -- OLE DB Connection string for Jet 4 Excel ISAM
DECLARE @blnCreatedWorksheet bit -- Whether the XLS Worksheet exists
DECLARE @strCreateTableDDL varchar(8000)-- Jet4 DDL for the XLS WKS table creation
DECLARE @intInsertedCount int -- Number of records added to XLS
-- Init variables
SET @intInsertedCount = 0
-- Assign the ADO connection string for the XLS creation
SET @strConnect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@pPath+';Extended Properties=Excel 8.0'
-- Table creation DDL, uses Jet4 syntax,
-- Text data type = varchar(255) when accessed from T-SQL
SET @strCreateTableDDL = 'CREATE TABLE ' + @pWorksheetName + ' (' + @pExcelTableDefinition + ')'
/****************************************************************************************************************/
/* Test for the presence of the Excel spreadsheet. If it is there then either connect to the specified */
/* worksheet (@pWorksheetName), or create it using the supplied definition (@pExcelTableDefinition) */
/****************************************************************************************************************/
-- Create the Conn object
EXEC @intHR = sp_OACreate 'ADODB.Connection', @intADOConnection OUT
-- Have to use <> as OLE / ADO can return negative error numbers
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
-- Set a the Conn object's ConnectionString property
-- Work-around for error using a variable parameter on the Open method
EXEC @intHR = sp_OASetProperty @intADOConnection, 'ConnectionString', @strConnect
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
-- Call the Open method to create the XLS if it does not exist, can't use parameters
EXEC @intHR = sp_OAMethod @intADOConnection, 'open'
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
-- Create the Excel table (worksheet)
-- Call the Execute method to Create the work sheet with the @pWorksheetName 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 Excel to leave it alone
EXEC @intHR = sp_OAMethod @intADOConnection, 'Execute', NULL, @strCreateTableDDL, NULL, 129 -- adCmdText + adExecuteNoRecords
-- 0x80040E14 for table exists in ADO kludge, skip 0x80042732 for ADO Optional parameters (NULL) in SQL7
IF @intHR = 0x80040E14 OR @intHR = 0x80042732 BEGIN
-- Trap these OLE Errors
IF @intHR = 0x80040E14 BEGIN
SET @blnCreatedWorksheet = 0
END
SET @intHR = 0 -- ignore these errors
END
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
-- Destroy the Conn object, +++ important to not leak memory +++
EXEC @intHR = sp_OADestroy @intADOConnection
IF @intHR <> 0 BEGIN
-- Return OLE error
EXEC sp_OAGetErrorInfo @intADOConnection, @strErrorSrc OUT, @strErrorDesc OUT
SELECT Error=convert(varbinary(4),@intHR), Source=@strErrorSrc, Description=@strErrorDesc
RETURN
END
/***************************************************************************************************************/
-- Execute the SQL
SET @pSQL = REPLACE(@pSQL, '~XLWS~', 'OPENDATASOURCE( ''Microsoft.Jet.OLEDB.4.0'', ''Data Source="' + @pPath + '";Extended properties="Excel 8.0;HDR=Yes;"'')...' + @pWorksheetName)
EXEC (@pSQL)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO