Tuesday, June 06, 2017

Sending Automatic Emails from SQL server

I am excited to reactivate my blog after 2.5 years.

I have written a stored procedure last week to send automated emails to customers based on certain criteria.

To identify this there is a stored procedure that requires some parameters.

The steps followed for creating the stored procedure are as follows:

  • There is already an existing stored procedure that creates a temp table and inserts data into that temp table.
  • In the new stored procedure define a new temp table.
  • Insert data into this temp table by executing the existing stored procedure
  • Declare new variables to hold the needed for the email.
  • In a while loop assign the data to the above variables for each record and send the email one by one using sp_send_dbmail built in stored procedure.
Here is the skeleton code I came up with for the procedure.  I have configured a HTML email


Declare @temp_table table
ID INT IDENTITY(1, 1) primary key ,
  field1 varchar(60) null,
AccountID VARCHAR(25) null,
AccountName varchar(200) null,
AccountEMail varchar(128) null

-- Insert into the temp table variable -- the list to be emailed by running the stored procedure Proc1

Insert into @temp_table


Exec [Proc1]
@Parameter1 = 'NZIO',
@Parameter2 = 1,

-- Declare new variables

DECLARE @accountemail varchar(60)
DECLARE @accountname varchar(60)
DECLARE @rowcount INT
DECLARE @href1 varchar(256)

--setting count to start from first row
SET @count = 1
SET @rowcount = (SELECT count(*) FROM @temp_table)
--Print @rowcount

--loop to send email from temp table

WHILE(@count < @rowcount)
SET @accountemail = (SELECT AccountEMail FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')
SET @accountname = (SELECT AccountName FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')
SET @href1 = (SELECT CASE When ClientMode = 'UNATTENDED (HANDS-FREE)' then 'http://link1.pdf' else 'http://link.pdf' END FROM @temp_table where Id = @count and AccountEMail LIKE '%_@__%.__%')

--Configure Email body


SET @tableHTML =

Automatic Notification -- Please do not reply

' +
N'Good Morning  ' +
cast(@accountname as nvarchar(max)) +
N' We are contacting you to let you know that blah blah (EDI: ' +
cast(@field1 as nvarchar(max)) +
is not working.  Please have a look at your account+ cast(@accountname as nvarchar(max)) +
N' Please click on the below link to view user guide on starting
' + N' Userguide for Windows ' +
N' Regards BI Support '


-- Send email using Database Mail

--USE msdb
EXEC MSDB.dbo.sp_send_dbmail @profile_name='Testreport',
@copy_recipients = @accountemail,
@recipients = 'iemail@emailadd.com',
@subject='Test message',
@body= @tableHTML,
@body_format = 'HTML' ;

SET @count= @count +1



No comments:

Rerunning a SSRS subscription report

Yesterday I had a situation where  a couple of SSRS reports failed due to a network upgrade. The SSRS report was not able to access the SQ...