Send_db_mail html issue

• Dpac

  SSC Enthusiast

  Points: 102

  More actions

  February 1, 2010 at 9:31 am

  #73985

  Hi,

  This might look like a simple problem. But i am new to html. Below is the script i use to send a

  mail using Send_db_mail and display a table containing values inside the body of the mail.

  Declare @tablehtml varchar(max)

  SET @tableHTML =N'<H5 >Report' + N'</H5>' +

  N'<table border="2" cellspacing="0" cellpadding="4" style="font-size:10px;">' +

  N'<tr><th ALIGN=LEFT>Name </th><th ALIGN=LEFT>Addresst</th>' +

  N'<th ALIGN=LEFT>Link</th>' +CAST ( ( SELECT td = Name,'',td = Address, '',td = '<a href="' + Link+ '">' +Link+ '</a>'

  from Student where payment_type='Credit' and Payment_Status=1

  FOR XML PATH('tr'), TYPE

  ) AS NVARCHAR(MAX) ) +

  N'</table>' ;

  set @tableHTML = REPLACE( @tableHTML, '<', '<' );

  set @tableHTML = REPLACE( @tableHTML, '>', '>' );

  set @tableHTML= REPLACE( @tableHTML, '&', '&' );

  EXEC msdb.dbo.sp_send_dbmail @recipients='XXXXX@gmail..com',

  @subject = 'Report',

  @body = @tableHTML,

  @body_format = 'HTML';

  This query worked fine when the Sql statement inside the HTMl returned some value. The problem arises when the query returns null value. I get a message like Mail Queued and i get a blank mail.

  What i want to achieve is when the query inside the html returns no values, the headers and the table layout should be displayed in the mail. Any suggestions??

• Jeffery Williams

  SSCertifiable

  Points: 6441

  More actions

  May 21, 2010 at 8:52 am

  #1170890

  You need to be sure that you don't return a NULL value... Try this

  SELECT td = coalesce(Name, 'No Name Returned'),'',td = coalesce(Address, 'No Address Returned'), '',td = '<a href="' + Link+ '">' +Link+ '</a>'

  from Student where payment_type='Credit' and Payment_Status=1

  The text I added

  'No Name Returned'

  'No Address Returned'

  Can both be changed to anything you want to display including just empty data ie: ''

  The coalesce function returns the first NON null value. SO if for example Name is NULL then the value I set in parenthesis will be returned.

  <hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh