Msg 7301 Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkToAceess"

While executing example given in my previous article, one of the reader faced an issue so he sent an email to me by complaining that he is facing following error:

Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41

The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkToAceess" reported an error. Access denied.

Msg 7301, Level 16, State 2, Procedure sp_tables_ex, Line 41

Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "LinkToAceess".

This is really very simple error regarding the setting of the provider you have used in your linked server. In our case, we have used “Microsoft.ACE.OLEDB.12.0” so we have to set the property of the same.

In your SSMS, go to “Object Explorer” and follow the below path:

Server->Server Objects->Linked Server->Providers->Microsoft.Ace.Oledb.12.0

Right click on it and click on “property” from popup menu, then set “Allow process” to true.

Click on “Ok” button and enjoy you OPENQUERY.

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”