• Microsoft Jet 4.0 64 Bit

Hi, I have seen quite a few requests for help on the subject of importing data from Access and Excel and I have followed all the advice given but I am still pulling out what little hair I have left as sod’s law says it will work on all servers apart from the one I want it to. As a test I am running the following, which as I say works on every other server apart from this one which is also running SQL 2005 Version 9.00.3042. SP2 Standard Edition.

Select. from openrowset('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;database=d: DataExtracts test.xls', Sheet1$); which produces the following error message. OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server '(null)' returned message 'Unspecified error'.

Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server '(null)'. I have:- 1) Set the Distributed Transaction Coordinator. 2) Tested that SQL can write to the SQL services logon TEMP folder.

3) Tested that SQL can write to the D: DataExtracts folder 4) Ensured that the test.xls is the same one used on all other servers for testing. The excel being used is 2003 and I have also tried Excel 5.0 in my openrowset statement. In my investigations I also read quite a bit about creating a linked server so I thought I would try that and the command EXEC spaddlinkedserver 'ImportData', 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'd: DataExtracts test.xls', NULL, 'Excel 8.0' Return Command(s) completed successfully. However the command SELECT. FROM OPENQUERY(ImportData, 'SELECT. FROM Sheet1$') Returned the same error as before.

Then I wondered if it was the RPC properties of the linked server and I attempted to edit them from False to True but that resulted in the error message Cannot initialize the data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server 'ImportData'. OLE DB provider 'Microsoft.Jet.OLEDB.4.0' for linked server 'ImportData' returned message 'Unspecified error'.

(Microsoft SQL Server, Error: 7303) So now I am completely at a loss. I am going to check the MDAC but the installation came from the same set as the other servers so I suspect that will not be the issue. Any help or fresh ideas would be most appreciated.

You can't post new topics. You can't post topic replies. You can't post new polls. You can't post replies to polls. You can't edit your own topics.

You can't delete your own topics. You can't edit other topics. You can't delete other topics. You can't edit your own posts.

You can't edit other posts. You can't delete your own posts. You can't delete other posts.

Microsoft Jet 4.0 64 Bit

You can't post events. You can't edit your own events. You can't edit other events. You can't delete your own events. You can't delete other events. You can't send private messages.

You can't send emails. You can read topics. You can't vote in polls. You can't upload attachments. You can download attachments.

You can't post HTML code. You can't edit HTML code. You can't post IFCode. You can't post JavaScript.

You can post emoticons. You can't post or upload images.