Trapping "Server does not exist" error

  • 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

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

  • Works perfectly - thanks very much!

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

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

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

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

  • 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

  • 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