snakehips2000
Programmer
Im trying to create a formatted Excel spreadsheet with the results of a T-SQL query. However, I'm getting an error when using the following code which seems to be in generic use on several forums. Can anyone tell me where my syntax is wrong? I have the necessary permissions for executing sp_OACreate, sp_OAGetProperty, sp_OASetProperty and sp_OAMethod. In addition I have run sp_configure to enable "OLE Automation Procedures" on my server. The code generates an error at the EXEC statement at line 81.
Thanks, here's the code ...
Thanks, here's the code ...
Code:
USE [AdventureWorks]
GO
/****** Object: StoredProcedure [dbo].[ExportToExcel] Script Date: 01/22/2010 22:35:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ExportToExcel] (
@server sysname = 'BRIAN',
@uname sysname = Null,
@pwd sysname = Null,
@QueryText varchar(200) = 'SELECT * FROM AdventureWorks.Person.Contact',
@filename varchar(200) = 'C:\MyTable.xls'
)
AS
BEGIN
DECLARE @SQLServer int,
@QueryResults int,
@CurrentResultSet int,
@object int,
@WorkBooks int,
@WorkBook int,
@Range int,
@hr int,
@Columns int,
@Rows int,
@indColumn int,
@indRow int,
@off_Column int,
@off_Row int,
@code_str varchar(100),
@result_str varchar(255)
IF @QueryText IS NULL
BEGIN
PRINT 'Set the query string'
RETURN
END
-- Sets the server to the local server
IF @server IS NULL SELECT @server = @@servername
-- Sets the username to the current user name
IF @uname IS NULL SELECT @uname = SYSTEM_USER
SET NOCOUNT ON
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @SQLServer OUT
IF @hr <> 0
BEGIN
PRINT 'error create SQLDMO.SQLServer'
RETURN
END
-- Connect to the SQL Server
IF @pwd IS NULL
BEGIN
EXEC @hr = sp_OASetProperty @SQLServer, 'LoginSecure', 1
IF @hr <> 0
BEGIN
PRINT 'error Connect with Windows Authentication'
RETURN
END
END
ELSE
BEGIN
EXEC @hr = sp_OAMethod @SQLServer, 'Connect', null, @server, @uname, @pwd
IF @hr <> 0
BEGIN
PRINT 'error Connect'
RETURN
END
END
SELECT @result_str = 'ExecuteWithResults(''' + @QueryText + ''')'
-- error gets generated here!
EXEC @hr = sp_OAMethod @SQLServer, @result_str, @QueryResults OUT
IF @hr <> 0
BEGIN
PRINT 'error with method ExecuteWithResults'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'CurrentResultSet', @CurrentResultSet
OUT
IF @hr <> 0
BEGIN
PRINT 'error get CurrentResultSet'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Columns', @Columns OUT
IF @hr <> 0
BEGIN
PRINT 'error get Columns'
RETURN
END
EXEC @hr = sp_OAMethod @QueryResults, 'Rows', @Rows OUT
IF @hr <> 0
BEGIN
PRINT 'error get Rows'
RETURN
END
EXEC @hr = sp_OACreate 'Excel.Application', @object OUT
IF @hr <> 0
BEGIN
PRINT 'error create Excel.Application'
RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'WorkBooks', @WorkBooks OUT
IF @hr <> 0
BEGIN
PRINT 'error create WorkBooks'
RETURN
END
EXEC @hr = sp_OAGetProperty @WorkBooks, 'Add', @WorkBook OUT
IF @hr <> 0
BEGIN
PRINT 'error with method Add'
RETURN
END
EXEC @hr = sp_OAGetProperty @object, 'Range("A1")', @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
SELECT @indRow = 1
SELECT @off_Row = 0
SELECT @off_Column = 1
WHILE (@indRow <= @Rows)
BEGIN
SELECT @indColumn = 1
WHILE (@indColumn <= @Columns)
BEGIN
EXEC @hr = sp_OAMethod @QueryResults, 'GetColumnString', @result_str OUT,
@indRow, @indColumn
IF @hr <> 0
BEGIN
PRINT 'error get GetColumnString'
RETURN
END
EXEC @hr = sp_OASetProperty @Range, 'Value', @result_str
IF @hr <> 0
BEGIN
PRINT 'error set Value'
RETURN
END
EXEC @hr = sp_OAGetProperty @Range, 'Offset', @Range OUT, @off_Row,
@off_Column
IF @hr <> 0
BEGIN
PRINT 'error get Offset'
RETURN
END
SELECT @indColumn = @indColumn + 1
END
SELECT @indRow = @indRow + 1
SELECT @code_str = 'Range("A' + LTRIM(str(@indRow)) + '")'
EXEC @hr = sp_OAGetProperty @object, @code_str, @Range OUT
IF @hr <> 0
BEGIN
PRINT 'error create Range'
RETURN
END
END
SELECT @result_str = 'exec master..xp_cmdshell ''del ' + @filename + ''',
no_output'
EXEC(@result_str)
SELECT @result_str = 'SaveAs("' + @filename + '")'
EXEC @hr = sp_OAMethod @WorkBook, @result_str
IF @hr <> 0
BEGIN
PRINT 'error with method SaveAs'
RETURN
END
EXEC @hr = sp_OAMethod @WorkBook, 'Close'
IF @hr <> 0
BEGIN
PRINT 'error with method Close'
RETURN
END
END
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
PRINT 'error destroy Excel.Application'
RETURN
END
EXEC @hr = sp_OADestroy @SQLServer
IF @hr <> 0
BEGIN
PRINT 'error destroy SQLDMO.SQLServer'
RETURN
END