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.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLDBA' ,
    @recipients = 'myEmail@myDomain.com' ,
    @subject = 'List of Databases'
    ,@query = 'Exec sp_databases'

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.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQLDBA' ,
    @recipients = 'myEmail@myDomain.com' ,
    --@body = @messageBody ,
    @subject = 'List of Databases'
    ,@query = '    select
        DATABASE_NAME   = db_name(s_mf.database_id),
        DATABASE_SIZE   = convert(int,
                                    case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
                                    when convert(bigint, sum(s_mf.size)) >= 268435456
                                    then null
                                    else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb
                                    end)
    from
        sys.master_files s_mf
    where
        s_mf.state = 0 and -- ONLINE
        has_dbaccess(db_name(s_mf.database_id)) = 1
    group by s_mf.database_id'

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> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>






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

SQLHelp


Welcome , today is Thursday, April 24, 2014