Send results of a SQL query in Outlook email body

Question

Send results of a SQL query in Outlook email body

Lord Slaagh

SSCertifiable

Points: 5748
More actions
December 31, 2024 at 2:56 pm

#4507966
Hello SSC!
I hope everyone had a great holiday! Happy New Year to all!
Issue:
I am currently using SQL Agent to send emails when a job succeeds or fails. The process is basically a bunch of SSIS packages. I also have an attachment that is the results of an SQL query, which UNIONS 2 log tables. This is done using an attachment text file. My issue is that management wants the results in the body of the email. As of now, this is a 2-3 step process to open the email and attachment. They just want to open the email and see the query results in the email body. I can't say that I blame them. LOL!
Does anyone know of a better way to handle this? Please see my list below and as always thank you all in advance!
1. I checked "Send Mail Task" in SSIS but I do not see any option to include an SQL query to the body of the email. Am I missing something?
2. SQL Agent only seems to have an attachment option.
3. Is XML an option? Would I be able to create a proc and have SSIS execute it? Full disclosure, I am NOT well versed in XML, but if you point me in the right direction, I can learn how to do this.
4. Maybe SSRS? This seems to be the proper way, but I am not sure if we have a SSRS reporting environment.
Any assistance would be greatly appreciated!
Dave

The are no problems, only solutions. --John Lennon

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

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

Ken McKelvey SSCoach Points: 18861 More actions · Answer 1

https://stackoverflow.com/questions/7086393/create-html-table-with-sql-for-xml/39487565#39487565

https://learn.microsoft.com/en-us/sql/xquery/flwor-statement-and-iteration-xquery?view=sql-server-ver15&redirectedfrom=MSDN

Lord Slaagh SSCertifiable Points: 5748 More actions · Answer 2

Thanks Ken - Thank you very much for your response, but this is just creating an XML table. I need to embed a SQL query into the body of an MS Outlook email.

This reply was modified 2 days, 20 hours ago by Lord Slaagh.

The are no problems, only solutions. --John Lennon

Ken McKelvey SSCoach Points: 18861 More actions · Answer 3

¿Qué?

Just send an html email with the table in the body text.

This reply was modified 2 days, 20 hours ago by Ken McKelvey.

Jeffrey Williams SSC Guru Points: 90309 More actions · Answer 4

Several examples here: https://www.sqlservercentral.com/forums/topic/create-html-email

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs