May 29, 2012

sp_MSsetalertinfo (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_MSsetalertinfo(nvarchar @failsafeoperator
, int @notificationmethod
, nvarchar @forwardingserver
, int @forwardingseverity
, nvarchar @pagertotemplate
, nvarchar @pagercctemplate
, nvarchar @pagersubjecttemplate
, int @pagersendsubjectonly
, nvarchar @failsafeemailaddress
, nvarchar @failsafepageraddress
, nvarchar @failsafenetsendaddress
, int @forwardalways)

MetaData:

 create procedure sys.sp_MSsetalertinfo  
@failsafeoperator nvarchar(255) = null,
@notificationmethod int = null,
@forwardingserver nvarchar(255) = null,
@forwardingseverity int = null,
@pagertotemplate nvarchar(255) = null,
@pagercctemplate nvarchar(255) = null,
@pagersubjecttemplate nvarchar(255) = null,
@pagersendsubjectonly int = null,
@failsafeemailaddress nvarchar(255) = null,
@failsafepageraddress nvarchar(255) = null,
@failsafenetsendaddress nvarchar(255) = null,
@forwardalways int = null -- 0 = forward only unhandled events, 1 = always forward events (both subject to @forwardingseverity)
as

-- only sysadmins are allowed to execute this stored procedure
if( is_srvrolemember(N'sysadmin') = 0 )
begin
RAISERROR (15003, -1, -1, N'sysadmin')
return 1
end

-- Set all alert info at one go, for performance reasons. Translate values if needed. --
if (@pagersendsubjectonly is not null and @pagersendsubjectonly <> 0)
select @pagersendsubjectonly = 1

if (@failsafeoperator is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeOperator', N'REG_SZ', @failsafeoperator
if (@notificationmethod is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertNotificationMethod', N'REG_DWORD', @notificationmethod
if (@forwardingserver is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingServer', N'REG_SZ', @forwardingserver
if (@forwardingseverity is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardingSeverity', N'REG_DWORD', @forwardingseverity
if (@pagertotemplate is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerToTemplate', N'REG_SZ', @pagertotemplate
if (@pagercctemplate is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerCCTemplate', N'REG_SZ', @pagercctemplate
if (@pagersubjecttemplate is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSubjectTemplate', N'REG_SZ', @pagersubjecttemplate
if (@pagersendsubjectonly is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertPagerSendSubjectOnly', N'REG_DWORD', @pagersendsubjectonly
if (@failsafeemailaddress is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeEmailAddress', N'REG_SZ', @failsafeemailaddress
if (@failsafepageraddress is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafePagerAddress', N'REG_SZ', @failsafepageraddress
if (@failsafenetsendaddress is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeNetSendAddress', N'REG_SZ', @failsafenetsendaddress
if (@forwardalways is not null)
exec sys.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertForwardAlways', N'REG_DWORD', @forwardalways

No comments:

Post a Comment

Total Pageviews