Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to resume execution in stored procedure even when linked SQL Server does not exist Expand / Collapse
Author
Message
Posted Saturday, April 11, 2009 7:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 3, 2011 2:32 AM
Points: 4, Visits: 168
Hi Everyone,

I have a stored procedure that picks-up the linked server names from "sysservers" system table and uses cursor to build the insert query that takes data from the linked server database.

******************************************
For example (Short code):

SET @SSQL = N'
INSERT INTO DBA_MON.dbo.DBA_SQLErrorLogContents_tbl
( EntryID, servername, LogDate, ProcessInfo, LogText, ContinuationRow, alerted )

Select EntryID, servername, LogDate, ProcessInfo, LogText, ContinuationRow, alerted From [' + @ServerName+ '].DBA_MON.dbo.DBA_SQLErrorLogContents_tbl
WHERE EntryID >' + CAST ( @maxEntryID AS NVARCHAR)


-- PRINT @SSQL
EXEC sp_ExecuteSQL @SSQL

************************************

The problem happens when a specified linked server is not running in which case it fails and stops executing the next linked sever which might just be running fine.

I tried using @@Error, but it seems that it can not handle "SQL Server does not exist" kind of errors.

Any help will be greatly appreciated.


Regards

Manu Kapoor
Post #695370
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse