x

Database Mail sending to multiple email addresses in the database SQL 2005

I updated the code. It is syntactically correct when I comment out the EXEC msdb.dbo.sp_send_dbmail. 1. Don't know why. 2. In the Assets table, there are multiple assets for the employee. but may have several asset managers that need to be notified. I was trying to create an inner loop just for the asset manager. May need help with this a little more... Thanks!

/* Email Variables */

DECLARE @mailsubject nvarchar(200) DECLARE @themailbody nvarchar (MAX)

/* Cursor Variables */ DECLARE @EmployeeID varchar (50); DECLARE @FunctionalMgrID varchar (50); DECLARE @Job varchar (6); DECLARE @WBS varchar (5); DECLARE @LastworkDate datetime; DECLARE @EmployeeName varchar (50); DECLARE @FunctionalMgrName varchar (50); DECLARE @FunctionalMgrEmail varchar (255); DECLARE @TalentMgrName varchar (50); DECLARE @TalentMgrEmail varchar (255); DECLARE @Library varchar (50); DECLARE @AssetMgrName varchar (50); DECLARE @AssetMgrEmail varchar (255);

/* Cursor - looping through specified CasualEmployee Database Records */ DECLARE Emails CURSOR FAST_FORWARD READ_ONLY FOR

/*Statement to fill cursor with data */ SELECT [CasualEmployeeDetails].[EmployeeID], [FunctionalMgrID], [Job], [WBS], [LastworkDate], [EmployeeName], [FunctionalMgrName], [FunctionalMgrEmail], [TalentMgrName], [TalentMgrEmail], [Library]
FROM [CDB].[dbo].[CEmpDetails] WHERE [CEmpDetails].[EmailSent] = 0

/* Open the cursor and fill it with the first set of values */ OPEN Emails

FETCH NEXT FROM Emails into @EmployeeID, @FunctionalMgrID, @Job, @WBS, @LastworkDate, @EmployeeName, @FunctionalMgrName, @FunctionalMgrEmail, @TalentMgrName, @TalentMgrEmail, @Library;

/*Prepare and send email */ WHILE @@FETCH_STATUS = 0 BEGIN SET @mailsubject = 'AMS - Notification: Inactive CASUAL employee with Assets Assigned ' +@EmployeeName+ ':';

SET @themailbody = 'Records indicate that the employee shown below: <br/>
                 -- is defined as a CASUAL status employee<br/>
                 -- has not worked for the last 90 calendar days with PARSONS<br/>
                 -- but has assets assigned<br/>
             <br/>Employee ID:  ' +@EmployeeID+
            '<br/>Employee Name:  ' +@EmployeeName+
            '<br/>Last Work Date:  ' +convert(varchar(30), @LastworkDate, 101)+
            '<br/>Last Work Job-WBS:  ' +@Job+ '-' +@WBS+
            '<br/>Talent Manager: please coordinate with the listed Asset Manager and Functional Manager above to appropriately
             <br/>re-assign this employee assets listed:' +
             N'<H1>Casual Employee Assets</H1>' +
             N'<table boarder="1">' +
             N'<tr><th>Asset Tag</th><th>AssetModelName</th></tr>' +
             CAST ( ( SELECT td = em.AssetTag,               '',
                             td = em.AssetModelName,                     ''
                   FROM       CasualEmployeeAssets 
                   WHERE [CEmpAssets].[EmailSent] = 0  and CEmpAssets.EmployeeID = @EmployeeID
                   FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))+ N'</table>' + N'Reference: '+ @Library;


Select distinct top 1 [AssetMgrName]
                    ,[AssetMgrEmail]

      FROM [CDB].[dbo].[CEmpAssets]
      WHERE [CEmpAssets].[EmailSent] = 0  and EmployeeID = @EmployeeID                

---- ---- ----EXEC msdb.dbo.sp_send_dbmail ---- @profile_name = 'CasualAsset', ---- @recipients = @TalentMgrEmail, ---- @copy_recipients = @AssetMgrEmail, ---- @subject = @mailsubject, ---- @body_format = 'HTML', ---- @body = @themailbody;

  UPDATE [CEmpDetails] Set [EmailSent] = 1, EmailSentDate = GetDate() WHERE EmployeeID = @EmployeeID

-- UPDATE [CEmpAssets] Set [EmailSent] = 1, EmailSentDate = GetDate() WHERE EmployeeID = @EmployeeID

  FETCH NEXT FROM Emails INTO @EmployeeID, @FunctionalMgrID, @Job, @WBS, @LastworkDate, @EmployeeName, @FunctionalMgrName,
                             @FunctionalMgrEmail, @TalentMgrName, @TalentMgrEmail, @Library;

  END 


  CLOSE Emails
DEALLOCATE Emails
more ▼

asked Aug 02 '11 at 08:25 AM in Default

technette gravatar image

technette
1.1k 77 91 101

@technette I updated your query in question. The biggest problem (outside of mismatching order of the columns in the select and variables in the FETCH) was that there is no way to set the variables at the time the send mail procedure is called. In other words,

@subject = 'Notification: blah blah + @EmployeeName + :',

is not possible, but

set @the_subject = 'Notification: blah blah + @EmployeeName + :'

and then @subjet = @the_subject

is OK.
Aug 04 '11 at 11:34 AM Oleg
I formatted the code and tweaked the subject, because the quotes were in the wrong place. Also restored a convert of a datetime variable. The code parses just fine.
Aug 04 '11 at 12:23 PM Oleg

Found another problem on this line:

@recpients = @TalentMgrEmail,

Since it is named parameter, its name must match what the msdb.dbo.s\p_send_dbmail procedure expects, so the line has to read

@recipients = @TalentMgrEmail,

The recipients is misspeled in the script, it now reads recpients
Aug 04 '11 at 12:29 PM Oleg
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You could do something similar to my answer to another question from today, namely create a cursor to run through your data and send the emails as necessary:

DECLARE @EmailAddress varchar(255),
    @EmailSubject varchar(255),
    @Emailbody varchar(max)

/* This is a cursor (a looping construct in SQL Server)*/
DECLARE Emails CURSOR FAST_FORWARD READ_ONLY
FOR
/* You define a select statement to fill the cursor with data, you change this to fit your schema*/
SELECT  EmailAddress,
        EmailSubject,
        Emailbody
FROM    MyEmailView

/* You then open the cursor and fill it with the first set of values*/
OPEN Emails

FETCH NEXT FROM Emails INTO @EmailAddress ,@EmailSubject , @Emailbody

/* If there is data, prepare it and send an email using the system sproc sp_send_dbmail (asynchronous email processor inside SQL Server)*/
WHILE @@FETCH_STATUS = 0 
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail 
            @recipients = @EmailAddress,
            @subject = @EmailSubject,
            @body = @EmailBody
        /* Continue the loop */    
        FETCH NEXT FROM Emails INTO @EmailAddress ,@EmailSubject , @Emailbody
    END
/* Cleanup */
CLOSE EmailReminder
DEALLOCATE EmailReminder
more ▼

answered Aug 02 '11 at 08:33 AM

WilliamD gravatar image

WilliamD
25.8k 17 19 41

Thank you for your input William! Really appreciate it. Can I declare a cursor at an HTML table as well?

I will be sending a different result set to the various users as well.
Aug 02 '11 at 05:20 PM technette

I have worked on the following but don't have the correct syntax where I am trying to CC FunctionalManagerEmail and AssetManagerEmail.

Also get an error on the final line of this procedure near AssetModelName.

One more question about this is I have a table that has multiple assets for one employee. Some assets may have a different asset manager. How do I make sure that FunctionalManager, TalentManager and AssetManager each only get one email for the set of employee assets?
Aug 03 '11 at 03:15 PM technette

@technette From the query in your question, it looks like the order of the variables do not match the order of columns in the select statement used by the cursor, so the variables are all populated incorrectly. Additionally, for some reason you use the column names when you populate the value of the email body parameter, but you should use the variables instead. For example, the line (just like any other line there)

'Employee ID: '+ [CEmpDetails].[EmployeeID],

is wrong, it should read

'Employee ID: '+ @EmployeeID

The commas are not used as intended and also the while loop does not end, you have begin but no end.

Try to fix all these errors before getting into the actual problem with sending only one email for the set of employee assets.
Aug 03 '11 at 04:15 PM Oleg
Thanks Oleg! Will take care of these items.
Aug 03 '11 at 04:47 PM technette

Still doesn't like the syntax, even when I replace the column names with variables. When I add the + sign or not I still get errors.

@subject = 'AMS - Notification: Inactive CASUAL employee with Assets Assigned: '@EmployeeName,
Aug 03 '11 at 06:12 PM technette
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1933
x672
x57

asked: Aug 02 '11 at 08:25 AM

Seen: 2890 times

Last Updated: Aug 05 '11 at 03:09 PM