May 13, 2003 at 1:51 am
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
--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
Works perfectly - thanks very much!
August 1, 2011 at 5:23 pm
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
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
Thanks - My apologies for not noticing I was in the 2000 forum.
August 1, 2011 at 5:37 pm
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
No, according to this 2005 thread, the availability of a linked server isn't testable using pre-Yukon TSQL.
August 1, 2011 at 5:55 pm
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