Linked Server - OLE/DB Provider 'MSDAORA'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Linked Server - OLE/DB Provider 'MSDAORA'

Author

Topic

raahul_v
Starting Member

6 Posts

Posted - 2006-12-04 : 04:38:52

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::Prepare 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)

Please let me know what could be the issue.

Thanks,
Raahul

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 04:49:48

Make sure MSDTC service is running on Onshore machine.

Harsh Athalye
India.
"Nothing is Impossible"

raahul_v
Starting Member

6 Posts

Posted - 2006-12-04 : 04:54:39

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.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 04:55:39

Try EXEC instead.

Peter Larsson
Helsingborg, Sweden

raahul_v
Starting Member

6 Posts

Posted - 2006-12-04 : 05:12:33

EXEC doesn't work

still,the same error.