July 11, 2008 at 7:20 am
Hi everyone, i need some help about the next:
Environment: MSSQL2005 - SP2 / Oracle Client 10.2.0.2.20
Sintoms:
When i run specific query the following messages appear:
"OLE DB provider "OraOLEDB.Oracle" for linked server "OracleLinkedServer" returned message "ORA-01403: no data found".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "OracleLinkedServer".
Query:
Update SQLTable Set NX.Field1='I'
From OracleLinkedServer..OracleTable.FieldA AS ORA
Inner Join SQLTable AS NX
ON ORA.NOV_ID = NX.NOV_ID
AND NX.Field1='L'
AND ORA.CHE_USER IS NOT NULL
AND ORA.CHE_DATE IS NOT NULL
When ResultSet = 1, the query works ok, but in > 1 the error appear-
This other query work ok:
Update SQLTable Set NX.Field1='I'
WHERE NX.NOV_ID IN
( select NX.NOV_ID From OracleLinkedServer..OracleTable.FieldA AS ORA
Inner Join SQLTable AS NX
ON ORA.NOV_ID = NX.NOV_ID
AND NX.Field1='L'
AND ORA.CHE_USER IS NOT NULL
AND ORA.CHE_DATE IS NOT NULL
)
In a different mssql server with same environment the first query works fine.
Any ideas?
Thanks in advanced.
July 11, 2008 at 7:43 am
Try using OpenQuery something like this:
SELECT * FROM OPENQUERY([OracleLinkedServer], 'Update SQLTable Set NX.Field1=''I''
From ..OracleTable.FieldA AS ORA
Inner Join SQLTable AS NX
ON ORA.NOV_ID = NX.NOV_ID
AND NX.Field1=''L''
AND ORA.CHE_USER IS NOT NULL
AND ORA.CHE_DATE IS NOT NULL') AS Query
Try changing this "..OracleTable" to a Fully Qualified name like dbo.OracleTable.
Maninder
www.dbanation.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply