April 1, 2004 at 10:03 am
I'm trying to find a way to test that a linked server is actually alive and running. I've tried the following code
declare @sqlcmd varchar(255)
declare @servername varchar(20)
declare @servererror 255
set @servername = 'linkedserver'
set @sqlcmd = 'select * from '+ rtrim(@servername)+'.master.dbo.sysobjects'
print @sqlcmd
exec (@sqlcmd)
set @servererror = @@error
print @servererror
if @servererror = >
.... a whole bunch of code
elseif @servererror = 0
.... do some other stuff
endif
When the server is alive and running, this works great, reports an error of 0 and I know all is well. Problem is when linkedserver is down for what ever reason, this script fails instead of giving following error.
select * from linkedserver.master.dbo.sysobjects
Server: Msg 17, Level 16, State 1, Line 1
SQL Server does not exist or access denied.
How can I test for/or trap that a linked server is alive or dead and act accordingly?
April 2, 2004 at 5:56 am
what is the value of print @servererror
when Server: Msg 17, Level 16, State 1, Line 1 happend?
April 2, 2004 at 12:22 pm
When a linked server is down the TSQL batch exits and there is no way to trap the error. If you expect the code to handle this issue, use a vbscript code with resume on error. I hear, Yukon will have the tsql errorhandling ability to trap these errors.
April 5, 2004 at 1:39 pm
I never see the value of @servererror or @@error, because as mssql_rules stated the TSQL batch exits. Hard to believe that the only option is for the script to just end.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply