Problem Creating a Linked Server

[b98mr1]

Hi all...

I am trying to create a linked server on SQl Server 2000 that will connect to an excel document on a local hard drive using Microsoft.Jet.4.0.OLEDB

I configured the Linked server with the following information:

Server Type - Other Data Source
Provider Name - Microsoft Jet 4.0 OLEDB Provider
Data Source - \\'computername'\sample.xls - (also tried just C:\Sample.xls)
Provider String - Excel 8.0 - (also tried Excel 10.0)

However I continually receive this error message whenever I try to view the tables within the Linked Server.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].

I looked this up on MSDN and it gave a workaround of editing the paths for user variables TMP and TEMP but this did not work.

I have also shared out the excel document on my local drive so it shouldn't be a permissions problem.

Any help on this would be greatly appreciated. If anyone has any suggestions of another way to link an excel document to insert data on an existing SQL Server database, I would be eternally greatful.

Thanks

Ross

 

[Jamfool]

--Try the following:

EXEC master.dbo.sp_addlinkedserver
@server = N'MyLinkedServer',
@srvproduct=N'Excel',
@provider=N'Microsoft.Jet.OLEDB.4.0',
@datasrc=N'C:\book1.xls',
@provstr=N'Excel 8.0'

SELECT * FROM MyLinkedServer...[sheet1$]
--or
SELECT * FROM OPENQUERY(MyLinkedServer, 'SELECT * FROM [Sheet1$]')

(Note you will get an error if the excel book is already open)

More Help:

http://support.microsoft.com/?kbid=321686