Karthik Viswanathan's Blog

Microsoft - Oracle Technologies

Automated Email Batch Jobs in SQL Server 2005

In SQL Server 2005, we can easily set up, configure and create a batch job which will send emai to bulk of users. You can prefer this option when you are having data stored across the tables within a database. Let's see how we can acheive this in a easiest way:

Prerequisites:

A. SQL Server 2005 Enterprise Edition
B. SMTP server name and credentials
C. Administrator Login to access Database Email, Job options.

Steps to Achieve:

1. Create a Stored Procedure for sending Emails
2. Create a Email Account
3. Configure SQL Server Database Email
4. Build a Batch Job to send Email

#1 - CREATE SP

First, write your logic to build the Email Subject, Content and getting list of users whom you want to send email.
Next we are going to Use the command msdb.dbo.sp_send_dbmail effectively to accomplish sending the mail.

sp_send_dbmail
[ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]' ]
[ , [ @subject = ] 'subject' ]
[ , [ @body = ] 'body' ]
[ , [ @body_format = ] 'body_format' ]
[ , [ @importance = ] 'importance' ]
[ , [ @sensitivity = ] 'sensitivity' ]
[ , [ @file_attachments = ] 'attachment [ ; ...n ]' ]
[ , [ @query = ] 'query' ]
[ , [ @execute_query_database = ] 'execute_query_database' ]
[ , [ @attach_query_result_as_file = ] attach_query_result_as_file ]
[ , [ @query_attachment_filename = ] query_attachment_filename ]
[ , [ @query_result_header = ] query_result_header ]
[ , [ @query_result_width = ] query_result_width ]
[ , [ @query_result_separator = ] 'query_result_separator' ]
[ , [ @exclude_query_output = ] exclude_query_output ]
[ , [ @append_query_error = ] append_query_error ]
[ , [ @query_no_truncate = ] query_no_truncate ]
[ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

(Refer MSDN for more details)

Here is the sample Procedure which send email to bulk of users:

This procedure will send email by bcc the list of users. You can change displaying the users in To (or) Cc list using options @recipients, @copy_recipients. Also you can format the mail using HTML tags when we mentioned the body_format as 'HTML'. Here the @profile_name will be the name of the database mail profile which we are going to create in #3. Sending the mail using this command is very straight forward once you go through the syntax description.

CREATE PROCEDURE [dbo].[P_SENDMAILTOUSERS] AS
DECLARE @User varchar(MAX),
@Content varchar(MAX),
@Subject varchar(100)
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
SELECT @User='user1@hotmail.com,user2@hotmail.com,user3@yahoo.com'
SELECT @Content = 'Mail Content Goes Here...'
SELECT @Subject = 'Test Mail from Blog!!!'
BEGIN
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Name of Your Mail Profile', @body_format = 'HTML', @blind_copy_recipients = @User, @body = @Content, @subject = @Subject
PRINT @Content
PRINT @User
END
END
END

GO

#2 and #3 - CREATE AND CONFIGURE DB MAIL ACCOUNT

  • Login to SQL Server Management Studio
  • Go to Management->Database Email->Configure Database Email
  • Choose Set up Database Email option
  • Provide Profile Name and Description. E.g., MY_MAIL_PROFILE
  • Click the Add button available under SMTP Accounts section
  • Click New Account (a)Provide unique Account Name, Description, SMTP Email Address and Display Name (Which will be appeared while email is sent to user) (b) Specify SMTP servername and Port number (if different), finally Click Ok.
  • Click Finish to complete the wizard.
#4 - BUILD A BATCH JOB TO SEND EMAIL


  • Login to SQL Server Management Studio
  • Go to SQL Server Agent -> Jobs -> New Job In the General Settings tab, Specify Job Name(MyBatchJob), Owner Login and Description.
  • Check the Enabled Option
  • Go to Steps tab, Click New and Provide Step Name as "Execute Procedure"
  • Choose Type T-SQL and Select the Your Database where "P_SENDMAILTOUSERS" Procedure resides.
  • Give Command as below: begin execute P_SENDMAILTOUSERS end
  • Click Advanced tab, Choose On success action as "Quit the job reporting success"
  • In the T-SQL section, provide Output file path and name which will be the log file for batch job, also make Sure to Select the below settings "Append output to existing file"
  • Create (or) Pick schedule for the same accordingly.
  • Finally Click Ok.

Hope this helps you :) Please feel free to contact me if you have any questions...


If you know any other options to accomplish this, plzzz throw here!

Note: If you are getting text from users through UI and want to send them through email then better write that code piece as .Net Batch.

0 comments:

Post a Comment

Followers