Sunday 26 October 2014

Msg 7399, Level 16, State 1, Line 1 / Msg 7303, Level 16, State 1, Line 1 in MS SQL Server dblink

Error:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL_TEST" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "ORCL_TEST".


Solution:

The solution to troubleshoot error is to set OraOleDB.Oracle provider Allow inprocess option.In order to configure Allow inprocess option goto OraOleDB Oracle provider properties.


Be sure that the Allow inprocess option is marked.



Allow inprocess

SQL Server allows the provider to be instantiated as an in-process server. When this option is not set, the default behavior is to instantiate the provider outside the SQL Server process. Instantiating the provider outside the SQL Server process protects the SQL Server process from errors in the provider. When the provider is instantiated outside the SQL Server process, updates or inserts referencing long columns (text, ntext, or image) are not allowed.




Now execute the following sql statements on Oracle databases using SQL Server Linked Server.

SELECT
 [FIRST_NAME], [LAST_NAME]
FROM [ORCL_TEST]..[HR].[EMPLOYEES]



No comments:

Post a Comment