Hi guys,
Here's an intriguing one:
I have a stored proc I use in a report to keep an eye on the SQL Agent jobs on all our servers. It goes through a list of linked servers and inserts stuff from that server's job history into a temporary table. I've wrapped each one in a TRY...CATCH and put a bit in the CATCH block to insert a message that the server wasn't available but this doesn't work if the server is not there. The CATCH doesn't happen and the proc fails with a 'Login timeout expired' error.
I've tried using sp_testlinkedserver and that works nicely with the TRY...CATCH on its own but as soon as I include anything to do with the linked server, I'm back to square one.
This code works fine and selects the message 'Login timeout expired' as a resultset:
declare @ServerUp int;
begin try
exec @ServerUp = sp_testlinkedserver brokenlinkedserver;
select @ServerUp as ServerUp;
end try
begin catch
select error_message() as ErrMsg;
end catch
This code fails with the same message as an error:
declare @ServerUp int;
begin try
exec @ServerUp = sp_testlinkedserver brokenlinkedserver;
select 'OK' as OKMsg
from brokenlinkedserver.msdb.dbo.sysjobhistory;
end try
begin catch
select error_message() as ErrMsg;
end catch
It seems that the select from the linked server is causing the batch to error even though it shouldn't have got to that line.
So can you please either help me fix this code or suggest another way I can test whether the linked server is available?
Thanks.
