Avatar of Kevin Smith
Kevin SmithFlag for United States of America

asked on 

How do I show blank cells in html formatted email sent from sql database mail?

I have the following stored procedure that sends an html email, but if there's no data in one field it throws the next column into the first empty column.  How do I correct that?
SET @tableHTML =
    N'<h1>Upcoming QA Dates</h1>'
  + N'<table border="1">'
  + N'<tr><th>Job Number</th>'
  + N'<th>FU Date</th>'
  + N'<th>PM Name</th></tr>'
  + CAST ( (  SELECT
                  td = p.JobNumber    , ''
                , td = wo.FUDate2_60   ,  ''
                , td = pm.PMName   ,  ''
                                           
              FROM (projmgmtsrv.dbo.tbl_QA AS wo
              LEFT JOIN projmgmtsrv.dbo.tbl_Jobs AS p
              ON wo.ProjectID = p.ProjectID)
              LEFT JOIN projmgmtsrv.dbo.tbl_PM AS pm ON p.PM = pm.PMID              
             
              FOR XML PATH('tr'), TYPE
           ) AS NVARCHAR(MAX)
         )
  + N'</table>';
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='mssjobbook'
, @recipients='email@here.com'
, @subject = 'Message Subject'
, @body = @tableHTML
, @body_format = 'HTML';
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
David Todd
ASKER CERTIFIED SOLUTION
Avatar of knightEknight
knightEknight
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
... and if that doesn't work, here is something else to try:

  + CAST ( (  SELECT
             td = isnull(p.JobNumber,'&nbsp;')    , ''
           , td = isnull(wo.FUDate2_60,'&nbsp;')   ,  ''
           , td = isnull(pm.PMName,'&nbsp;')   ,  ''

Open in new window

Avatar of Kevin Smith
Kevin Smith
Flag of United States of America image

ASKER

those cause it not to email at all...
Avatar of David Todd
David Todd
Flag of New Zealand image

Hi,

I try to avoid using XML, but isn't the repeated syntax of td = wrong for a select query? Shouldn't those aliases be different?

Just asking ...

Regards
  David
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo