Problem running script
-
I created the following Sp submitted by one of group members. Below this sp is the script I am trying to execute. When I execute the script, I get the error messages submitted below. When I try and trace this in SQL profiler. It tells me that 1 file was created. but the output is null. Please tell me what I am doing wrong here.
Thanks,
Newbie
Procedure:
Create proc sp_write2Excel (@fileName varchar(100),
@NumOfColumns tinyint,
@query varchar(200))
as
begin
declare @dosStmt varchar(200)
declare @tsqlStmt varchar(500)
declare @colList varchar(200)
declare @charInd tinyint
set nocount on
-- construct the columnList A,B,C ...
-- until Num Of columns is reached.
set @charInd=0
set @colList = 'A'
while @charInd < @NumOfColumns - 1
begin
set @charInd = @charInd + 1
set @colList = @colList + ',' + char(65 + @charInd)
end
-- Create an Empty Excel file as the target file name by copying the template Empty excel File
set @dosStmt = ' copy c:\temp\empty.xls ' + @fileName
exec master..xp_cmdshell @dosStmt
-- Create a "temporary" linked server to that file in order to "Export" Data
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
@fileName,
NULL,
'Excel 5.0'
-- construct a T-SQL statement that will actually export the query results
-- to the Table in the target linked server
set @tsqlStmt = 'Insert ExcelSource...[ExcelTable$] ' + ' ( ' + @colList + ' ) '+ @query
print @tsqlStmt
-- execute dynamically the TSQL statement
exec (@tsqlStmt)
-- drop the linked server
EXEC sp_dropserver 'ExcelSource'
set nocount off
end
GO
Script:
use master
go
exec sp_write2Excel
-- Target Excel file
'c:\temp\NorthProducts.xls' ,
-- Number of columns in result
3,
-- The query to be exported
'select convert(varchar(10),ProductId),
ProductName,
Convert (varchar(20),UnitPrice) from Northwind..Products'
ERROR MESSAGE:
OLE DB provider 'ExcelSource' does not contain table 'ExcelTable$'. The table either does not exist or the current user does not have permissions on that table.
Insert ExcelSource...[ExcelTable$] ( A,B,C ) select convert(varchar(10),ProductId),
ProductName,
Convert (varchar(20),UnitPrice) from Northwind..Products
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ExcelSource', TableName='ExcelTable$'].
-
Firstly, I don't know why you would waste time fiddling with this when DTS has a perfectly usable datapump that exports to Excel.
The error that you're getting is because it's expecting to find a worksheet called ExcelTable$. If the sheet is named something else then you should use that name.
--------------------
Colt 45 - the original point and click interface
-
Agree with Phil. Especially the avatar
-
Your table is
ExcelSource...[Sheet1$]
and if you have more Sheets you will have more tables
Kindest Regards,Vasc
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply