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.
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 NameIVR - Oracle Package NameBut when the same query is executed on onsite environment it is raising the following error.Server: Msg 7321, Level 16, State 2, Line 1An 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 Server2)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 Server2)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 AthalyeIndia."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 asSELECT * 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 LarssonHelsingborg, Sweden |
 |
|
raahul_v
Starting Member
6 Posts |
Posted - 2006-12-04 : 05:12:33
|
EXEC doesn't work still,the same error. |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-04 : 05:17:10
|
Maybe you should try www.dbforum.com because their ORACLE competence is higher.After all, this is a MS SQL forum.Peter LarssonHelsingborg, Sweden |
 |
|
pootle_flump
1064 Posts |
Posted - 2006-12-04 : 08:08:07
|
FYI - http://www.dbforums.com/showthread.php?t=1612324 |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-04 : 08:09:14
|
Peter LarssonHelsingborg, Sweden |
 |
|
|
|
|
|
|