Solved

TSql Query output  to Excel Spreadsheet generates severe error. Although expected operation completes OK. What's wrong?

Posted on 2008-06-23
2
883 Views
Last Modified: 2012-08-13
I have put together this (attached) Ms SQL script (with a little help from the web) that exports a query to a new excel spreadsheet. The included script was written on my laptop using MS SQL Express, although when tested on my 2003 Server/MSSQL Server SP2 I encountered the exact same error. I have Office 2003 installed locally and Office 2007 installed on server.

I had issues with permissions to the output folder which is the reason it is set to output to the shared directory.

My problem is that whenever the script is run, even though it seems to do everything I had intended it to do, it generates this error;

Quote:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

With the spreadsheet already in existence with the correct sheet name and so forth the error is not generated. Although the script needs to be able to create the file from scratch as the query needs to be able to be changed dynamically in the future.

I've pinned it down to the line "EXEC @OLEReturnvalue = sp_OAMethod @ADOObjectIdentifier, 'Execute', NULL, @DDL, NULL, 129", and have established an error code of -2147211470 (prints when the code is run). Yet thats as far as I seem to be able to get.

Can anybody see where I  am going wrong?

set nocount on
 

DECLARE @ADOObjectIdentifier int

DECLARE @OLEReturnvalue int

DECLARE @DDL varchar(8000)

DECLARE @WorkSheetName varchar(128)

DECLARE @xlsFilePath varchar(255)

DECLARE @ADOObjectIdentifierectionString varchar(255)

DECLARE @SQL varchar(8000)

DECLARE @LinkedServerName nvarchar(128)

DECLARE @fileExists int

DECLARE @tempStr nVarChar(255)
 

SET @LinkedServerName = 'EXCEL_REPORT'

SET @WorkSheetName = 'EmployeePreferences'

SET @DDL = 'CREATE TABLE '+@WorkSheetName+' (PartNumber text, Preference text, EmployeeNumber text)'

SET @xlsFilePath = 'C:\Documents and Settings\All Users\Documents\Test_'+CONVERT(varchar(10),GETDATE(),112)+'.xls'

SET @ADOObjectIdentifierectionString = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+@xlsFilePath+';Extended Properties=Excel 8.0'

SET @SQL = 'INSERT INTO '+@LinkedServerName+'...'+@WorkSheetName+' (PartNumber, Preference, EmployeeNumber)

SELECT convert(text, convert(nVarChar(255), PartNumber))

, convert(text, convert(nVarChar(255), Preference))

, convert(text, convert(nVarChar(255), EmployeeNumber))

FROM TestingSQL.dbo.TechNumberVsPartNumber'
 
 

--Destroy all previous

--IF @outputFile exists, delete it 

	EXEC master..xp_fileexist @xlsFilePath, @fileExists OUTPUT

    IF(@fileExists = 1)

	BEGIN

		SET @tempStr = 'del /F "' + @xlsFilePath + '"'

		EXEC master..xp_cmdshell  @tempStr, NO_OUTPUT

	END

IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @LinkedServerName)

BEGIN

	EXEC sp_dropserver @LinkedServerName, 'droplogins'

END--
 
 

EXEC @OLEReturnvalue = sp_OACreate 'ADODB.Connection', @ADOObjectIdentifier OUT

EXEC @OLEReturnvalue = sp_OASetProperty @ADOObjectIdentifier, 'ConnectionString', @ADOObjectIdentifierectionString

EXEC @OLEReturnvalue = sp_OAMethod @ADOObjectIdentifier, 'Open'

EXEC @OLEReturnvalue = sp_OAMethod @ADOObjectIdentifier, 'Execute', NULL, @DDL, NULL, 129

print @OLEReturnvalue

EXEC @OLEReturnvalue = sp_OADestroy @ADOObjectIdentifier

IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @LinkedServerName)

BEGIN

	EXEC sp_addlinkedserver @server = @LinkedServerName

    		, @srvproduct = 'Microsoft Excel Workbook'

    		, @provider = 'Microsoft.Jet.OLEDB.4.0'

    		, @datasrc = @xlsFilePath

    		, @provstr = 'Excel 8.0' 

	--Windows Authentication

	EXEC sp_addlinkedsrvlogin @LinkedServerName, 'false' 

END

EXEC (@SQL)

--Destroy

IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @LinkedServerName)

BEGIN

	EXEC sp_dropserver @LinkedServerName, 'droplogins'

END

Open in new window

0
Comment
Question by:2curiousX
2 Comments
 
LVL 14

Accepted Solution

by:
NBSO_ISS earned 500 total points
ID: 21845372
0
 

Author Closing Comment

by:2curiousX
ID: 31469682
Thanks,
The link you posted at first confused me because I was not using synonyms which was the cause of the error described but it led me on the right track. I decided to use synonyms, and use them without the "sp_". Works great.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now