sp_OAMethod - ExecuteWithResults

[snakehips2000]

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 ...

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

 

[gmmastros]

What is the error message you are getting?

Also, I see that you are using SQLDMO. Could this be the problem. SQLDMO was distributed with SQL2000. With SQL2005, SQLSMO is used instead.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom