none
Linked Server error

    Question

  • Hi Team,

    I am trying to create linked server from MS SQL Server to Oracle 12c database instance. I get a message while I do Test connection

    "The test connection to the linked server succeeded"

    However, while I execute SQL query to retrieve data from oracle. It is giving below error

    The OLE DB provider "SQL Server" for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure.
    Cannot get the data of the row from the OLE DB provider "SQL Server" for linked server "(null)".  (Microsoft SQL Server, Error: 7399)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.1601&EvtSrc=MSSQLServer&EvtID=7399&LinkId=20476

    Here is the steps used for creating linked server

    exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'AllowInProcess', 1


    exec master.dbo.sp_MSset_oledb_prop 'ORAOLEDB.Oracle', N'DynamicParameters', 1


    exec sp_addlinkedserver N'MyOracle', 'Oracle', 'ORAOLEDB.Oracle', N'//172.23.225.204/ODSTDWDB'

    exec master.dbo.sp_serveroption @server=N'MyOracle', @optname=N'rpc out', @optvalue=N'true'

    exec sp_addlinkedsrvlogin @rmtsrvname='MyOracle', @useself=N'FALSE', @rmtuser=N'xxxxx', @rmtpassword='xxxxxx' 

    Regards

    VGR

    Tuesday, June 12, 2018 10:57 AM

All replies

  • Can you post the query that is failing? Are you using OPENQUERY?

    What version of Oracle client you have on the SQL box? Can you do a "TNSPING <TnsAlias>" and see if it returns OK?

    See if you missed any step from this article.

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, June 12, 2018 11:34 AM
  • Hi Mohsin,

    Please find the query used 

    exec ('select * from IPPBODSCBS.GENERAL_ACCT_MAST_TABLE') at MyOracle.

    two oracle homes for 12C version 12.1.0.2 – one home will be 32 bit and anther will be 64 bit.

    In addition installed ODAC – 32 as well as 64 bit.

    Regards

    VGR


    Tuesday, June 12, 2018 1:14 PM
  • Can you use "OPENQUERY" by following the example in the link I posted in my previous response? See if that makes any difference.

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, June 12, 2018 3:50 PM
  • Hi Mohsin,

    Issue still exist when I use OPENQUERY.

    Regards

    VGR

    Tuesday, June 12, 2018 4:56 PM
  • The OLE DB provider "SQL Server" for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure.
    Cannot get the data of the row from the OLE DB provider "SQL Server" for linked server "(null)".  (Microsoft SQL Server, Error: 7399)

    If it is a linked server to Oracle, why does it say SQL Server in the error message?

    Tuesday, June 12, 2018 9:27 PM