I have a scheduled job which runs on a SQL Server 2005 database. It runs a stored proc which imports data from a View. This View consists of an OPENQUERY to a linked Oracle server.
I have no control over the Oracle server - it is both geographically and virtually separated from the Sql Server installation. The View just has read-only access and I grab the data from it every 30 mins.
On occasion, the connection to the linked server will either drop, hang or be otherwise unresponsive. When this happens, the Job (on SQL Server) also hangs, waiting indefinitely for the connection to return. The Job never fails nor completes in this scenario and the first I know about it is when users complain about missing data.
Querying a database on the same server fails as expected if there's a connection problem - it's only when OPENQUERY is used on the linked Oracle server that it loses its 'awareness' of the connection.
Is there a way to make the job realise the connection to the Linked Server has dropped and fail accordingly? Or do I have to create a second Job to check that the first one hasn't hung?
