Problem running script

Question

Post reply

Problem running script

imty

SSC Enthusiast

Points: 152
More actions
February 21, 2005 at 9:26 pm

#67061

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$'].

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47794 More actions · Answer 1

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