Formatted emails from SQL Server - Page 2

Send formatted emails from SQL Alerts

Step 1

Create a new error message in SQL Server using the code below.

SP_addmessage 50001,10,
  'Warning message: %s',@with_log='True'

Step 2

Create SQL job with the following code. This will create the job and job steps as shown in Fig 1.4 and 1.5. The job uses SQL Agent tokens.

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) 
   FROM msdb.dbo.syscategories 
   WHERE name = N'[Uncategorized (Local)]') 
     < 1 
  EXECUTE msdb.dbo.sp_add_category @name = 
     N'[Uncategorized (Local)]'

  -- Delete the job with the same name 
     (if it exists)
  SELECT @JobID = job_id     
  FROM   msdb.dbo.sysjobs    
  WHERE (name = N'Notify_Alert_50001')       
  IF (@JobID IS NOT NULL)    
  BEGIN  
  -- Check if the job is a multi-server job  
  IF (EXISTS (SELECT  * 
    FROM    msdb.dbo.sysjobservers 
    WHERE   (job_id = @JobID) 
	AND (server_id <> 0))) 
  BEGIN 
    -- There is, so abort the script 
    RAISERROR (N'Unable to import 
      job ''Notify_Alert_50001'' 
      since there is already a multi-server 
      job with this name.', 16, 1) 
    GOTO QuitWithRollback  
  END 
  ELSE 
    -- Delete the [local] job 
    EXECUTE msdb.dbo.sp_delete_job @job_name = 
	  N'Notify_Alert_50001' 
    SELECT @JobID = NULL
  END 

BEGIN 

  -- Add the job
  EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , 
  	@job_name = N'Notify_Alert_50001', 
	@owner_login_name = N'sa', 
	@description = N'No description available.', 
	@category_name = N'[Uncategorized (Local)]', 
	@enabled = 1, 
	@notify_level_email = 0, 
	@notify_level_page = 0, 
	@notify_level_netsend = 0, 
	@notify_level_eventlog = 2, 
	@delete_level= 0
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the job steps
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, 
  	@step_id = 1, 
	@step_name = N'Notify', 
	@command = N'DECLARE @subject nvarchar(4000)
DECLARE @msg varchar(4000)
set @Subject = "SQL Server Alert System: Alert - Error #[A-ERR]  on [A-SVR]"
Set @msg = REPLACE("<Font Color=Red>SQL Server Alert System: Alert - Error #[A-ERR]  on [A-SVR]<BR>
<BR>
<Font Color=Blue><B>Severity:</b><Font Color=Black> [A-SEV]<BR>
<Font Color=Blue><B>Date:</b><Font Color=Black> [STRTDT] <BR>
<Font Color=Blue><B>Time:</b> <Font Color=Black>[STRTTM]<BR>
<Font Color=Blue><B>Database:</b><Font Color=Black> [A-DBN]<BR>
<Font Color=Blue><B>Message:</b><Font Color=Black> [A-MSG] <BR>", "''''", "") --''''

EXEC usp_send_cdosysmail "mak_99@yahoo.com",
"mak_999@yahoo.com", 
@Subject, 
@msg,
@smtpserver="OPTONLINE.NET",
@Bodytype="HTMLBody"', 
	@database_name = N'master', 
	@server = N'', 
	@database_user_name = N'', 
	@subsystem = N'TSQL', 
	@cmdexec_success_code = 0, 
	@flags = 0, 
	@retry_attempts = 0, 
	@retry_interval = 1, 
	@output_file_name = N'', 
	@on_success_step_id = 0, 
	@on_success_action = 1, 
	@on_fail_step_id = 0, 
	@on_fail_action = 2
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 
  EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

  -- Add the Target Servers
  EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' 
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback 

END
COMMIT TRANSACTION          
GOTO   EndSave              
QuitWithRollback:
  IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION 
EndSave: 

Download Notify_Alert_50001.txt

Step 3

Create the alert using the code below. This will create the alert shown in figures 1.6 and 1.7

IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Alert 50001'))
 ---- Delete the alert with the same name.
  EXECUTE msdb.dbo.sp_delete_alert @name = N'Alert 50001' 
BEGIN 
EXECUTE msdb.dbo.sp_add_alert @name = N'Alert 50001', 
	@message_id = 50001, 
	@severity = 0, 
	@enabled = 1,  
	@delay_between_responses = 60, 
	@include_event_description_in = 5, 
	@job_name = N'Notify_Alert_50001',  
	@category_name = N'[Uncategorized]'
END

Download Alert.txt

Step 4

Trigger the alert using the code below [Refer Fig 1.8] in order to receive email as shown in Fig 1.9

raiserror (50001,16,1,' <BR>This is a test message<BR>')

Conclusion

This article illustrated how to use CDOSys and send formatted HTML emails from Query Analyzer and SQL Server Alerts.

» See All Articles by Columnist MAK