Formatted emails from SQL Server

Thursday Apr 7th 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

Learn how to use CDOSys to send formatted emails from Query Analyzer and SQL Server Alerts.

Collaboration Data Objects, also called CDO, is designed to simplify writing programs that create or manipulate Internet messages. CDO for Windows 2000 is an integral part of the Windows 2000 and higher series of operating systems. It is easy to send SMTP email from SQL Server using CDOsys. It is an alternate method to SQLMail. This article illustrates how to use CDOSys to send formatted emails from Query Analyzer and SQL Server Alerts.

Pre-requisite

a.  Your email client should be capable of handling and showing HTML messages

b.  Operating System of the SQL Server box should be windows 2000 or later.

c.  SQL Server box can connect to the internet.

d.  Usp_Send_cdosysmail (Refer below)

Note

a.  Please change the email Id used in this article to your email ID.

b.  Please change the SMTP server used to your SMTP server.

Create the stored procedure "usp_send_cdosysmail" as shown below.

use master
go
if exists (select * from dbo.sysobjects 
  where id = object_id(n'[dbo].[usp_send_cdosysmail]') and 
  objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[usp_send_cdosysmail]
go
create procedure usp_send_cdosysmail
@from varchar(500) ,
@to varchar(500) ,
@subject varchar(500),
@body varchar(4000) ,
@smtpserver varchar(25),
@bodytype varchar(10) 
as
declare @imsg int
declare @hr int
declare @source varchar(255)
declare @description varchar(500)
declare @output varchar(1000)
exec @hr = sp_oacreate 'cdo.message', @imsg out
exec @hr = sp_oasetproperty @imsg,
'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'

exec @hr = sp_oasetproperty @imsg, 
  'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', 
  @smtpserver 

exec @hr = sp_oamethod @imsg, 'configuration.fields.update', null
exec @hr = sp_oasetproperty @imsg, 'to', @to
exec @hr = sp_oasetproperty @imsg, 'from', @from
exec @hr = sp_oasetproperty @imsg, 'subject', @subject

-- if you are using html e-mail, use 'htmlbody' instead of 'textbody'.

exec @hr = sp_oasetproperty @imsg, @bodytype, @body
exec @hr = sp_oamethod @imsg, 'send', null

-- sample error handling.
if @hr <>0 
select @hr
begin
exec @hr = sp_oageterrorinfo null, @source out, @description out
if @hr = 0
begin
select @output = ' source: ' + @source
print @output
select @output = ' description: ' + @description
print @output
end
else
begin
print ' sp_oageterrorinfo failed.'
return
end
end
exec @hr = sp_oadestroy @imsg
go
set quoted_identifier off 
go
set ansi_nulls on 
go

Download the stored procedure from here.

Send simple formatted emails from Query Analyzer

Execute the code below, as shown in Fig 1.0, in order get email as shown in Fig 1.1

exec master..usp_send_cdosysmail
@from='mak_999@yahoo.com', 
@to ='mak_999@yahoo.com',
@subject ='Formatted email from MAK',
@body ='<B>Test Email From MAK</B><BR> Please Ignore this <Font Color=red>email.',
@smtpserver ='optonline.net',
@bodytype ='HTMLBody'

Parameters Explained

@From = 'Sender of the Email'

@To= 'Receiver of the Email'

@Subject = 'Subject of this Email'

@Body = 'Body of this Email. The body of the message accepts HTML tags'

@SMTPServer = 'SMTP server'

@BodyType = 'Type of body: It can be HTMLBody or TEXTBody'


[Fig 1.0]


[Fig 1.1]

Send query results as formatted emails from Query Analyzer

Create and execute the code below, as shown in Fig 1.2, in order to receive email as shown in Fig 1.3

declare @body1 varchar(1000)
set @body1='<table border="2" cellspacing="2" cellpadding="2"> 
  <TR><td colspan=2 
  style="color: #A0522D; font-family: Verdana; font-size: 10;" align=left>'
set @body1=@body1+'Tables </TD></TR>'
select @body1=@body1 +'<TR><TD>'+ name +'</TD></TR>' from sysobjects
print @body1

exec master..usp_send_cdosysmail
@from='mak_999@yahoo.com', 
@to ='mak_999@yahoo.com',
@subject ='Formatted email from MAK',
@body =@body1,
@smtpserver ='optonline.net',
@bodytype ='HTMLBody'

Download Query.txt


[Fig 1.2]


[Fig 1.3]

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved