Including two email bodies in one TSQL

  • Hi All

    I have a code that i use to check SSL certificates that are expiring in 30 days. The data is uploaded to a database table then I execute the job everyday @ 9am if there's a certificate that expires then an email will be sent to relevant ppl

    My issue is i have two certificates that are expiring in a same month & day, How will i make the attached code to include one or more if the expiring certicates? See attached code & an a table picture also the email output.

    Thanks

    It's better to fail while trying, rather than fail without trying!!!

  • Hi,

    See the BOL subject sp_send_dbmail specifically 'sending an HTML e-mail message'. This will show you how to return multiple records and then insert into each record into one html table row.

    Then you could concatenate

    SELECT 'ExpiryDate:' + convert(varchar (30),[Expiry_Date],126) + ' ServerWebsite: + [Server\Website] + etc

    FROM table.

    you would need to incorporate the html coding in the BOL subject.

    This will give you one line per certificate.

    If you want to retain your existing format style you would probably have to duplicate the parameters, @ServerWebsite2, @@ExpiryDate, etc...one set for each certificate, probelamatic if you don't know the number of certificates...or use the BOL syntax, don't concatenate and sort appropriately.

    I think if you convert from your parameter based setup to what is described in BOL you'll be on your way...

Viewing 2 posts - 1 through 1 (of 1 total)

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