It leverages on the system stored procedure sys.sp_testlinkedserver
SET NOCOUNT ON; DECLARE @LinkName NVARCHAR (128), @retval INT, @msg varchar(300) IF (SELECT COUNT (srvname) FROM sys.sysservers WHERE srvname <> @@SERVERNAME) = 0 RETURN DECLARE srvname INSENSITIVE CURSOR FOR (SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME) FOR READ ONLY OPEN srvname FETCH NEXT FROM srvname INTO @LinkName WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRY EXEC @retval = sys.sp_testlinkedserver @LinkName END TRY BEGIN CATCH IF @LinkName IS NULL RETURN SET @retval = SIGN(@@ERROR) IF @retval <> 0 BEGIN SET @msg = 'Unable to connect to the Linked server : ' + @LinkName RAISERROR (@msg, 16, 2 ) END END CATCH FETCH NEXT FROM srvname INTO @LinkName END CLOSE srvname DEALLOCATE srvname
|
4 comments:
Thanks a lot SIVA. Good Work. Keep Rocking... Was searching on net for this kind of script. Finally got yours. Tested it and its working..
I feel SQL and other similar and useful aspects really work to provide information and solutions to the most complex database and useful IT problems.
SQL Server Load Soap API
Post a Comment