OpenQuery from SQL Server to server hangs endlessly if connection is lost

Question

I have a scheduled task that runs in a SQL Server 2005 database. It runs a stored procedure that imports data from a view. This view consists of OPENQUERY for the linked Oracle server.

I have no control over the Oracle server - it is geographically and practically separate from installing the Sql server. View has read-only access, and I get data from it every 30 minutes.

In some cases, the connection to the linked server will either drop, or hang, or otherwise be out of date. When this happens, Job (on SQL Server) also freezes, waiting for an indefinite time to return the connection. Work is never interrupted or terminated in this scenario, and the first thing I know about it is when users complain about missing data.

A database query on the same server fails as expected if there is a connection problem - only when OPENQUERY used on the linked Oracle server does it lose its "awareness" of the connection.

Is there a way to make the work realize that the connection to the Linked Server has dropped and failed accordingly? Or do I need to create a second task to verify that the first one was not hanging?

Answer 1

We had similar problems for long operations. The operation will be launched (as well as its work done and completed), but the call has not returned. In the end, we were able to trace this to the firewall that closes the connection to the Oracle server.

We could prevent this by using the SQLNET.EXPIRE_TIME parameter on the server, which periodically "pings" the client and, therefore, blocks the firewall. Perhaps this also helps in your case.