Sql-server – SQL Server Agent crashes when scheduled procedures make select in DBLink on an offline Oracle database

dblinksql serversql-server-agent

I have SQL Server 2014 and I'm using SQL Server Agent to start some scheduled procedures during the day. These procedures make some selects (in DBlink) on an Oracle database that is handled by another company. Sometimes the Oracle database is offline and my procedures seem to be stucked and I should restart the entire server the make the SQL Server Agent work again.

What am I doing wrong? I suppose there is a sort of timeout to end the procedure and release the SQL Agent.

Example of select:

SELECT * from openquery(positions,
'SELECT * from oracleTableDbLink.POSITION'
)

Best Answer

You can configure a timeout for a query to a remote server. The documentation is here ( https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-remote-query-timeout-server-configuration-option?view=sql-server-ver15 )

You can change this on a database with this script which sets a 100 second timeout

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'remote query timeout', 100 ;  
GO  
RECONFIGURE ;  
GO

There does not seem to be a way to set a query timeout within T-SQL for one statement. This is discussed in more detail here.

I see another user has suggested configuring the sqlnet.ora file on the client on the SQL server however this should be tested as I believe SQL Server will still wait on a response from the client even if the client has terminated the connection.

While you are testing you do not need to restart the server. Just restart the Agent service and that will terminate all running jobs.

You might also consider a test query before doing your real work. Try this, if it returns a value the Oracle database is up.

select 1 from dual;

Due to all places where things can go wrong in a linked server query this answer from Hannah Vernon might assist.

She suggests surrounding the initial test with some error handling that will allow any problems to be found at run time when the dynamic statement runs

BEGIN TRY
    DECLARE @cmd nvarchar(max);
    SET @cmd = 'SELECT * FROM OPENQUERY([server1], ''SELECT 1 AS c;'');';
    EXEC sp_executesql @cmd;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER(), ERROR_MESSAGE();
END CATCH;
Related Question