August 18, 2009 at 6:59 am
Hi,
I am exporting information to an Excel spreadsheet.
It has been working fine, but now when I execute the stored proc, it returns an error on the point where I am writing detail into the spreadsheet.
The command that creates the spreadsheet works fine, and it puts the column headings in as well.
I do not know of anything that has changed that might have caused this. As far as I know everything is still the same. I am not sure what might have caused it to stop working.
Here is the code for the create:
Declare @DDLString VarChar(max)
Declare @ExcelWorksheet VarChar(128)
Declare @EletronicFileName VarChar(255)
Declare @ExcelServerName VarChar(255)
SET @ExcelServerName = 'EXCEL_EXPORT'
SET @ExcelWorksheet = 'Sheet1'
Set @EletronicFileName = 'C:\ElecExport.Xls'
Set @DDLString = 'Create table ' + RTrim(@ExcelWorksheet) +
'(Personnel_Number Text, Start_Date Text, End_Date Text)'
Exec sp_Create_Excel_Document @EletronicFileName, @DDLString
As I have said, this code executes 100% and the file is created on disc, with the three column names.
Here is the code that writes detail into the file:
Set @DDLString = 'insert into %ServerName...' + RTrim(@ExcelWorksheet) +
'(Personnel_Number, Start_Date, End_Date)
values (''' + Cast(COALESCE(@SAL_PERSONNELNO, 0) As VarChar) + ''', ''' +
Cast(COALESCE(@MStart, 0) As VarChar) + ''', ''' +
Cast(COALESCE(@MEnd, 0) As VarChar) + ''')'
Exec sp_Insert_Excel_Data @EletronicFileName, @DDLString, @ExcelServerName
The variables used as the values are primed from a loop through a cursor.
Here are the stored procs:
Procedure sp_Create_Excel_Document
@DataSource VarChar(255),
@DDL VarChar(max)
AS
SET NOCOUNT ON
DECLARE @Conn int
, @hr int
, @src varchar(255)
, @desc varchar(255)
, @connect varchar(255)
, @WKS_Created bit
SET @connect = 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + @DataSource + ';Extended Properties=Excel 8.0'
EXEC @hr = sp_OACreate 'ADODB.Connection', @Conn OUT
EXEC @hr = sp_OASetProperty @Conn, 'ConnectionString', @connect
EXEC @hr = sp_OAMethod @Conn, 'Open'
DECLARE @property varchar(255)
EXEC @hr = sp_OAMethod @Conn, 'Execute', @property OUT, @DDL
EXEC @hr = sp_OADestroy @Conn
Procedure sp_Insert_Excel_Data
@DataSource VarChar(255),
@SQLStatement VarChar(max),
@ServerName VarChar(255)
As
IF NOT EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
EXEC sp_addlinkedserver @server = @ServerName
, @srvproduct = 'Microsoft Excel Workbook'
, @provider = 'Microsoft.Jet.OLEDB.4.0'
, @datasrc = @DataSource
, @provstr = 'Excel 8.0'
EXEC sp_addlinkedsrvlogin @ServerName, 'false'
END
Set @SQLStatement = Replace(@SQLStatement, '%ServerName', @ServerName)
EXEC (@SQLStatement) --ErrorPoint
IF EXISTS(SELECT srvname from master.dbo.sysservers where srvname = @ServerName)
BEGIN
EXEC sp_dropserver @ServerName, 'droplogins'
END
That is all the related code.
The point where I commented "ErrorPoint" is where the error occurs.
At this stage I have the file on disc, as well as the linked server connection.
The error I receive is:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL_EXPORT" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "EXCEL_EXPORT".
I have read on some forum that permissions on the Linked Server connection might cause this kind of error and it was suggested that Security be set to: "Be made using this security context:" and then specifying Admin as the user with no password.
When doing this, I get the exact same error message as when trying to insert data into the spreadsheet.
I have also granted "ALTER ANY LINKED SERVER" permission as suggested in the help file on the "sp_addlinkedserver" method.
None of this has sorted out this problem.
Any help regarding this will be greatly appreciated.
August 19, 2009 at 4:21 am
Why is it that I seem to solve my own problems every time before I even get a response? 😛
It seems that the Linked Server I was creating already existed, and that was causing the problem.
Once I deleted it, the stored procs executed correctly.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply