October 13, 2009 at 10:12 am
Hi All,
The issue is i have to update Oracle's table from Sql Server by comparing with local Sql Server's table.
For this purpose i have created linked server for Oracle server.
Through the linked server i have failed to update the oracle table so i have created view(REGIS_ORA_RXL_HR_STG_EMPLOYEE SET) for that Oracle's table in SQL Server.
After this i have written a join query on view and Sql server table for updating oracle's table.
Code:
UPDATE REGIS_ORA_RXL_HR_STG_EMPLOYEE SET
REGIS_ORA_RXL_HR_STG_EMPLOYEE.INTERFACED = '',
REGIS_ORA_RXL_HR_STG_EMPLOYEE.END_DATE = [termination date],
REGIS_ORA_RXL_HR_STG_EMPLOYEE.INTERFACE_DATE = NULL
FROM REGIS_ORA_RXL_HR_STG_EMPLOYEE,sql_tbl
WHERE
REGIS_ORA_RXL_HR_STG_EMPLOYEE.PKEY = sql_tbl.FLXID
AND REGIS_ORA_RXL_HR_STG_EMPLOYEE.END_DATE IS NULL
And i m facing the below error..
Error
Could not fetch a row using a bookmark from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowsetLocate::GetRowsByBookmark returned 0x80040e21: ].
Please help me, it is very IMP and urgent for me.
Thanks in advance.
October 13, 2009 at 12:41 pm
Is your SQL box 64 bit?
If so, try this:
Install the Oracle ODAC (available on the Oracle Technology Network) and a patch (#5043675 available on Metalink) that fixes a bug that causes updates and deletes to fail.
You could also try to use the OraOLEDB instead of the MSDAORA connector.
And lastly, try running a Select statement on the Oracle box before running the update statement (it may just want to create a resultset first).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 20, 2009 at 8:52 am
My SQLserver box is 32 bit..
Still the issue is not resolved...
please help me out....
October 20, 2009 at 8:56 am
F Y I
I have already installed the Oracle ODAC .
and dont have permission to change linkserver provider from MSDAORA to OraOLEDB since there are so many other jobs using it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy