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"]

[/codesyntax]

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"]

[/codesyntax]

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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">






Calendar
August 2011
M T W T F S S
« Jul   Sep »
1234567
891011121314
15161718192021
22232425262728
293031  
Content
SQLHelp

SQLHelp

  • @GEEQL: #sqlhelp question from daughter: "why is it 'more optimized' to list 6 tables in a single from statement than it is to do JOINs?"
  • @BrentO: @markmurphynyc This is kinda beyond 140 - best to put the full queries & plans on a Q&A web site. #sqlhelp
  • @markmurphynyc: #SQLHELP But if I filter by S_DATE_ID in the D_DATE dim, it does use partition elimination. This is in 2012.
  • @markmurphynyc: #SQLHELP Queries filtering by calendar date in d_date joined to the fact table by s_date_id aren't using partition elim. Any way to force?
  • @markmurphynyc: #SQLHELP (1/2) Fact table has s_date_id as YYYYMMDD and is partitioned monthly by this key. D_Date has same numeric key, plus calendar date
  • @jlangdon: @SQLSoldier Also, when hyperthreading is turned on each thread is licensed which sucks. Not applicable with SA @YetAnotherSQL #sqlhelp
  • @jlangdon: @SQLSoldier Correct. Question asked was about EE which is what we have too.@YetAnotherSQL #sqlhelp
  • @databasedave: Any additional info on wait types "se repl commit ack" or "resource governor idle"? Cant find anything on the interwebs. #sqlhelp #azure
  • @SQLHammer: #sqlhelp why does my SSMS open and recovery all of my query windows after I close it explicitly? It's like it thought it crashed.
  • @SQLSoldier: @jlangdon @YetAnotherSQL Only if you oversubscribe the CPUs which I recommend never doing, and need to use Ent. Ed. #sqlhelp

Welcome , today is Tuesday, September 16, 2014