Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2015
    Posts
    3

    Unanswered: Help creating SQL Job email

    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 don't know T-SQL very well. 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). If anyone can help with converting this code over to what needs to be pasted into the SQL Job step it would be greatly appreciated.

    // Code to find records
    Select * from dbo.Tasks where DueDate=GetDate() AND CompletedDate IS NULL

    // Code from trigger that needs to be converted to loop through found records and send one email for each record found
    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:
    -- Create date:
    -- 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. #2
    Join Date
    Apr 2015
    Posts
    3
    So, no body?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •