Thursday 29 May 2008

OPENDATASOURCE or OPENROWSET linked server Error fix

This drove me mental for a while.

If you are trying to open an external file such as a TXT, XLS, CSV using SQL's OPENROWSET or OPENDATASOURCE functionality and keep getting errors that look like the ones below then here is the fix/workaround.

The Errors:

OLE Database 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 Database provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


The fix:

I tried all the suggestions about SP2, restarting services, setting flags in the SQL settings etc but all to no avail.

What you need to do is visit the link below and download the

2007 Office System Driver: Data Connectivity Components download

Once downloaded and installed on the SQL Server you will notice a new driver in the list of drivers on the server at this path:

SERVER\Server Objects\Linked Servers\Providers

The new driver is called:

Microsoft.ACE.OLEDB.12.0

All you then need to do is change your SQL Query to use this new driver rather than the old Microsoft.Jet.OLEDB.4.0 one.

Your new connection string should look something like this:

OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'',
''Data Source=C:\data\;
Extended Properties="Text;HDR=No;FMT=Delimited"'')...['+ @FileN +'#CSV]'

Voila! That should sort you out.

4 comments:

Unknown said...

Great post. This fix works a treat.

I have been struggling with this problem for over a day now. Thing started to go wrong when I installed Oracle Client onto the SQL Server 2005 box.

I am now able to link with out problems using Microsoft.ACE.OLEDB.12.0

Thanks

Anonymous said...

Afin une solution qui fonctionne !!
Encore merci beaucoup, après plusieurs heures de prise de tête.

Omen said...

I dont speak that much french but from what I do speak it looks to have sorted your problem! :o)

Glad it helped you out my friend.

Henk said...

French guy said:
At last a working solution. Thanks a lot, after many hours of banging my head.