September 11, 2015 at 12:25 am
HI,
We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server .
Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
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 "<linked server name>".
Please help to resolve above issue.
September 11, 2015 at 6:14 am
How are you doing the UPDATE?
Over the years I have found the whole Oracle Linked Server thingie to be very fragile, and what seems to have worked for us is using OPENQUERY with only a pass-through query and the results being stored into a Staging table (i.e. the OPENQUERY is NOT also joined to other, local, tables)
I have no idea if that level of abstraction / caution is actually required! just we've found that works for us.
So something like this:
DECLARE @MySQL Nvarchar(4000)
SET @MySQL = 'SELECT Col1, Col2, ... FROM MyOracleTable WHERE Col3 = ...'
INSERT INTO #MyTempTable
SELECT *
FROM OPENQUERY(LinkedOracleServer, @MySQL)
UPDATE U
SET Col1 = T.Col1
FROM #MyTempTable AS T
JOIN dbo.MyLocalTable AS U
ON U.SomePKey = T.SomePKey
WHERE Col1 <> T.Col1 ...
September 14, 2015 at 1:46 am
Thanks for the update.:-)
We were using below query which was working fine .
UPDATE A
SET A.IsPushToOracle=1, A.PUSHED_BY=13362, A.PUSHED_DATE=GETDATE()
--SELECT *
from <sql table > A
inner join openquery (<linked serve query >')B
ON CONVERT(VARCHAR,B.ATTRIBUTE6) = CONVERT(VARCHAR,A.ROWID)
WHERE ISNULL(IsPushToOracle,0)=0 AND isnull (B.USER_JE_SOURCE_NAME,'')='<username>'
We already provided the <#temptable > solution to the user and its working fine.
but the reason is still unknown why its stop working suddenly .
September 14, 2015 at 2:00 am
Longshot:
Datatype difference one a column(s) between #TEMP table and Oracle's table?
I suspect that in the Old Way a change to Oracle datatype would just make a change to an Implicit Conversion (which may or may not be valid, but perhaps you would get a better error message?)
Perhaps the current way SQL sends some information about what datatype it expects the columns to be, and the middle-ware barfs if they don't align well.
Some Kind Person needs to dramatically improve the quality of messages that fall out of these types of issues. Every time I get one it takes hours/days/weeks to solve where a simple "You can't do this because A does not equal B" would save huge amounts of time. Perhaps there is some verbose LOG data somewhere, but if there is I've never found it.
FWIW we ONLY ever do this with SELECT INTO #TEMP FROM OPENQUERY and then JOIN #TEMP to Local Tables as we regard it as being more controllable. For us it has been the more reliably way ... sorry that's not the case for you.
September 14, 2015 at 2:13 am
SELECT INTO #TEMP FROM OPENQUERY
Sorry, I meant
INSERT INTO #TEMP
SELECT
FROM OPENQUERY
as you have it.
September 14, 2015 at 2:15 am
Thanks for the reply
There is no change in datatypes for both platforms.
We have raised the issue with Oracle and they confirm that its bug
Please find bug details as below.
"Bug 3321984 : ORA-1403 AFTER CALLING MOVEFIRST ON A RECORDSET"
which is limitation in "Oracle Provider for OLE DB" driver.
Below is workaround which you can apply in environment .
1.use Microsoft OLEDB, Microsoft ODBC, or Oracle ODBC (microsoft ODBC takes 11 minutes to update 10 records which is not feasible in production environment .)
2.get all oracle data in SQL servers temp table and after that you can update data in sql server .(works in my case .)
September 14, 2015 at 4:30 am
haridatpurohit (9/14/2015)
2.get all oracle data in SQL servers temp table and after that you can update data in sql server .(works in my case .)
Over the years we've found that a lot less "fragile" when connecting to A/N/Other database brand.
Although we might well have been encountering that, or another, bug - assuming it was a genuine data issue!
September 14, 2015 at 6:31 am
Moving the Oracle data into a temp table also gives you the opportunity to examine it for loss due to data type problems. At least there's a way to deal with the confirmed ORA-1403 error. I've found them to not usually be a long shot, but instead the norm. Good job finding the error and solution. You now have a way forward.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply