How to test that a linked server is running?

• mrhodes2

  SSC Journeyman

  Points: 80

  More actions

  April 1, 2004 at 10:03 am

  #161350

  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? 

   

• wz700

  SSCertifiable

  Points: 7133

  More actions

  April 2, 2004 at 5:56 am

  #501586

  what is the value of print @servererror

  when Server: Msg 17, Level 16, State 1, Line 1 happend?

   

• mssql_rules

  SSCrazy

  Points: 2783

  More actions

  April 2, 2004 at 12:22 pm

  #501659

  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.

• mrhodes2

  SSC Journeyman

  Points: 80

  More actions

  April 5, 2004 at 1:39 pm

  #501799

  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.