SQL Server : Job not reconnecting to restarted Linked Server

Question

The problem

To keep two tables across two servers in sync, we have a trigger that adds a change to a MergeTable. Every so often, a merging job runs on SQL Server to push the changes to the Progress linked server.

I was testing connectivity issues when I ran into this problem:

1. shut down Progress DB
2. job fails as expected
3. start up Progress DB
4. job continues to not connect to Progress DB until manually disabled and re-enabled

I get many multiples of these three messages:

OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Server crash likely.". [SQLSTATE 01000]

OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Socket closed.". [SQLSTATE 01000]

OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Unexpected Network Error. ErrNum = 10054". [SQLSTATE 01000]

What I've tried

I can execute sp_testlinkedserver and get an error, so I can detect WHEN it happens:

Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "PROGRESS_TEST".

I attempted to call sp_dropserver, sp_addlinkedserver, and sp_testlinkedserver in quick succession, which does not throw an error, but the job thinks it successfully completes when it didn't.

I'm quite new to SQL/T-SQL. There's no sp_reconnectlinkedserver, and I can't replicate this issue outside the job; running the individual procedures works when the database gets back up. I'm thinking this is a session issue, but I wouldn't know where to begin fixing it.

• Jensd: Search the official Progress Knowledgebase: knowledgebase.progress.com (best searched via Google and not via it's internal not-so-great search).
• Bret: Do you think this is a Progress issue? I figured it would have more to do with opening another socket on the MSSQL side than trying to open a dead socket on a shut-down Progress server. In any case, I am more than familiar with the searching on Progress's KB. :P
• Jensd: Perhaps not then. Many people don't even know about the KB. There are some issues regarding complex queries and the likely server crash but perhaps that's not the problem if nothing around the queries is changed....

Answer 1

Querying the linked server seems to get it to reconnect. Calling sp_testlinkedserver doesn't always work, but a select statement will.

Sometimes there is a two minute lag from the start of the downed server to the next successful run of the job. In any case, it's simply a matter of waiting.