dbWatch AS

Main procedure SQL Server

We will use the “Test alert” as example code, as its only purpose is to generate alarms and warnings, but uses many underlying functions. We go through it in steps, and include the full routine at the end.
For historical reasons some code will refer to terms like “task” or “check”. Task is jobs without status, and check is jobs with status.
As checks are an expansion of a task, both tasks and checks are found in the dbw_tasks table, but only checks are in the dbw_checks table. The dbw_checks table only keeps additional data not stored in dbw_tasks.

create  procedure dbw_test_alert @taskID INT

The main procedure gets the taskid as input when it gets executed. This is used to lookup in the underlying framework to get hold of parameters and updating status.

as
begin
set nocount on
declare @status int

It is typical to declare an int value for the return status. If this job wants to update status, this argument needs to be part of the call of the dbw_updateCheckValues procedure. 0 is OK, 1 is Warning and 2 is Alarm.

declare @now_date datetime   
declare @exec_info VARCHAR(1000)

At the end of this procedure we call “dbw_updateCheckValues taskID, status, @exec_info”. This routine writes data in the underlying framework. This data is then again displayed in the software, and used for alerts.
We declare the exec_info variable. The data in this variable will end up as the details field on the job in the monitoring module.

We continue with general code for this procedure..

declare @last_run_alarm datetime
declare @between_alarm int
declare @send_alarm int
declare @alarm_message varchar(1000)
declare @alarm_disable int
declare @last_run_warning datetime
declare @between_warning int
declare @send_warning int
declare @warning_message varchar(1000)
declare @warning_disable int
declare @ok_message varchar(1000)

These are just other declarations for the function of this job. 

BEGIN TRY

This is to run the procedure with exception handling

set @status = 0
set @now_date = getdate() 
set @send_alarm = 0
set @alarm_disable = 0
set @send_warning = 0
set @warning_disable = 0

These is some variables used in the function of this job.

select @between_warning = convert(int, VALUE) FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Time between warnings') and TASK_ID=@taskID
select @between_alarm = convert(int, VALUE) FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Time between alarms') and TASK_ID=@taskID
select @alarm_message = VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Alarm message') and TASK_ID=@taskID
select @warning_message= VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Warning message') and TASK_ID=@taskID
select @ok_message = VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Ok message') and TASK_ID=@taskID
select @warning_disable = convert(int,CASE VALUE WHEN 'NO' THEN '0' WHEN 'YES' THEN '1' ELSE '0' END) 
FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Disable warnings') and TASK_ID=@taskID
select @alarm_disable =convert(int, CASE VALUE WHEN 'NO' THEN '0' WHEN 'YES' THEN '1' ELSE '0' END)  
FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Disable alarms') and TASK_ID=@taskID

This is how we get data from parameters. The configurable parameters in the job, are stored in the framework in the “DBW_PARAMETERS” table. We retrieve values from this by using the parameter name and the task_id we got at execution time. “DBW_TASKS” table provides the mapping between task_id and the name of the job. 

select @last_run_alarm = max(histr_date) from dbw_test_alert_history where type_message like @alarm_message
select @last_run_warning = max(histr_date) from dbw_test_alert_history where type_message like @warning_message
if @last_run_alarm is null set @last_run_alarm = getdate() - 9999
if @last_run_warning is null set @last_run_warning = getdate() - 9999
			if @now_date > @last_run_warning + @between_warning/24./60. 
			begin 
			if @send_alarm = 0 
			begin
				if @warning_disable = 0 
				begin
		     set @send_warning = 1
				end
			end
	 end
			if @now_date > @last_run_alarm + @between_alarm/24./60.
	 begin 
			if @send_warning = 0 
			begin 
				if @alarm_disable = 0 
				begin  
		     set @send_alarm = 1
				end 
			end
	 end

	if @send_warning > 0 
	begin
		insert into dbw_test_alert_history (histr_date, type_message) values (@now_date, @warning_message)
		set @status = 1
		set @exec_info = @warning_message
	end 
	if @send_alarm > 0 
	begin
		insert into dbw_test_alert_history (histr_date, type_message) values (@now_date, @alarm_message)
		set @status = 2
		set @exec_info = @alarm_message
	end 

	if @send_warning = 0 
	begin 
		if @send_alarm = 0 
		begin 
			set @status = 0 
			set @exec_info = @ok_message
		end 
	end 


			delete from dbw_test_alert_history where histr_date < getdate() -30

This is the main function section of the job.

exec dbw_updateCheckValues  @taskID, @status, @exec_info

This call is essential to update the result of the job. dbw_updateCheckValues takes the task_id(which we get as input when this procedure is executed), an int, here status (which can be 0 for OK, 1 for Warning, and 2 for Alarm), and a string, here exec_info where we add short detailed information of the status of the job. Keep this short, under 1000 characters. 

END TRY
BEGIN CATCH
set @exec_info = 'Exception, ' + ERROR_MESSAGE()
exec dbw_updateCheckValues  @taskID, 0 , @exec_info
END CATCH
end

It is recommended, for stability, to add exception handling to the job, in case something goes wrong. Here we try to add some information about the exception, as part of the details, so it will be visible in the graphical interface.

That is the layout of a main procedure on MS SQL Server. All the jobs provided are open, and its possible to read the code to get inpiration to new jobs.

Complete code


create  procedure dbw_test_alert @taskID INT 
as
begin
set nocount on
declare @status int
declare @now_date datetime   
declare @exec_info VARCHAR(1000)
declare @last_run_alarm datetime
declare @between_alarm int
declare @send_alarm int
declare @alarm_message varchar(1000)
declare @alarm_disable int
declare @last_run_warning datetime
declare @between_warning int
declare @send_warning int
declare @warning_message varchar(1000)
declare @warning_disable int
declare @ok_message varchar(1000)

BEGIN TRY

set @status = 0
set @now_date = getdate() 
set @send_alarm = 0
set @alarm_disable = 0
set @send_warning = 0
set @warning_disable = 0

select @between_warning = convert(int, VALUE) FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Time between warnings') and TASK_ID=@taskID
select @between_alarm = convert(int, VALUE) FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Time between alarms') and TASK_ID=@taskID

select @alarm_message = VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Alarm message') and TASK_ID=@taskID
select @warning_message= VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Warning message') and TASK_ID=@taskID
select @ok_message = VALUE FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Ok message') and TASK_ID=@taskID

select @warning_disable = convert(int,CASE VALUE WHEN 'NO' THEN '0' WHEN 'YES' THEN '1' ELSE '0' END) 
FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Disable warnings') and TASK_ID=@taskID
select @alarm_disable =convert(int, CASE VALUE WHEN 'NO' THEN '0' WHEN 'YES' THEN '1' ELSE '0' END)  
FROM DBW_PARAMETERS WHERE UPPER(NAME)=UPPER('Disable alarms') and TASK_ID=@taskID

select @last_run_alarm = max(histr_date) from dbw_test_alert_history where type_message like @alarm_message
select @last_run_warning = max(histr_date) from dbw_test_alert_history where type_message like @warning_message


if @last_run_alarm is null set @last_run_alarm = getdate() - 9999
if @last_run_warning is null set @last_run_warning = getdate() - 9999

			if @now_date > @last_run_warning + @between_warning/24./60. 
			begin 
			if @send_alarm = 0 
			begin
				if @warning_disable = 0 
				begin
		     set @send_warning = 1
				end
			end
	 end
			if @now_date > @last_run_alarm + @between_alarm/24./60.
	 begin 
			if @send_warning = 0 
			begin 
				if @alarm_disable = 0 
				begin  
		     set @send_alarm = 1
				end 
			end
	 end

	if @send_warning > 0 
	begin
		insert into dbw_test_alert_history (histr_date, type_message) values (@now_date, @warning_message)
		set @status = 1
		set @exec_info = @warning_message
	end 
	if @send_alarm > 0 
	begin
		insert into dbw_test_alert_history (histr_date, type_message) values (@now_date, @alarm_message)
		set @status = 2
		set @exec_info = @alarm_message
	end 

	if @send_warning = 0 
	begin 
		if @send_alarm = 0 
		begin 
			set @status = 0 
			set @exec_info = @ok_message
		end 
	end 


	delete from dbw_test_alert_history where histr_date < getdate() -30

exec dbw_updateCheckValues  @taskID, @status, @exec_info

END TRY
BEGIN CATCH
set @exec_info = 'Exception, ' + ERROR_MESSAGE()
exec dbw_updateCheckValues  @taskID, 0 , @exec_info
END CATCH
end

End of procedure

← Implementation / Post implementation →

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment