Import job fails on Linked Server

Question

Import job fails on Linked Server

cris2709

SSC Journeyman

Points: 87
More actions
December 15, 2005 at 8:50 am

#96074

We are running SQL Server 2000 on Windows Server 2003 SE SP1.
We have Oracle 9i client installed on that server and using the drivers provided by that client.
We have set-up a job on the SQL server to import selected tables from Oracle 9i into SQL Server. The job executes a stored procedure.

Every other day, when we check the log that job shows up as failed with the following error message(s).

Executed as user: UAWDENSQL01\SQLAdmin. OLE DB provider 'OraOLEDB.Oracle' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.

OR

Executed as user: UAWDENSQL01\SQLAdmin. Could not get the data of the row from the OLE DB provider 'OraOLEDB.Oracle'. [SQLSTATE 42000] (Error 7346) OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' IRowset::GetData returned 0x80040e4b]. [SQLSTATE 01000] (Error 7300). The step failed.

After the job fails, when you try to view the tables on the linked server, we always get an error message - meaning link between SQL and Oracle is broken.

We have no choice but to stop and restart all services on the SQL server, to fix the above issue.

If anyone has a solution for this, please kindly advise.

Many Thanks,
Cris

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736602 More actions · Answer 1

You might need to burn the $249 and call PSS. I'd bet that the driver is flaking somehow. We got some strange results from DB2 with the Client Access driver a few years ago and ended up just restarting the server periodically because the driver would leak memory.

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

My experience is with Oracle 8i, and I found that for SQL Server 2000 sp3 access to Oracle, I had better results using the Microsoft Oracle drivers than the Oracle drivers with a linked server.

I'll bet that PSS will tell you this and still charge you the $249.

Andy

David le Quesne SSCertifiable Points: 5229 More actions · Answer 3

Hi,

depending how long your queries on the linked Oracle server take, it might be worth having a look at the properties of your SQL server in EM, on the connections tab look at the remote server connections query time-out and check if it is long enough. I think the default is 10 minutes.

David

If it ain't broke, don't fix it...