September 23, 2011 at 7:24 am
We have a SQL 2005 server with a linked server to an Oracle 10g database and a job that runs every hour. The job calls a single stored procedure which inserts records into a couple of the Oracle tables with a select into.
insert into OUR_ORACLE..XXX.OUR_Table
(XXX, YYY, ZZZ)
(SELECT XXX,YYY,ZZZ
FROM SQLTable
This job has been running for years. Lately, however, it has started to fail about 5 or 6 times a week on the first insert statement. It doesn't fail at the same time of day and sometimes it will fail a few times in 1 day and then be fine for 3 or 4 days.
We get this error when it fails:
The OLE DB Provider "OraOLEDB.Oracle" for linked server "OUR_ORACLE" reported error 0x80004005 aborting the current transaction.
Looking for ideas to troubleshoot.
Thanks.
September 28, 2011 at 10:44 am
What are the data types of the columns being transferred? I'm wondering if an implicit conversion is happening when the data hits Oracle and throws that error. I've always put views on top of both my SQL and Oracle tables and tried my best to avoid any inconsistent data types between the two platforms (e.g. bit, datetime, etc.).
John
September 29, 2011 at 6:55 am
John,
Thanks for the thought. When we run the procedure with the same parameters that failed on the scheduled job, it works. So it's not a data problem. One of our developers is changing the process to not have this single point of failure.
Thanks,
Mike
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply