Send DBMail

Categories: News, Professional, SSC
Comments: No Comments
Published on: August 15, 2011

With SQL Server 2005, Microsoft improved the methods available for DBAs to send email from SQL Server.  The new method is called Database Mail.  If you want to send emails programmatically, you can now use sp_send_dbmail.  You can read all about that stored procedure here.

What I am really looking to share is more about one of the variables that has been introduced with sp_send_dbmail.  This parameter is @query.  As the online documentation states, you can put a query between single quotes and set the @query parameter equal to that query.  That is very useful.

Why am I bringing this up?  Doing something like this can be very useful for DBAs looking to create cost-effective monitoring solutions that require emailing result sets to themselves.  I ran across one scenario recently where a DBA was looking for help doing this very thing.  In this case, the query was quite simple.  He just wanted to get a list of databases with the size of those databases to be emailed.

Here is a quick and dirty of one method to do such a thing.

[codesyntax lang=”tsql”]


As I said, this is a real quick and dirty example of how to send an email with query results.  The results of the query in the @query parameter (in this case) will be in the body of the email.  A slightly modified version of that first solution is as follows.

[codesyntax lang=”tsql”]


This is only really slightly modified because I took the guts of sp_databases and dumped that into this query.  The modification being that the remark column was removed.  Why do this?  Well, to demonstrate two different methods to get the same data from the @query parameter.  We can either pass a stored procedure to the parameter, or we can build an entire SQL statement and pass that to the parameter.

This is just a simple little tool that can be used by DBAs.  Enjoy!

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *

August 2011
« Jul   Sep »


  • @SQLSoldier: @pshore73 @AngryPets About any 3rd party VSS backup tool: handles backups great. Opinion deviates if you ever need to do restores. #sqlhelp
  • @AngryPets: @pshore73 #sqlhelp Also, the backups veeam created were... crap. (I got called in after 24+ hours of down time and gobs of lost data.)
  • @AngryPets: @pshore73 #sqlhelp Arguably, that was operator error - but any TOOL that would do VSS backups and trim log like that is a FAIL in my book.
  • @AngryPets: @pshore73 #sqlhelp NOT a fan. Had someone call me after they lost 12+ hours of data because veeam did: BACKUP LOG dbName TO DISK = 'NUL'
  • @pshore73: #SQLHelp Any thoughts on Veeam as a tool for production backup & restore of SQL, including performance impacts
  • @paschott: SSIS "Could not load file or assembly" w/ SurveyMonkey DLL. Any ideas? Doesn't seem to be GACable so not sure what next. #sqlhelp #ssishelp
  • @wendy_dance: @hus_sid432 Have you looked at the job history? You can also check in the SSISDB execution logs. #sqlhelp
  • @hus_sid432: Agent job set up as ssis package taking > 12 hrs as suppose to 1hr. How can I troubleshoot #sqlhelp
  • @wendy_dance: @willmeier #sqlhelp Sounds like you need to reinit that subscription. Something may have blocked it, or it only partially completed
  • @mexicanrooster: @whoisyarad I read the next book, highly recommended: Querying Microsoft SQL Server 2012. It covers everything you need. #sqlhelp

Welcome , today is Tuesday, December 1, 2015