Linked server connection intermittent

I have some SQL server jobs that connect to a linked DB2 on an AS400 and
fetches some data and inserts into SQL server. The AS400 is set up as a
linked server. The jobs fail at times and work at others. The error message
I get when trying to look at the tables through Enterprise manager is
Error 7399: OLE DB provider 'MSDASQL' reproted 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: ].
The jobs run for 45 mins and 3 secs, (retrying 3 times) then fail.
If I reboot SQL server then jobs run fine, and at times just doing nothing
(no reboot or stop/start of SQL server) also sometimes works.
Intermittent problems are pretty difficult to troubleshoot
on newsgroups. In your case, I've generally seen the issues
due to old drivers, issues on the remote data source. But
other things can be causing the problems as well.
A couple of things you can try to stabilize the connectivity
would be to try the Client Access drivers or providers
instead of using the OLE DB provider for ODBC. That provider
is now considered obsolete by Microsoft.
In addition, when using the Client Access components, you
need to make sure to keep the patch level of the server
where CA is installed up to date with the server. I've run
into issues with linked servers to the 400 when the 400 is
patched and CA isn't updated.
-Sue
On Fri, 14 Jan 2005 14:11:03 -0800, "jddotman"
<jddotman@.discussions.microsoft.com> wrote:

>I have some SQL server jobs that connect to a linked DB2 on an AS400 and
>fetches some data and inserts into SQL server. The AS400 is set up as a
>linked server. The jobs fail at times and work at others. The error message
>I get when trying to look at the tables through Enterprise manager is
>Error 7399: OLE DB provider 'MSDASQL' reproted 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: ].
>The jobs run for 45 mins and 3 secs, (retrying 3 times) then fail.
>If I reboot SQL server then jobs run fine, and at times just doing nothing
>(no reboot or stop/start of SQL server) also sometimes works.
>