May 15, 2012

sp_MSgetalertinfo (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetalertinfo(bit @includeaddresses)

MetaData:

 create procedure sys.sp_MSgetalertinfo  
@includeaddresses bit = 0
as
-- Return all alert info at one go, for performance reasons. --
declare @FailSafeOperator nvarchar(255)
declare @NotificationMethod int
declare @ForwardingServer nvarchar(255)
declare @ForwardingSeverity int
declare @ForwardAlways int
declare @PagerToTemplate nvarchar(255)
declare @PagerCCTemplate nvarchar(255)
declare @PagerSubjectTemplate nvarchar(255)
declare @PagerSendSubjectOnly int
declare @FailSafeEmailAddress nvarchar(255)
declare @FailSafePagerAddress nvarchar(255)
declare @FailSafeNetSendAddress nvarchar(255)

exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeOperator', @param = @FailSafeOperator OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertNotificationMethod', @param = @NotificationMethod OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingServer', @param = @ForwardingServer OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingSeverity', @param = @ForwardingSeverity OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardAlways', @param = @ForwardAlways OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerToTemplate', @param = @PagerToTemplate OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerCCTemplate', @param = @PagerCCTemplate OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSubjectTemplate', @param = @PagerSubjectTemplate OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSendSubjectOnly', @param = @PagerSendSubjectOnly OUT, @no_output = N'no_output'

if (@includeaddresses <> 0) begin
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeEmailAddress', @param = @FailSafeEmailAddress OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafePagerAddress', @param = @FailSafePagerAddress OUT, @no_output = N'no_output'
exec sys.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeNetSendAddress', @param = @FailSafeNetSendAddress OUT, @no_output = N'no_output'
end

select
AlertFailSafeOperator = @FailSafeOperator,
AlertNotificationMethod = @NotificationMethod,
AlertForwardingServer = @ForwardingServer,
AlertForwardingSeverity = @ForwardingSeverity,
AlertPagerToTemplate = @PagerToTemplate,
AlertPagerCCTemplate = @PagerCCTemplate,
AlertPagerSubjectTemplate = @PagerSubjectTemplate,
AlertPagerSendSubjectOnly = @PagerSendSubjectOnly,
AlertForwardAlways = ISNULL(@ForwardAlways, 0)

if (@includeaddresses <> 0)
select
AlertFailSafeEmailAddress = @FailSafeEmailAddress,
AlertFailSafePagerAddress = @FailSafePagerAddress,
AlertFailSafeNetSendAddress = @FailSafeNetSendAddress

No comments:

Post a Comment

Total Pageviews