SQL Server – Script Alerts

Background

As part of documentation that I am doing, I have the need to script all the alerts defined on a SQL Server Instance.

What is the problem?

One is able to do it for individual alerts, but not for the entire alert set.

Alerts Drop-down

Here is the drop-down on the Alerts option.

AlertsDropdownCustomized

 

Individual Alert Drop-down

individualAlert

 

Quick Explanation:

  1. We are able to script individual alerts
  2. But, not the entire alert set

 

 

Code

Here is a script for scripting all alerts

 



use master
go

if object_id('dbo.sp_ScriptAlerts') is null
begin

	exec('create procedure dbo.sp_ScriptAlerts as select 1/0 as [shell] ')

end
go

alter procedure dbo.sp_ScriptAlerts
(
	  @asATransactionBlock bit = 0
	, @listAlerts bit = 0
)
as

	set nocount on;

	declare @tblAlert TABLE
	(

		  [identity]				int not null identity(1,1)
		, [id]						int not null primary key
		, [alert]					sysname not null
		, [messageID]				int null
		, [severity]				int null
		, [enabled]					tinyint not null
		, [jobID]					uniqueIdentifier not null
		, [delayBetweenResponses]	int not null
		, [includeEventDescription] tinyint
		, [categoryID]				int not null
		, [categoryName]			sysname null
	
	)

	declare
		  @identity					int
		, @identityBase				int
		, @identityMax				int
		, @id						int
		, @alert					sysname
		, @messageID				int
		, @severity					int 
		, @enabled					tinyint
		, @jobID					uniqueIdentifier 
		, @delayBetweenResponses	int 
		, @includeEventDescription  tinyint
		, @categoryID				int 
		, @categoryName				sysname

	declare @scriptLine nvarchar(4000)
	declare @SCRIPT_BASE nvarchar(300)
	
	declare @CHAR_TAB varchar(10)
	declare @CHAR_TAB_2 varchar(10)
	declare @CHAR_TAB_3 varchar(10)

	declare @CHAR_SINGLE_QUOTE char(1)
	declare @CHAR_CRLF char(1)
	declare @CHAR_BEGIN varchar(30)
	declare @CHAR_END   varchar(30)

	set @CHAR_TAB = char(9)
	set @CHAR_TAB_2 = replicate(char(9), 2)
	set @CHAR_TAB_3 = replicate(char(9), 3)

	set @SCRIPT_BASE = 'EXEC msdb.dbo.sp_add_alert '

	set @CHAR_SINGLE_QUOTE = ''''
	set @CHAR_CRLF = char(13) + char(10)
	set @CHAR_BEGIN = 'BEGIN '
	set @CHAR_END = 'END '

	insert into @tblAlert
	(
		  [id]						
		, [alert]					
		, [messageID]				
		, [severity]				
		, [enabled]					
		, [jobID]					
		, [delayBetweenResponses]	
		, [includeEventDescription]
		, [categoryID]	
		, [categoryName]
	)
	select 
			  tblSA.[id]
			, tblSA.[name]
			, tblSA.message_id
			, tblSA.severity
			, tblSA.[enabled]
			, tblSA.job_id
			, tblSA.delay_between_responses
			, tblSA.[include_event_description]
			, tblSA.category_id
			, [categoryName] = tblSC.name

	from   msdb.dbo.sysalerts tblSA

				left outer join msdb.[dbo].[syscategories] tblSC

					on tblSA.category_id = tblSC.[category_id]
			
	order  by tblSA.name

	select
			  @identityBase = min([identity])
			, @identityMax = max([identity])
	from  @tblAlert

	set @identity = @identityBase

	if (@asATransactionBlock =1)
	begin

		print 'begin tran'
		print @CHAR_CRLF

	end

	while (@identity <= @identityMax )
	begin

		select 
			  @id = [id]						
			, @alert = [alert]					
			, @messageID = [messageID]				
			, @severity = [severity]				
			, @enabled = [enabled]					
			, @jobID = [jobID]					
			, @delayBetweenResponses = [delayBetweenResponses]	
			, @includeEventDescription = [includeEventDescription]
			, @categoryID = [categoryID]
			, @categoryName = [categoryName]
		
		from  @tblAlert tblA
		where  tblA.[identity] = @identity



		set @scriptLine = ''
		set @scriptLine = @scriptLine + @CHAR_TAB + ' if not exists ( select 1 from [msdb].[dbo].[sysalerts] where name = '
									  +  @CHAR_SINGLE_QUOTE + @alert + @CHAR_SINGLE_QUOTE
									  +  ') '
									  + @CHAR_CRLF
		set @scriptLine = @scriptLine + @CHAR_TAB_2 + @CHAR_BEGIN  + @CHAR_CRLF
		set @scriptLine = @scriptLine + @CHAR_TAB_3 + @SCRIPT_BASE  + @CHAR_CRLF

		set @scriptLine = @scriptLine + @CHAR_TAB_3 + '  @name =' + @CHAR_SINGLE_QUOTE + @alert + @CHAR_SINGLE_QUOTE + @CHAR_CRLF
		set @scriptLine = @scriptLine + @CHAR_TAB_3 + ', @message_id =' + cast(@messageID as varchar(30)) + @CHAR_CRLF
		set @scriptLine = @scriptLine + @CHAR_TAB_3 + ', @severity =' + cast(@severity as varchar(30)) + @CHAR_CRLF
		set @scriptLine = @scriptLine + @CHAR_TAB_3 + ', @enabled =' + cast(@enabled as varchar(30)) + @CHAR_CRLF
		set @scriptLine = @scriptLine + @CHAR_TAB_3 + ', @delay_between_responses =' + cast(@delayBetweenResponses as varchar(30)) + @CHAR_CRLF
		set @scriptLine = @scriptLine + @CHAR_TAB_3 + ', @include_event_description_in =' + cast(@includeEventDescription as varchar(30)) + @CHAR_CRLF

		if (@categoryName is not null)
		begin

			set @scriptLine = @scriptLine + @CHAR_TAB_3 + ', @category_name =N' 
										  + @CHAR_SINGLE_QUOTE + @categoryName  + @CHAR_SINGLE_QUOTE + @CHAR_CRLF
		
		end

		set @scriptLine = @scriptLine + @CHAR_TAB_3 + ', @job_id =N' 
							+ @CHAR_SINGLE_QUOTE + cast(@jobID as varchar(60)) + @CHAR_SINGLE_QUOTE	+ @CHAR_CRLF


		set @scriptLine = @scriptLine + @CHAR_TAB_2 + @CHAR_END  + @CHAR_CRLF

		print @scriptLine

		set @identity = @identity + 1

	end


	if (@listAlerts = 1)
	begin

		exec msdb.dbo.sp_help_alert

	end
	if (@asATransactionBlock =1)
	begin

		print 'rollback tran'

	end


go


EXEC sys.sp_MS_marksystemobject '[dbo].[sp_ScriptAlerts]'
go


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s