Demystifying SQL-Oracle Distributed Query Issues

While running the following query distributed query

select * from openrowset

('OraOLEDB.Oracle','oradb';'scott';'pwd1234$',

'select * from emp')

I got the following error message :

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

This is because by default in SQL Server 2005 Ad Hoc Queries are disabled. You can enable it by opening up SQL Server 2005 Configuration Wizard and click on Surface Area Configuration For Features and under Database Engine click Enable OPENROWSET and OPENDATASOURCE support.

You get the error

Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "OraOLEDB.Oracle" for linked server "(null)".

The reasons why you get this error is because "Allow in Process" is not enabled for the provider. You can enable this option by opening up Linked Server > Providers and checking on "Allow in Process"

This option is needed because SQL Server will pass proper authentication across the remote procedure only when the Ole DB provider is configured for in process.

Comments

• Anonymous
  December 17, 2008
  Allow inprocess is set on our linked server and we are still receiving this error.  We are receiving about 501 rows then get this error and the query quits.  Any suggestions?
• Anonymous
  March 07, 2009
  I have spent hous on this and finaly have fixed my issue.After enabeling "Allow in Process" (above) you should restrat the instance!