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