1

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'
) 
2

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 Max Vernon might assist.

He 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;
  • Thank you very much, however it seems a global configuration. Is there a way to set a timeout for a specific procedure? – Koba Jan 15 at 8:42
  • When the remote DB is down and I launch "SELECT * from openquery(positions, 'select 1 from dual;' )" I get ''A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)'' – Koba Jan 20 at 13:22
  • @Koba added a reference to a great answer from Max Vernon – kevinsky Jan 20 at 14:08
0

https://www.sqlshack.com/link-sql-server-oracle-database/ ....

Edit the configuration of Oracle Net (sqlnet.ora)

The sqlnet.ora file is the profile configuration file. We will use it to first prioritize the name resolution method, to define the default domain and a time interval to verify that the connection is still active.

Have a look at the following page for further information.

To do so, add the following lines in the sqlnet.ora file.

NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
NAMES.DEFAULT_DOMAIN = LOCALDOM
SQLNET.EXPIRE_TIME = 30

further down in the linked site you will find info on how to resolve "TNS:could not resolve the connect identifier specified."

  • Thank you for this info... Is there a way to set a timeout for a specific procedure? – Koba Jan 17 at 11:19
  • No - in the described scenario of a Link between 2 different database servers its faaar too complex to define timeouts per specific procedure. – eagle275 Jan 17 at 11:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.