OPENROWSET, OPENDATASOURCE - “Microsoft.ace.oledb.12.0” for linked server “(null)” reported error – Excel 2007, 2010

SQL Server supports distributed queries in many ways, allowing users to access heterogeneous data sources. The frequency of data access determines the way that remote data source should be set up for accessing via SQL Server. If data needs to be accessed frequently, Linked Server would be the best way. If not, distributed queries with OPENROWSET or OPENDATASOURCE can be used for accessing data.

Distributed query is a superstar for errors :). I have been using all techniques; Linked Servers, OPENQUERY, OPENROWSET, and OPENDATASOURCE and, of course, have seen enough errors too. Yesterday I faced for them again, the famous error ….for linked server “(null)”…… while I was testing codes for my students, explaining Distributed Queries.

The complete error was;

Msg 7399, Level 16, State 1, Line 1
The OLE DB Provider “Microsoft.ace.oledb.12.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “Microsoft.ace.oledb.12.0” for linked server “(null)”.

The funny thing was, it showed the result without data but headers. Have a look on this (click on the image to see it properly);

This confirmed me that provider can access the Excel file because headers are shown. As the second error said, the problem was with fetching. As usual, googled for it and found many solution and one worked fine for me. This type of error is appeared mainly in new OSs such as Vista, Windows 7 and specially on 64-bit machine. My laptop is configured with Windows 2008 R2 – 64-bit, SQL Server 2008 64-bit and Office 2010 64-bit. It seems that the provider is not getting registered properly on these OSs. You may try with the latest version of OLE DB provider which is 14.0 but I did not try because the solution below worked for me. If you see this error, it would be better to try 14.0 before going for solution below.

Here is the link for 14.0: http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

This is what I did. I ran below code in Master database and as I said, it worked :).

USE master

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

There is a thread, discussing this issue, here is link for it, you can read a lot on it: http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/8514b4bb-945a-423b-98fe-a4ec4d7366ea