asked on

How to build and send multiple tables using sp_send_dbmail

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

Aneesh

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';

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jsvb1977

ASKER

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

tigin44

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

jsvb1977

ASKER

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

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tigin44

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], ''), '' ),

tigin44

or if you want the NULL text to be seen then

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

jsvb1977

ASKER

nice. i will try these in the morrow. cheers.

Jason

dbaSQL

aneeshattingal & tigin44, this is great! thank you very much.