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: 5750
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 12 posts - 1 through 11 (of 11 total)

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

Ken McKelvey SSCoach Points: 18868 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: 5750 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 weeks, 4 days ago by Lord Slaagh.

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

Ken McKelvey SSCoach Points: 18868 More actions · Answer 3

¿Qué?

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

This reply was modified 2 weeks, 4 days ago by Ken McKelvey.

Jeffrey Williams SSC Guru Points: 90321 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

Lord Slaagh SSCertifiable Points: 5750 More actions · Answer 5

Hi SSC team,

I wanted to leave the answer here just in case anyone else needs it. This works. Only caveat is that you need to use dts.vaiables instead of what they are using depending on your VS version.

Thank you all for your help!!!

Happy New Year!

https://stackoverflow.com/questions/6439663/how-to-send-the-records-from-a-table-in-an-e-mail-body-using-ssis-package

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

Jeff Moden SSC Guru Points: 1004457 More actions · Answer 6

Lord Slaagh wrote:

Hi SSC team,
I wanted to leave the answer here just in case anyone else needs it. This works. Only caveat is that you need to use dts.vaiables instead of what they are using depending on your VS version.
Thank you all for your help!!!
Happy New Year!
https://stackoverflow.com/questions/6439663/how-to-send-the-records-from-a-table-in-an-e-mail-body-using-ssis-package

Are you happy with doing such a thing through SSIS? You can, instead, build the body of the email as HTML in a string variable (NVARCHAR(MAX)) and then just send the email using that variable as the body.

The reason why I do it that way is because <<insert drum-roll here>>, I have a mostly hate-relationship with SSIS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18380 More actions · Answer 7

.

This reply was modified 1 week, 2 days ago by Jonathan AC Roberts. Reason: deleted, duplicate

Jonathan AC Roberts SSCoach Points: 18380 More actions · Answer 8

Jeff Moden wrote:

Lord Slaagh wrote:
Hi SSC team,
I wanted to leave the answer here just in case anyone else needs it. This works. Only caveat is that you need to use dts.vaiables instead of what they are using depending on your VS version.
Thank you all for your help!!!
Happy New Year!
https://stackoverflow.com/questions/6439663/how-to-send-the-records-from-a-table-in-an-e-mail-body-using-ssis-package
Are you happy with doing such a thing through SSIS? You can, instead, build the body of the email as HTML in a string variable (NVARCHAR(MAX)) and then just send the email using that variable as the body.
The reason why I do it that way is because <<insert drum-roll here>>, I have a mostly hate-relationship with SSIS.

Do you use a cursor to create the body? 😁

Jeff Moden SSC Guru Points: 1004457 More actions · Answer 9

Jonathan AC Roberts wrote:

Jeff Moden wrote:
Lord Slaagh wrote:
Hi SSC team,
I wanted to leave the answer here just in case anyone else needs it. This works. Only caveat is that you need to use dts.vaiables instead of what they are using depending on your VS version.
Thank you all for your help!!!
Happy New Year!
https://stackoverflow.com/questions/6439663/how-to-send-the-records-from-a-table-in-an-e-mail-body-using-ssis-package
Are you happy with doing such a thing through SSIS? You can, instead, build the body of the email as HTML in a string variable (NVARCHAR(MAX)) and then just send the email using that variable as the body.
The reason why I do it that way is because <<insert drum-roll here>>, I have a mostly hate-relationship with SSIS.
Do you use a cursor to create the body? 😁

You already know the answer to that... 😉 Not only "NO" but "HELL NO!!!" 😀

For my "Morning Reports", I have the servers send me an email using the technique explained in the following MS Documentation...

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql#c-send-an-html-e-mail-message

I also do some little tricks with the conditional coloring of cell backgrounds to highlight issues, etc.

I should check if others have written much about the method and, if not, maybe write an article that explains how I do a "Morning Jobs Report".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lord Slaagh SSCertifiable Points: 5750 More actions · Answer 10

Hi Jeff,

I am not a huge fan of SSIS myself, but it pays the bills. I will look into the HTML solution, but that would require another step in this process... I think.

Thank you again. Nice to meet you!

Dave

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

Audionova Old Hand Points: 301 More actions · Answer 11

Jeff Moden wrote:

Jonathan AC Roberts wrote:
Jeff Moden wrote:
Lord Slaagh wrote:
Hi SSC team,
I wanted to leave the answer here just in case anyone else needs it. This works. Only caveat is that you need to use dts.vaiables instead of what they are using depending on your VS version.
Thank you all for your help!!!
Happy New Year!
https://stackoverflow.com/questions/6439663/how-to-send-the-records-from-a-table-in-an-e-mail-body-using-ssis-package
Are you happy with doing such a thing through SSIS? You can, instead, build the body of the email as HTML in a string variable (NVARCHAR(MAX)) and then just send the email using that variable as the body.
The reason why I do it that way is because <<insert drum-roll here>>, I have a mostly hate-relationship with SSIS.
Do you use a cursor to create the body? 😁
You already know the answer to that... 😉 Not only "NO" but "HELL NO!!!" 😀
For my "Morning Reports", I have the servers send me an email using the technique explained in the following MS Documentation...
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql#c-send-an-html-e-mail-message
I also do some little tricks with the conditional coloring of cell backgrounds to highlight issues, etc.
I should check if others have written much about the method and, if not, maybe write an article that explains how I do a "Morning Jobs Report".

That sounds like an interesting article!