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 ofthe reader faced an issue so he sent an email to me by complaining that he isfacing 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 therequired 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 ofthe provider you have used in your linked server. In our case, we have used “Microsoft.ACE.OLEDB.12.0” so we have to set theproperty of the same.
In your SSMS, go to “Object Explorer” and follow the belowpath:
Server->Server Objects->LinkedServer->Providers->Microsoft.Ace.Oledb.12.0
Right click on it and click on “property” from popup menu, thenset “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 butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

OpenRowSet and OpenQuery in SQL Server 2005/2008

Said both of the techniques (OpenRowSetand OpenQuery) are almost same and may confuse many developers. I have beenasked many time personally by few of my team member in recent years as well asI have seen the same question in few forums like Experts-Exchange and Forums.Asp.Net.
Basically both commands requires to access remote data fromother data source like other SQL Server or may be Oracle or may be Access orExcel or anything else. Basically both of these commands requires connectioninformation of source database, table information etc.
OpenQuery gets all these information from LinkedServer where as in OpenRowSet,you have to provide all these information at run time. There is no otherdifference.
Syntaxes of both the commands from Microsoft’s website:
OPENROWSET
({ ‘provider_name’ , { ‘datasource’ ; ‘user_id’ ; ‘password’
   |’provider_string’ }
   ,{   [ catalog. ] [ schema. ] object
       | ‘query’
     }
   |BULK ‘data_file’ ,
       { FORMATFILE= ‘format_file_path’ [ ]
       | SINGLE_BLOB| SINGLE_CLOB | SINGLE_NCLOB }
})
::=
   [, CODEPAGE = { ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’ } ]
   [, ERRORFILE = ‘file_name’ ]
   [, FIRSTROW = first_row ]
   [, LASTROW = last_row ]
   [, MAXERRORS = maximum_errors ]
   [, ROWS_PER_BATCH = rows_per_batch ]
   [, ORDER ( { column [ ASC | DESC ] } [ ,…n ] ) [ UNIQUE ]
OpenSource:
OPENQUERY ( linked_server ,'query' )
Let us see how does it works, we are going to create oneaccess file named “acc1.accdb” and will create one Employee table inside “acc1.accdb”file which will be stored in D drive of computer. You can have your own accessfile at your desired location.
Here is the query for the same:
–add link server
exec sp_addlinkedserver
@server=‘LinkToAceess’,
@srvproduct=‘AccessDatabase’,
–if you haveolder version of access then kindly use old jet provider
–Microsoft.Jet.OLEDB.4.0
@provider=‘Microsoft.ACE.OLEDB.12.0’,
@datasrc=‘D:\db1.accdb’
GO
–add MDB’scredental, if any
EXEC sp_addlinkedsrvlogin ‘LinkToAceess’, ‘false’
exec sp_serveroption [LinkToAceess],‘Data Access’,‘true’
–check whether’LinkToAccess’ has been added
select * from sys.servers
–list alltables available in Testing.MDB
exec sp_tables_ex ‘LinkToAceess’
–now query withOPENSOURCE
SELECT * FROM OPENQUERY(LinkToAceess, ‘SELECT id,empname FROMemployee’)
–now query withOPENROWSET,
–which will notuse our linked server
SELECT *
   FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
      ‘D:\db1.accdb’;
      ;,Employee);
     
–if you getfollowing error
–Msg 15281,Level 16, State 1, Line 1
–SQL Serverblocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad HocDistributed Queries’ because this component is turned off as part of thesecurity configuration for this server. A system administrator can enable theuse of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more informationabout enabling ‘Ad Hoc Distributed Queries’, see “Surface AreaConfiguration” in SQL Server Books Online.
–you have toenable “Ad Hoc Distributed Queries”
sp_configure ‘show advanced options’,1
reconfigure
GO
sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure
GO
–now try againsame query
SELECT * FROM
OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
      ‘D:\db1.accdb’;
      ‘admin’;,Employee);
Hope you have enjoyed!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile

OpenRowSet and OpenQuery in SQL Server 2005/2008

Said both of the techniques (OpenRowSetand OpenQuery) are almost same and may confuse many developers. I have beenasked many time personally by few of my team member in recent years as well asI have seen the same question in few forums like Experts-Exchange and Forums.Asp.Net.
Basically both commands requires to access remote data fromother data source like other SQL Server or may be Oracle or may be Access orExcel or anything else. Basically both of these commands requires connectioninformation of source database, table information etc.
OpenQuery gets all these information from LinkedServer where as in OpenRowSet,you have to provide all these information at run time. There is no otherdifference.
Syntaxes of both the commands from Microsoft’s website:
OPENROWSET
({ ‘provider_name’ , { ‘datasource’ ; ‘user_id’ ; ‘password’
   |’provider_string’ }
   ,{   [ catalog. ] [ schema. ] object
       | ‘query’
     }
   |BULK ‘data_file’ ,
       { FORMATFILE= ‘format_file_path’ [ ]
       | SINGLE_BLOB| SINGLE_CLOB | SINGLE_NCLOB }
})
::=
   [, CODEPAGE = { ‘ACP’ | ‘OEM’ | ‘RAW’ | ‘code_page’ } ]
   [, ERRORFILE = ‘file_name’ ]
   [, FIRSTROW = first_row ]
   [, LASTROW = last_row ]
   [, MAXERRORS = maximum_errors ]
   [, ROWS_PER_BATCH = rows_per_batch ]
   [, ORDER ( { column [ ASC | DESC ] } [ ,…n ] ) [ UNIQUE ]
OpenSource:
OPENQUERY ( linked_server ,'query' )
Let us see how does it works, we are going to create oneaccess file named “acc1.accdb” and will create one Employee table inside “acc1.accdb”file which will be stored in D drive of computer. You can have your own accessfile at your desired location.
Here is the query for the same:
–add link server
exec sp_addlinkedserver
@server=‘LinkToAceess’,
@srvproduct=‘AccessDatabase’,
–if you haveolder version of access then kindly use old jet provider
–Microsoft.Jet.OLEDB.4.0
@provider=‘Microsoft.ACE.OLEDB.12.0’,
@datasrc=‘D:\db1.accdb’
GO
–add MDB’scredental, if any
EXEC sp_addlinkedsrvlogin ‘LinkToAceess’, ‘false’
exec sp_serveroption [LinkToAceess],‘Data Access’,‘true’
–check whether’LinkToAccess’ has been added
select * from sys.servers
–list alltables available in Testing.MDB
exec sp_tables_ex ‘LinkToAceess’
–now query withOPENSOURCE
SELECT * FROM OPENQUERY(LinkToAceess, ‘SELECT id,empname FROMemployee’)
–now query withOPENROWSET,
–which will notuse our linked server
SELECT *
   FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
      ‘D:\db1.accdb’;
      ;,Employee);
     
–if you getfollowing error
–Msg 15281,Level 16, State 1, Line 1
–SQL Serverblocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad HocDistributed Queries’ because this component is turned off as part of thesecurity configuration for this server. A system administrator can enable theuse of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more informationabout enabling ‘Ad Hoc Distributed Queries’, see “Surface AreaConfiguration” in SQL Server Books Online.
–you have toenable “Ad Hoc Distributed Queries”
sp_configure ‘show advanced options’,1
reconfigure
GO
sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure
GO
–now try againsame query
SELECT * FROM
OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
      ‘D:\db1.accdb’;
      ‘admin’;,Employee);
Hope you have enjoyed!!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile