Linked Server - OLE/DB Provider ''MSDAORA''
-
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
-
i'm venturing a guess here, but i've had trouble with issues where the table being called contains oracle blob fields.
the oracle 8/9 drivers, as well as the microsoft MSDBORA drivers will work on a table as long as the table does not contain a blob field...the way blobs are handled changed in oracle 10, and you need an updated driver.
if you install the oracle client connectivity tools on the sql server, then you can use the OraOledb driver instead. problem is, it replaces the previous version, so you can only connect to 10G instances with that driver....to connect to a 9, you'd need to uninstall the 10 client connectivity tools and reinstall the 9 drivers.
so select column1 from table1 works fine
but select * from table1, where any column in the db might be a blob/clob would fail.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowell, I really appreciate that you have shared your experience.But,as put in my post I am able to execute the query and on Offshore environment but the same is not working on Onsite environment.Infact from Offshore and Onsite we connect to same Oracle schema and moreover there are no BLOB columns and in no where in our code we are calling "select * " because we are calling only the required columns
"select col1,col2 ..."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply