Send email when record date = GetDate()

Transact-SQL
,
1

I have a trigger in place that sends an email on insert. I am wanting to create a second email when a date field equals today. From what I can tell the only way to accomplish this would be a sql job? Creating the job looks pretty easy through management studio, but I am by no means an expert and was wondering if someone could help me get the code correct. Below is the exact code I am using for the trigger. I am also including a quick snippet of code for what needs to be searched for in the records to send emails on (one email per record found);

Select * from dbo.Tasks where DueDate=GetDate() AND CompletedDate IS NULL

USE [Paramount2]
GO
/****** Object: Trigger [dbo].[t_NewTaskEmail] Script Date: 11/10/2015 5:28:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date:
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[t_NewTaskEmail]
ON [dbo].[Tasks]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
DECLARE
	@AssignedToWorkEmail varchar(100),
	@CreatedByAgent varchar(101),
	@EmailSubject varchar(450),
	@EmailBody varchar(max)
Select
	@AssignedToWorkEmail = AssignedTo.WorkEmail,
	@CreatedByAgent = i.Priority,
	@EmailSubject =isnull(CreatedBy.Agent,'N/A')+' assigned you a new task!',
	@EmailBody = '

Task Information



<td>Type:</td>
<td><b>'+i.TaskType+'</b></td>


<td>Description:</td>
<td>'+i.TaskDescription+'</td>


<td>Priority:</td>
<td>
    '+i.Priority+'
</td>


<td>Assigned:</td><td>'+cast(i.AssignedDate as varchar(20))+'</td>


<td>Due:</td><td>'+isnull(cast(i.DueDate as varchar(20)),'N/A')+'</td>


ProMeLand Tasks

DO NOT REPLY TO THIS EMAIL. THIS IS AN AUTOMATED MESSAGE FROM AN UNMONITORED MAILBOX.

'

From INSERTED i
left join dbo.Agents AssignedTo on i.AssignedTo =AssignedTo.AgentID
left join dbo.Agents CreatedBy on i.CreatedBy=CreatedBy.AgentID

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProMeLand System',
@recipients = @AssignedToWorkEmail,
@subject = @EmailSubject,
@body = @EmailBody,
@body_format = 'HTML'
END

2

since getdate() produces a millisecond resolution, its unlikely your where clause will ever match

3

Ok, that's what I mean, I know what I want to accomplish and the general idea, but I don't know T-SQL or SQL Jobs well enough to create this. I need someone who can help take my email code and convert it for use with SQL Jobs when my criteria are met.

4

First, the trigger needs adjusted to handle multiple rows being INSERTed at one time; that code is below.

And then we need to adjust the WHERE condition, which is easy enough to do, as below. The expression Dateadd(Day, Datediff(Day, 0, GetDate()), 0) looks complex, but it's just a "best-practice" way to very efficiently strip the time off the current date (or any other datetime value).

Select *
from dbo.Tasks
where DueDate >= Dateadd(Day, Datediff(Day, 0, GetDate()), 0) AND
DueDate < Dateadd(Day, Datediff(Day, 0, GetDate()) + 1, 0) AND
CompletedDate IS NULL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[t_NewTaskEmail] 
ON [dbo].[Tasks] 
AFTER INSERT
AS 
SET NOCOUNT ON;

DECLARE @emails_to_send TABLE (
	AssignedToWorkEmail varchar(100),
	CreatedByAgent varchar(101),
	EmailSubject varchar(450),
	EmailBody varchar(8000)
	)

INSERT INTO @emails_to_send ( AssignedToWorkEmail, CreatedByAgent, EmailSubject, EmailBody )
Select
	AssignedTo.WorkEmail,
	i.Priority,
	isnull(CreatedBy.Agent,'N/A')+' assigned you a new task!',
	'
Task Information




<td>Type:</td>
<td><b>'+i.TaskType+'</b></td>


<td>Description:</td>
<td>'+i.TaskDescription+'</td>


<td>Priority:</td>
<td>
    '+i.Priority+'
</td>


<td>Assigned:</td><td>'+cast(i.AssignedDate as varchar(20))+'</td>


<td>Due:</td><td>'+isnull(cast(i.DueDate as varchar(20)),'N/A')+'</td>




ProMeLand Tasks




DO NOT REPLY TO THIS EMAIL. THIS IS AN AUTOMATED MESSAGE FROM AN UNMONITORED MAILBOX.

'

From INSERTED i
left join dbo.Agents AssignedTo on i.AssignedTo =AssignedTo.AgentID
left join dbo.Agents CreatedBy on i.CreatedBy=CreatedBy.AgentID

DECLARE cursor_emails CURSOR LOCAL FAST_FORWARD FOR
SELECT AssignedToWorkEmail, CreatedByAgent, EmailSubject, EmailBody
FROM @emails_to_send

DECLARE
	@AssignedToWorkEmail varchar(100),
	@CreatedByAgent varchar(101),
	@EmailSubject varchar(450),
	@EmailBody varchar(8000)
	
OPEN cursor_emails

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM cursor_emails INTO @AssignedToWorkEmail, @CreatedByAgent, @EmailSubject, @EmailBody
    IF @@FETCH_STATUS <> 0
        BREAK
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'ProMeLand System',
    @recipients = @AssignedToWorkEmail,
    @subject = @EmailSubject,
    @body = @EmailBody,
    @body_format = 'HTML'    
END --WHILE

DEALLOCATE cursor_emails
GO --end of trigger
5

I'm really confused by this. This would be a trigger that creates a temporary table to store a duplicate of the data upon insertion and sends emails to all those in the temporary table? I'm not sure how this can be used, especially without the initial select statement integrated.

I believe the only way to accomplish what I'm looking for would be to use a SQL Job that runs every morning. So the code I am needing would be everything that goes into the job step list, first retrieving all the records that meet the criteria then sending an email for each record found.

6

I did use the initial SELECT statement, to populate the table variable. You can't use a single value in a variable because multiple rows can be INSERTed at one time, in which case the trigger gets all the rows at once in the inserted table.

7

I'm still confused, I don't think I can use a trigger to accomplish this. This trigger is set to run anytime a record is inserted and then would look for records that meet the criteria. I don't think this approach makes much sense especially as if there are 20 tasks due today, there will be 20 emails sent out for every single record that is inserted today. To be clear;

I am wanting to create a SQL Job (not a trigger) that runs once per day to find tasks that are due today and send an email reminder. The only reason I posted my trigger code is because I am using SQL triggers to send emails whenever a new task is created. I need to convert my trigger over to a SQL Job.

8

I'm confused too. Wouldn't the query I also posted at the same time:
Select *
from dbo.Tasks
where DueDate >= Dateadd(Day, Datediff(Day, 0, GetDate()), 0) AND
DueDate < Dateadd(Day, Datediff(Day, 0, GetDate()) + 1, 0) AND
CompletedDate IS NULL
form the base for the job step you need?
With logical similar to the proc to create the actual emails?

9

Ok, let's break this down;

I'm creating a SQL Job and entered the schedule that I want. On the job steps I assume I only create a single step with the type T-SQL. In the command text if I enter what you posted;

Select *
from dbo.Tasks
where DueDate >= Dateadd(Day, Datediff(Day, 0, GetDate()), 0) AND
DueDate < Dateadd(Day, Datediff(Day, 0, GetDate()) + 1, 0) AND
CompletedDate IS NULL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[t_NewTaskEmail]
ON [dbo].[Tasks]
AFTER INSERT
AS
SET NOCOUNT ON;

DECLARE @emails_to_send TABLE (
AssignedToWorkEmail varchar(100),
CreatedByAgent varchar(101),
EmailSubject varchar(450),
EmailBody varchar(8000)
)

INSERT INTO @emails_to_send ( AssignedToWorkEmail, CreatedByAgent, EmailSubject, EmailBody )
Select
AssignedTo.WorkEmail,
i.Priority,
isnull(CreatedBy.Agent,'N/A')+' assigned you a new task!',
'
Task Information

Type: '+i.TaskType+' Description: '+i.TaskDescription+' Priority: '+i.Priority+' Assigned:'+cast(i.AssignedDate as varchar(20))+' Due:'+isnull(cast(i.DueDate as varchar(20)),'N/A')+'

ProMeLand Tasks

DO NOT REPLY TO THIS EMAIL. THIS IS AN AUTOMATED MESSAGE FROM AN UNMONITORED MAILBOX.

'

From INSERTED i
left join dbo.Agents AssignedTo on i.AssignedTo =AssignedTo.AgentID
left join dbo.Agents CreatedBy on i.CreatedBy=CreatedBy.AgentID

DECLARE cursor_emails CURSOR LOCAL FAST_FORWARD FOR
SELECT AssignedToWorkEmail, CreatedByAgent, EmailSubject, EmailBody
FROM @emails_to_send

DECLARE
@AssignedToWorkEmail varchar(100),
@CreatedByAgent varchar(101),
@EmailSubject varchar(450),
@EmailBody varchar(8000)

OPEN cursor_emails

WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_emails INTO @AssignedToWorkEmail, @CreatedByAgent, @EmailSubject, @EmailBody
IF @@FETCH_STATUS <> 0
BREAK
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ProMeLand System',
@recipients = @AssignedToWorkEmail,
@subject = @EmailSubject,
@body = @EmailBody,
@body_format = 'HTML'
END --WHILE

DEALLOCATE cursor_emails
GO --end of trigger

This code is obviously off as it's still referencing triggers which shouldn't be a part of this at all. The first part of code would create a select statement and show records that meet the criteria. After that if I remove the code specific to the trigger I would be at the creation of a temporary table which I don't see why is even necessary, can't I just read the found records? Then I'm inserting into the temporary table from what was inserted (again this doesn't make sense because this isn't a trigger). Then we're at some code to do with cursors which I am not familiar with in the slightest so I really can't say what this is doing. The loop statement seems to make sense but again why would this not loop through the found records in the actual table and send emails from all those? It looks like this code is creating two separate tables to hold and run through this data.

Again, I don't know T-SQL well enough to create the actual code I need to use. I'm already familiar with the basic concepts and what needs to be accomplished, but I need help creating the T-SQL that does the following;

-Select from Tasks table that meets my criteria
-Loop through found records and send one email for each record found

10

Of course you can just read the rows, but you want to send emails, correct? The trigger uses a table variable to collect those emails then runs over that table with a cursor, calling the mail proc on the way. You can do it without the table var but you need to change the cursor to read from the main query

11

So... can anyone help in creating the code necessary?

12

You'll want to get it working in a query window in SSMS before you add it to a job step.

You can use these as examples of how to do it:


http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158847