Post reply

Trapping "Server does not exist" error

  • May 13, 2003 at 1:51 am

    #58968

    I have a stored procedure that accesses a linked server. If the linked server is down for any reason or if the service has been stopped I get "SQL Server does not exist or access is denied"

    Is there any way to trap this error and prevent the SP crashing? My attempts with @@error and the like haven't worked.

    Thanks

  • May 13, 2003 at 11:13 am

    #457009

    --Check if linked server is up and running.

    --Uses trusted connection. -t = timeout in seconds

    declare @iServerStatus int,

    @sSQL varchar(200),

    @sLinkName varchar(30)

    set @sLinkName = 'MyLinkName'

    set @sSQL = 'osql -E -t 10 -S ' + @sLinkName + ' -q "SELECT @@ServerName"'

    exec @iServerStatus = master..xp_cmdShell @sSQL

    IF (@iServerStatus = 0)

    print 'Success'

    ELSE

    print 'Failure'

  • May 15, 2003 at 1:36 am

    #457215

    Works perfectly - thanks very much!

  • August 1, 2011 at 5:23 pm

    #1361902

    This looks like a good solution for environments where the command shell is permitted.

    I need a solution that doesn't use the command shell.

  • August 1, 2011 at 5:27 pm

    #1361903

    Just a guess... have you tried try... catch?

    I'm guessin ghere because the error might be too severe to catch there. It also requires sql 2005 and this is a 2000 forum.

    If that doesn't help please start a new thread so it gets more exposure.

  • August 1, 2011 at 5:31 pm

    #1361904

    Thanks - My apologies for not noticing I was in the 2000 forum.

  • August 1, 2011 at 5:37 pm

    #1361907

    Carl Grant (8/1/2011)

    Thanks - My apologies for not noticing I was in the 2000 forum.

    NP, did it solve your problem??

    Post the url here if you make a new thread for future googlers.

  • August 1, 2011 at 5:44 pm

    #1361910

    No, according to this 2005 thread, the availability of a linked server isn't testable using pre-Yukon TSQL.

    http://www.sqlservercentral.com/Forums/Topic109509-8-1.aspx

  • August 1, 2011 at 5:55 pm

    #1361911

    Please start a new thread and specify your exact version and cmdshell restrictions and post the link in here..

Viewing 9 posts - 1 through 8 (of 8 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy