Post reply

Help with some send_dbmail code

  • May 12, 2009 at 12:03 pm

    #204287

    I have some code to send an HTML email:

    DECLARE @tableHTML NVARCHAR(MAX) ;

    select Hdr.sopnumbe as OrderNumber, hdr.shiptoname, dtl.itemdesc, dtl.Quantity, dtl.uofm, hdr.reqshipdate

    from cplu..sop10100 as hdr

    inner join cplu..sop10200 as dtl on hdr.sopnumbe = dtl.sopnumbe and hdr.soptype = dtl.soptype

    where hdr.soptype = '2' /* order */ and hdr.docid = 'STDORD'

    if @@RowCount > 0

    Begin

    SET @tableHTML =

    N'

    New Orders

    ' +

    N'

    ' +

    N'

    ' +

    N'

    ' +

    CAST ( ( SELECT td = Hdr.sopnumbe, '',

    td = hdr.shiptoname, '',

    td = dtl.itemdesc, '',

    td = dtl.quantity, '',

    td = dtl.UofM, '',

    td = Cast(Hdr.reqshipDate as Char(12)), ''

    from cplu..sop10100 as hdr

    inner join cplu..sop10200 as dtl on hdr.sopnumbe = dtl.sopnumbe and hdr.soptype = dtl.soptype

    where hdr.soptype = '2' /* order */ and hdr.creatddt >= DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE()))

    order by Hdr.reqshipDate

    FOR XML PATH('tr'), TYPE

    ) AS NVARCHAR(MAX) ) +

    N'

    Order #Ship ToItemQuantityUoMShip on

    ' ;

    EXEC msdb.dbo.sp_send_dbmail @recipients='jmetcalf@computer-plus.com',

    @subject = 'New orders ',

    @body = @tableHTML,

    @body_format = 'HTML' ;

    End

    that executes fine, but the email body is blank, even though the query returns results.

    Executing

    SELECT * FROM sysmail_allitems

    ORDER BY mailitem_id DESC

    shows the email with a blank body. The email does get sent, but no body is in the email message.

    Could someone help me diagnose why?

    Thanks in advance.

  • May 12, 2009 at 12:07 pm

    #993176

    never mind...I'm an idjit.

  • May 12, 2009 at 1:29 pm

    #993241

    So, what was it? You never know when someone else might have the same problem.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 14, 2009 at 8:12 am

    #994556

    the 'where' clause in the two select statements was different.

  • May 14, 2009 at 9:23 am

    #994628

    Good catch. Thanks for letting us know!

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply