Linked Server connection fails intermittently

[jddotman]

I have 2 jobs that connect to an AS400/DB2 database and fetches some data to place into a MS SQL server 2000 database.

The jobs are failing intermittenly ( jobs time out after 3 retries, takes about 45 mins.)

When I right click on the linked server->tables icon in Enterprise manager I get the following error.

Error 7399: OLE DB provider 'MSDASQL' reported an error.
Driver's SQLSetConnectAttr failed]
communication link failure. comm rc=12 - CWB0999 - Unexpected error:
unexpected return code 12]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize;;Initialize
returned 0x80004005: ].

If I reboot the MS SQL server the jobs will run fine. This is an unacceptable solution as these are production boxes supporting our web-app.

The strangest part though is the jobs are scheduled to run every 2 hours. The 6,8 am jobs work, the 10 am and 12 pm jobs fail. At this point if I do nothing the jobs will either continue to fail or suddenly start working.

Any ideas?

 

[ecobb]

Is it possible that there's a network connection issue between them? Possibly a card or cable going bad? Of course, if it was I'm sure you would also be seeing everything else dropping connection to the DB Servers as well.



Hope This Helps!

Ecobb
Beer Consumption Analyst

"My work is a game, a very serious game." - M.C. Escher