?
Solved

How to build and send multiple tables using sp_send_dbmail

Posted on 2009-12-29
10
Medium Priority
?
1,480 Views
Last Modified: 2013-02-05
I am attempting to send an email with an html table embedded in it from sql db mail. I am unable to build separate html tables and send them in one email. Any ideas on how to do this?

The error message i am getting is:
Msg 102, Level 15, State 1, Line 66
Incorrect syntax near '+'.

I have tried variations of + including commas, AND, JOIN [anything i can think of]. I have also tried using multiple @body tags. all without success.

My sql is listed below.

Thank you,
Jason
DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML = 
	N'<h2>Modified Employees</h2>' +
	N'<table border="1">' +
	N'<tr>' +
	N'<th>Employee Number</th>' +
	N'<th>Full Name</th>' +
	N'<th>Password ID</th>' +
	N'<th>Job Code</th>' +
	N'<th>Effective Date</th>' +
	N'<th>Department Code</th>' +
	N'<th>Department</th>' +
	N'<th>Description</th>' +
	N'<th>Manager</th>' +
	N'</tr>' +
CAST ((	SELECT	td = [AssociateId], '',
				td = [Name], '',
				td = RIGHT([SSN],4), '',
				td = [JobCode], '',
				td = LEFT([EffDate],12), '',
				td = [DeptCode], '',
				td = [Title], '',
				td = [Description], '',
				td = [Manager]
		FROM [AssociateIndexTest].[dbo].[FSPI_AssociateIndexView]
		WHERE [Modified] = 1
		FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
	N'</table>'

DECLARE @tableHTML2 NVARCHAR(MAX) ;

SET @tableHTML2 = 
	N'<h2>New Employees</h2>' +
	N'<table border="1">' +
	N'<tr>' +
	N'<th>Employee Number</th>' +
	N'<th>Full Name</th>' +
	N'<th>Password ID</th>' +
	N'<th>Job Code</th>' +
	N'<th>Start Date</th>' +
	N'<th>Department Code</th>' +
	N'<th>Department</th>' +
	N'<th>Description</th>' +
	N'<th>Manager</th>' +
	N'</tr>' +
CAST ((	SELECT	td = [AssociateId], '',
				td = [Name], '',
				td = RIGHT([SSN],4), '',
				td = [JobCode], '',
				td = LEFT([EffDate],12), '',
				td = [DeptCode], '',
				td = [Title], '',
				td = [Description], '',
				td = [Manager]
		FROM [AssociateIndexTest].[dbo].[FSPI_AssociateIndexView]
		WHERE [Modified] = 2
		FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
	N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'WS0071_SQL_EMAIL',
     @recipients = 'jasonsm@fsproduce.com',
	 @body = @tableHTML + @tableHTML2,
	 @body_format = 'HTML',
     @subject = 'These associates have been modified';

Open in new window

0
Comment
Question by:jsvb1977
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26141016
DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
      N'

Modified Employees

' +
      N'' +
      N'' +
      N'Employee Number' +
      N'Full Name' +
      N'Password ID' +
      N'Job Code' +
      N'Effective Date' +
      N'Department Code' +
      N'Department' +
      N'Description' +
      N'Manager' +
      N'' +
CAST ((      SELECT      td = [AssociateId], '',
                        td = [Name], '',
                        td = RIGHT([SSN],4), '',
                        td = [JobCode], '',
                        td = LEFT([EffDate],12), '',
                        td = [DeptCode], '',
                        td = [Title], '',
                        td = [Description], '',
                        td = [Manager]
            FROM [AssociateIndexTest].[dbo].[FSPI_AssociateIndexView]
            WHERE [Modified] = 1
            FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
      N''

DECLARE @tableHTML2 NVARCHAR(MAX) ;

SET @tableHTML2 =
      N'

New Employees

' +
      N'' +
      N'' +
      N'Employee Number' +
      N'Full Name' +
      N'Password ID' +
      N'Job Code' +
      N'Start Date' +
      N'Department Code' +
      N'Department' +
      N'Description' +
      N'Manager' +
      N'' +
CAST ((      SELECT      td = [AssociateId], '',
                        td = [Name], '',
                        td = RIGHT([SSN],4), '',
                        td = [JobCode], '',
                        td = LEFT([EffDate],12), '',
                        td = [DeptCode], '',
                        td = [Title], '',
                        td = [Description], '',
                        td = [Manager]
            FROM [AssociateIndexTest].[dbo].[FSPI_AssociateIndexView]
            WHERE [Modified] = 2
            FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
      N'' ;

declare @nBody nvarchar(max)
SET @nBody = @tableHTML + @tableHTML2
EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'WS0071_SQL_EMAIL',
     @recipients = 'jasonsm@fsproduce.com',
     @body =@nBody ,
     @body_format = 'HTML',
     @subject = 'These associates have been modified';

0
 
LVL 26

Accepted Solution

by:
tigin44 earned 1000 total points
ID: 26141028
change the last part as this
SET @tableHTML = @tableHTML + @tableHTML2
EXEC msdb.dbo.sp_send_dbmail
     @profile_name = 'WS0071_SQL_EMAIL',
     @recipients = 'jasonsm@fsproduce.com',
         @body =  @tableHTML,
         @body_format = 'HTML',
     @subject = 'These associates have been modified';

Open in new window

0
 

Author Comment

by:jsvb1977
ID: 26141406
OK Cool, nicely done!

one last thing, though:

I have a column of data which Sometimes contains NULLs. If there is no data [NULL] then the html table which is built with the above scripting becomes out of sync. In other words, every row that does not display data in the [EffDate] Column has all the data in the cells to its right shifted over.

I think what I need to do is add either COALESCE or ISNULL so that there is some data in each cell when the html table is built -- but it's not working. Any thoughts?

Jason
CAST ((	SELECT	td = [AssociateId], '',
				td = [Name], '',
				td = RIGHT([SSN],4), '',
				td = [JobCode], '',
				td = ISNULL(NULL,[EffDate]), '',
				td = [DeptCode], '',
				td = [Title], '',
				td = [Description], '',
				td = [Manager], ''

Open in new window

0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 26

Expert Comment

by:tigin44
ID: 26141422
try this
CAST (( SELECT  td = [AssociateId], '',
                                td = [Name], '',
                                td = RIGHT([SSN],4), '',
                                td = [JobCode], '',
                                td = ISNULL([EffDate], ''), '',
                                td = [DeptCode], '',
                                td = [Title], '',
                                td = [Description], '',
                                td = [Manager], ''

Open in new window

0
 

Author Comment

by:jsvb1977
ID: 26141505
It spit out: 1900-01-01T00:00:00 Where there was once no data. I also tried your format with COALESCE and got the same result.

Is there another method that you know of that will simply display the word "Null" where "Null" Data Exists?
It is a DateTime field, but I would prefer to display NULL instead of the Default Date in SQL Server.

Thanks for your help,
Jason
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 1000 total points
ID: 26141528
> td = ISNULL(NULL,[EffDate]),


td = ISNULL(CONVERT(varchar, [EffDate], 120) , ''  ),
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26141650
its highly probably that the rows has no date data has a null terminated string not null... so the isnull failing to catch that row... try this

td = ISNULL(NULLIF([EffDate], ''), ''  ),
0
 
LVL 26

Expert Comment

by:tigin44
ID: 26141658
or if you want the NULL text to be seen then

td = ISNULL(NULLIF([EffDate], ''), 'NULL'  ),
0
 

Author Comment

by:jsvb1977
ID: 26141916
nice. i will try these in the morrow. cheers.

Jason
0
 
LVL 17

Expert Comment

by:dbaSQL
ID: 38856524
aneeshattingal & tigin44, this is great!  thank you very much.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question