Create procedure usp_lsping(@nf nchar = 'N', @wtn nchar = 'E')
WITH ENCRYPTION
as
-- ===========================================================================
-- Author:Mark Huber
-- Version:1.0
-- Create date: Oct 19, 2011
-- Description:Description to ping the linked server and see if there alive
-- ===========================================================================
-- Parameters
-- @NF - did you want to be notified Y - yes N - no
-- @wtn - when to notofiy E - on error S - on Succes B - on Both
--
-- EXAMPLE USE
--DECLARE@return_value int
--EXEC@return_value = [dbo].[usp_lsping]
--@nf = N'Y',
--@wtn = N'E'
--SELECT'Return Value' = @return_value
-- ===========================================================================
--
set nocount on
BEGIN
DECLARE @LSrvrs TABLE
(
SrvrID int IDENTITY(1,1) PRIMARY KEY,
SrvName nvarchar(128)
)
insert into @LSrvrs
select srvname from sys.sysservers
where srvname != CONVERT(nvarchar(128), SERVERPROPERTY('servername'));
;
--
declare @maxloocnt int;
declare @loopcnt int;
declare @srvr nvarchar(128);
declare @retval int;
declare @CLR nvarchar(4)
declare @msg nvarchar(MAX)
declare @errchk int;
--
set @errchk = 0;
set @CLR = char(13)+char(10);
select @maxloocnt = count(*) from @LSrvrs;
set @loopcnt = 1;
set @msg = '';
set @msg = @msg + '<Start>'+@Clr
while @loopcnt <= @maxloocnt
begin
select @srvr = srvname from @LSrvrs where SrvrID = @loopcnt;
--select @srvr
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
set @errchk = 99;
end catch;
--if @retval =qwqw 0
set @msg = @msg + @CLR+@srvr + ':' + cast(@retval as nvarchar(100));
set @loopcnt = @loopcnt + 1;
end;
set @msg = @msg + @CLR+'<end>'+@Clr
if @NF = 'Y'
begin
--if @wtn = 'E' or @wtn = 'S' or @wtn = 'B'
--BEGIN
--print 'WTN:'+@wtn
--print '@errchk:'+ CONVERT(nvarchar(128),@errchk)
if (@wtn = 'S' and @errchk = 0) or (@wtn = 'E' and @errchk > 0) or(@wtn = 'B' )
BEGIN
--select @msg;
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'mark.huber@bp.com',@body=@msg,
@subject ='Linked Server Ping'--,@query ='select @msg;',
--@attach_query_result_as_file = 1,@query_attachment_filename ='PingResults.txt'
END;
--else
-- BEGIN
--
-- END
END
--end
--else
--BEGIN
-- '';
--END;
END
set nocount off