How to resume execution in stored procedure even when linked SQL Server does not exist

• Manu Kapoor

  SSC Journeyman

  Points: 88

  More actions

  April 11, 2009 at 7:37 pm

  #208064

  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