Post reply

How to test that a linked server is running?

  • 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? 

     

  • 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?

     

  • 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.

  • 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. 

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply