Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Apr 7, 2005

Formatted emails from SQL Server - Page 2

By Muthusamy Anantha Kumar aka The MAK

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


[Fig 1.4]


[Fig 1.5]

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


[Fig 1.6]


[Fig 1.7]

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>')


[Fig 1.8]


[Fig 1.9]

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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date