|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Exporting ToExcel fileHi, 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 <eliassal@online.nospam>>thread-index: AcUYosS7NtNk3oRyQvKh7cX1Ypnwdw== >X-WBNR-Posting-Host: 82.233.27.153 >From: "=?Utf-8?B?U2FsYW1FbGlhcw==?=" >Subject: Exporting ToExcel file <532CA433-F9C8-40B2-A335-14E809EE0***@microsoft.com>>Date: Mon, 21 Feb 2005 21:53:01 -0800 >Lines: 18 >Message-ID: >MIME-Version: 1.0 TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.>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: phx.gbl!TK2MSFTNGXA03.phx.gbl >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.tools:26922>X-Tomcat-NG: microsoft.public.sqlserver.tools server, then tries to > >Hi, I have a SP that adds an Excel file as a linked >send the result of a query into this file. (varchar(20),UnitPrice) >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 >from Northwind..Products exclusively by another >[OLE/DB provider returned message: Cannot start your application. >The workgroup information file is missing or opened >user.] 'Microsoft.Jet.OLEDB.4.0' >OLE DB error trace [OLE/DB Provider >IDBInitialize::Initialize returned 0x80040e4d: is on my laptop. So Authentication failed.]. >--------------- >I am executing this on my Laptop(winXP SP2), sql 2000 Show quoteHide quote >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 Show quoteHide quote "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. > > -------------------- > >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-14E809EE0***@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!TK2MSFTNGP08. > 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 > > > > 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 <eliassal@online.nospam>>thread-index: AcUZDYE2sZAiZVxlSiqUFE3MWVkzdg== >X-WBNR-Posting-Host: 82.233.27.153 >From: "=?Utf-8?B?U2FsYW1FbGlhcw==?=" >References: <532CA433-F9C8-40B2-A335-14E809EE0***@microsoft.com> <K8U2ePMGFHA.2***@TK2MSFTNGXA02.phx.gbl> >Subject: RE: Exporting ToExcel file <6D98A325-8651-4FD5-AC3E-ADDE2258B***@microsoft.com>>Date: Tue, 22 Feb 2005 10:37:04 -0800 >Lines: 168 >Message-ID: >MIME-Version: 1.0 TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED01.>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: phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl >Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.tools:26924>X-Tomcat-NG: microsoft.public.sqlserver.tools name and place on the > >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 Show quoteHide quote >server file name by copying the >/* > >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 >template Empty excel File file in order to > set @dosStmt = ' copy E:\Dev\sql\empty.xls ' + @fileName > exec master..xp_cmdshell @dosStmt > > -- Create a "temporary" linked server to that >"Export" Data actually export the query > EXEC sp_addlinkedserver 'ExcelSource', 'Jet 4.0', >'Microsoft.Jet.OLEDB.4.0', @fileName, NULL, 'Excel 5.0' > > -- construct a T-SQL statement that will >results ExcelSource...[ExcelTable$] ' + ' ( ' + > -- to the Table in the target linked server > set @tsqlStmt = 'Insert Show quoteHide quote >@colList + ' ) '+ @query TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.> > 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. >> >> -------------------- >> >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-14E809EE0***@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: >> Show quoteHide quote >> 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 >> > >> >> >  
Other interesting topics
|
|||||||||||||||||||||||
