Post reply

Linked Server - OLE/DB Provider ''MSDAORA''

  • December 4, 2006 at 3:10 am

    #76624

    We have created linked server between SQL Server 2000 and Oracle 10g in offshore environment as well as onsite environment,both the environments are almost IDENTICAL.Basically, the whole execution happens on Oracle and the output is passed to SQL Server.When the below query is executed in offshore environment it is returning values perfectly.

    SELECT * FROM OPENQUERY(ORACLE_LINK,'{Call TEST.IVR.lo_Dlr_GetInfo(55,{resultset 2,p_ReturnVal})}')

    Here: ORACLE_LINK - is the Linked Server Name,

    TEST - Oracle Schema Name

    IVR - Oracle Package Name

    But when the same query is executed on onsite environment it is raising the following error.

    Server: Msg 7321, Level 16, State 2, Line 1

    An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.

    OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare:repare returned 0x80040e14].

    The environment details are:

    Offshore Environment(Successfully returning values):

    1)Operating System - Windows 2000 Advanced Server

    2)Service pack on Operating System - SP4

    3)Version on MSDORA.dll(C:\ProgramFiles\CommonFiles\System\OLEDB) - 2.81.1117.0

    4)Version on tstbestsql1---SQL Database - SQL Server 2000 Enterprise Edition

    5)ServicePack on tstbestsql1---SQL Database - 8.00.2040 (SP3a)

    Onsite Environment:

    1)Operating System - Windows 2000 Advanced Server

    2)Service pack on Operating System - SP4

    3)Version on MSDORA.dll(C:\ProgramFiles\CommonFiles\System\OLEDB) - 2.81.1117.0

    4)Version on tstbestsql1---SQL Database - SQL Server 2000 Enterprise Edition

    5)ServicePack on tstbestsql1---SQL Database - 8.00.2040 (SP4)

    We are able to execute if we are executing the query as

    SELECT * FROM OPENQUERY(ORACLE_LINK,'select column1 from table1') on the Onshore server and it is perfectly returning the value.I believe the problem might be with "Call" if anyone can help with it that will be great.

    Thanks,

    Raahul

  • December 7, 2006 at 8:00 am

    This was removed by the editor as SPAM

  • December 7, 2006 at 11:59 am

    #676705

    Just to be sure, you have made sure that the link is valid on the "onsite" server correct?

  • December 7, 2006 at 1:04 pm

    #676726

    Have you checked the entry for oracle server in sql server tnsnames,ora file...?

  • December 7, 2006 at 10:09 pm

    #676816

    Folks,

        We NAILED the issue,it was a simple one.The problem was that the SQL Server DB user was not provided with EXECUTE priviledges beacuse of which it was throwing exeception only while calling Oracle procedure.Anyways,thanks for the support guys.....

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply