5

I need to write a procedure to collect data from several remote servers,

I use linkedservers and OPENQUERY to gather data from servers, but sometimes I lose connection to some servers or I simply can't connect them (e.g. remote server is offline)- and OPENQUERY results in time-outs in these cases.

So I wanted to check linkedservers connectivity first and then if it's successful run the query, if not just move on to next the remote server.

I tried to put OPENQUERY in TRY - CATCH but it still returned me time-out error, sp_testlinkedserver procedure also returned me time-out error.

I'd really appreciate any help.

dogant
  • 1,376
  • 1
  • 10
  • 23

2 Answers2

17

You can use below script

https://web.archive.org/web/20190201090243/https://blogs.msdn.microsoft.com/sqltips/2005/06/07/test-linked-server-connection-settings/

declare @srvr nvarchar(128), @retval int;
set @srvr = 'my_linked_srvr';
begin try
    exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval <> 0
  raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
HotN
  • 4,216
  • 3
  • 40
  • 51
Thakur
  • 1,890
  • 5
  • 23
  • 33
  • It takes so long to time out though. I'm looking at 40" minimum, whatever the linked server settings and the "connect timeout" server option. – Doc Mar 06 '15 at 13:51
  • Same behaviour, this code works but takes 47'' even if I have set a connection timeout of 15 seconds on linked server properties – Ezin82 Jul 31 '19 at 12:19
  • https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-testlinkedserver-transact-sql?view=sql-server-ver15 – Nader Gharibian Fard Jan 18 '21 at 05:54
  • How could we add a while loop to test connectivity for all the available link servers instead of one variable? – Haans Sep 09 '21 at 07:08
2
USE master;  
GO  
sp_testlinkedserver [LINKED_SERVER_NAME];  
GO

or

SELECT * FROM OPENQUERY(LINKED_SERVER_NAME , 'select name DATABASE_NAME ,SYS_CONTEXT (''USERENV'', ''SESSION_USER'') USERNAME, host_name from v$database, dual, v$instance') ;
Aashutosh Kumar
  • 615
  • 9
  • 13