Exporting ToExcel file

Hi, I have a SP that adds an Excel file as a linked server, then tries to
send the result of a query into this file.
I get the following error :
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
convert(varchar(10),ProductId), ProductName, Convert (varchar(20),UnitPrice)
from Northwind..Products
[OLE/DB provider returned message: Cannot start your application.
The workgroup information file is missing or opened exclusively by another
user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
I am executing this on my Laptop(winXP SP2), sql 2000 is on my laptop. So
what authentification is failing?
Thanks in advance
Hi,
Could you please post the exact text of the stored
procedure? You can use sp_helptext.
You may also want to test this script to see if there is
any problem:
sp_dropserver 'EXCELSOURCE', 'droplogins'
go
--Replace 'E:\test.xls' appropriately
sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
@.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
'E:\test.xls' , @.provstr = 'Excel 5.0'
go
Insert ExcelSource...[ExcelTable$] ( A,B,C )
select convert(varchar(10),ProductId), ProductName,
Convert (varchar(20),UnitPrice)
from Northwind..Products
go
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUYosS7NtNk3oRyQvKh7cX1Ypnwdw==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>Subject: Exporting ToExcel file
>Date: Mon, 21 Feb 2005 21:53:01 -0800
>Lines: 18
>Message-ID:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26922
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hi, I have a SP that adds an Excel file as a linked
server, then tries to
>send the result of a query into this file.
>I get the following error :
>--
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. Authentication
>failed.
>Insert ExcelSource...[ExcelTable$] ( A,B,C ) select
>convert(varchar(10),ProductId), ProductName, Convert
(varchar(20),UnitPrice)
>from Northwind..Products
>[OLE/DB provider returned message: Cannot start your
application.
>The workgroup information file is missing or opened
exclusively by another
>user.]
>OLE DB error trace [OLE/DB Provider
'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80040e4d:
Authentication failed.].
>--
>I am executing this on my Laptop(winXP SP2), sql 2000
is on my laptop. So
>what authentification is failing?
>Thanks in advance
>
|||Hello, here is the SP :
CREATE proc sp_write2Excel
(
@.fileName varchar(100),
@.NumOfColumns tinyint,
@.query varchar(200)
)
--Obligation : create an empty Excel file with a fixed name and place on the
server
/*
Usage
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'
*/
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 E:\Dev\sql\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
"William Wang[MSFT]" wrote:

> Hi,
> Could you please post the exact text of the stored
> procedure? You can use sp_helptext.
> You may also want to test this script to see if there is
> any problem:
> sp_dropserver 'EXCELSOURCE', 'droplogins'
> go
> --Replace 'E:\test.xls' appropriately
> sp_addlinkedserver 'EXCELSOURCE' , @.srvproduct = '' ,
> @.provider = 'Microsoft.Jet.OLEDB.4.0' , @.datasrc =
> 'E:\test.xls' , @.provstr = 'Excel 5.0'
> go
> Insert ExcelSource...[ExcelTable$] ( A,B,C )
> select convert(varchar(10),ProductId), ProductName,
> Convert (varchar(20),UnitPrice)
> from Northwind..Products
> go
> Sincerely,
> William Wang
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via
> your newsreader so that others may learn and benefit
> from your issue.
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> --
> <eliassal@.online.nospam>
> <532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
> TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.
> phx.gbl!TK2MSFTNGXA03.phx.gbl
> microsoft.public.sqlserver.tools:26922
> server, then tries to
> error. Authentication
> (varchar(20),UnitPrice)
> application.
> exclusively by another
> 'Microsoft.Jet.OLEDB.4.0'
> Authentication failed.].
> is on my laptop. So
>
|||Hi,
Your script looks good and it works correctly on my test
machine. Based on my research, this issue can occur
because the login used to connect to the SQL Server does
not have enough permission. Please add the following
statement to your SP defination (below EXEC
sp_addlinkedserver):
EXEC sp_addlinkedsrvlogin 'ExcelSource',
'false',NULL,'ADMIN',NULL
then drop the existing SP and create a new SP to test
the problem.
Feel free to let me know if this resolves your problem.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
This posting is provided "AS IS" with no warranties, and
confers no rights.
--
>Thread-Topic: Exporting ToExcel file
>thread-index: AcUZDYE2sZAiZVxlSiqUFE3MWVkzdg==
>X-WBNR-Posting-Host: 82.233.27.153
>From: "=?Utf-8?B?U2FsYW1FbGlhcw==?="
<eliassal@.online.nospam>
>References:
<532CA433-F9C8-40B2-A335-14E809EE002D@.microsoft.com>
<K8U2ePMGFHA.2840@.TK2MSFTNGXA02.phx.gbl>
>Subject: RE: Exporting ToExcel file
>Date: Tue, 22 Feb 2005 10:37:04 -0800
>Lines: 168
>Message-ID:
<6D98A325-8651-4FD5-AC3E-ADDE2258B7C1@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
>charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.tools
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
>Path:
TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFT FEED01.
phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA02.phx.gbl
microsoft.public.sqlserver.tools:26924
>X-Tomcat-NG: microsoft.public.sqlserver.tools
>Hello, here is the SP :
>--
>CREATE proc sp_write2Excel
>(
>@.fileName varchar(100),
>@.NumOfColumns tinyint,
>@.query varchar(200)
>)
>--Obligation : create an empty Excel file with a fixed
name and place on the
>server
>/*
>Usage
>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'
>
>*/
>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 E:\Dev\sql\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$] ' + ' ( ' +[vbcol=seagreen]
>@.colList + ' ) '+ @.query
> print @.tsqlStmt
> -- execute dynamically the TSQL statement
> exec (@.tsqlStmt)
> -- drop the linked server
> EXEC sp_dropserver 'ExcelSource'
> set nocount off
>End
>GO
>
>"William Wang[MSFT]" wrote:
is[vbcol=seagreen]
,[vbcol=seagreen]
and[vbcol=seagreen]
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGP08.[vbcol=seagreen]
an[vbcol=seagreen]
2000
>