[dba-SQLServer] Database Mail format Body of email

I created a stored procedure that formats an email to a select set of
people daily.

In the body of the email is an HTML table that lists project Milestones
that are due, either in the near future or are delinquent.

Now they want to send an email notification to the same group when a
Milestone has changed or if the basic project data has changed. 

This can be easily done by sending out two emails (one for Milestones
due and another if it changes) to each person.

 

Is it possible to create two or three tables in the body of an email
using separate views?

I have yet to succeed in doing this.

The following code works - but the email is blank!

 

I am very inexperienced with this sort of thing, any help would be
greatly appreciated.

Thanks,

 

Jim

 

Declare

@tableHTML  NVARCHAR(MAX),

@MaxRows int,

@RowCnt int,

@PMname varchar(100),

@PMEmail varchar(100);

 

SET @RowCnt = 1

 

CREATE TABLE #whileTable

            (RowNum int IDENTITY(1,1) Primary Key,

            E_Name varchar(50),

            E_email varchar(50))

 

Insert into #whileTable

(E_Name, E_email)

Select E_Name, E_email 

>From NCI_Milestones.dbo.vwEmailPM

 

Select @MaxRows = Count(*) from #whileTable

 

WHILE EXISTS (Select * from #whileTable as vw

                                                            where (
RowNum <= @MaxRows 

                                                            and RowNum =
@RowCnt))

 

BEGIN

 

SET @PMName = (select E_Name from #whileTable where RowNum = @RowCnt)

SET @PMEmail = (select E_Email from #whileTable where RowNum = @RowCnt)

 

SET @tableHTML =

    N'<H4>Milestones Due</H4>' + 

    N'<H5>This email is to notify you that you have a Milestone due
shortly or due now on your project.  Please work with your A/R Analyst
to generate the necessary invoice.  Once completed, please update the
Milestone Database (located at
https://one.nciinc.com/BusinessOperations/PM_Central/MilestoneDatabase/d
efault.aspx) to check off the "Amount Received" field.  However, if the
milestone due date has slipped, please update the Milestone Database
with the number of days slipped as well as an explanation for the slip.
You will continue to receive these notices until one of these 2 actions
has been taken.</H5>' +

    N'<table border="1">' +

    N'<tr><th>Project Name</th><th>ID#</th><th>Milestone</th>' +

    N'<th>Days Over Due</th>' +

    N'<th>Due Date</th></tr>' +

    CAST ( ( SELECT td = vw.ProjectName,'',

                                                            td =
MilestoneID,'',

                    td = Milestone,'', 

                    td = OverDue,'',

                    td = DueDate,''

              FROM vwProjectsDuePM as vw 

                                     WHERE PM_Name = @PMname for XML
PATH('tr'), TYPE) 

            as NVARCHAR(Max))+ N'</table>' +

    N'<H4>Modified Projects</H4>' + 

                        N'<H5>A change was made to the project data for
the following items.</H5>' +

                        N'<table border="1">' +

                        N'<tr><th>Project Name</th><th>Project
Number</th>' +

                        N'<th>GM Name</th>' +

                        N'<th>Business Manager</th></tr>' +

                        CAST ( ( SELECT td = vw1.ProjectName,' ',

 
td = ProjectNumber,' ',

 
td = GM_Name,' ', 

 
td = BM_Name,' '

                                                  FROM
vwProjectsModified as vw1 

                                                 WHERE PM_Name = @PMName
for XML PATH('tr'), TYPE) 

                        as NVARCHAR(Max))+ N'</table>' +

    N'<H4>Modified Milestones</H4>' + 

                        N'<H5>A change was made to the milestone data
for the following items.</H5>' +

                        N'<table border="1">' +

                        N'<tr><th>Project Name</th>' +

                        N'<th>Project Number</th>' +

                        N'<th>Milestone</th>' +

                        N'<th>PM Name</th>' +

                        N'<th>Business Manager</th></tr>' +

                        CAST ( ( SELECT td = vw2.ProjectName,' ',

 
td = ProjectNumber,' ',

 
td = Milestone,' ' ,

 
td = PM_Name,' ', 

 
td = BM_Name,' '

                                                  FROM
vwMilestonesModified as vw2 

                                                 WHERE PM_Name = @PMName
for XML PATH('tr'), TYPE) 

                        as NVARCHAR(Max))+ N'</table>' ;

 

EXEC msdb.dbo.sp_send_dbmail 

            @profile_name = 'Milestone Email',

            @recipients = 'jhewson at nciinc.com,

-- at PMEmail,

    @subject = 'Milestone Database Notification',

    @body =  @tableHTML,

    @body_format = 'HTML';

 

Select @RowCnt = @RowCnt +1

 

END

 

DROP TABLE #whileTable

 

 


################################################################################
If you have received this message in error, please contact the sender
immediately and be aware that the use, copying, or dissemination of 
this information is prohibited. This email transmission contains 
information from NCI Information Systems, Inc. that may be considered 
privileged or confidential and is intended solely for the named 
recipient.
################################################################################