Send DBMail

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

1

2

3

4

5

EXEC msdb.dbo.sp_send_dbmail

@profile_name='SQLDBA',

@recipients='myEmail@myDomain.com',

@subject='List of Databases'

,@query='Exec sp_databases'

[/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”]

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

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)) &gt;= 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'

[/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.

SQL Server is full of good stuff. There are plenty of features to be used. Plenty of applications to help it. And there is even plenty of metadata within SQL Server to help you better understand your data and the queries that are being run. It just so happens that a couple of clients requested[…]

Today we have another installment in what is known as TSQL Tuesday. This month we have an invitation and topic given to us by the infamous Kenneth Fisher ( blog | twitter). Today, the invitation is for us to share our stories on how we like to manage security. Or at least that is the[…]

Recently I wrote an article about Capturing Online Index Operations. In that article, I discussed a problem that I had encountered. Well, there were multiple problems. One was an issue with a vendor app that had some hidden module that was performing online index defrags that was causing corruption in a couple of indexes every[…]

One of the things that DBAs love to do is keep their servers running and healthy. A healthy server, after all, is your ticket to a stress free day and a full night’s sleep. Granted this not a guarantee but it sure helps make life easier. We are always looking for the big ticket items[…]

What a fun week we have tuned up for the folks in Las Vegas. It is the first full week of January and there is this huge convention going on near the strip. And as timing would have it, this week is also the perfect time to have our User Group meeting. What major conference[…]

Tis the season for TSQL Tuesday. Not only is it that season again, but it is also the Holiday season. During this season, many people start to think about all of the things for which they are thankful. Many may start to think about their families and friends. And many others will focus more of[…]

This past weekend I had the opportunity to go visit Washington DC. It was just the second time I got to stay in the Nation’s capitol for more than just a few hours. The previous opportunity came with last years event which I talked about here. Sadly, my time was far too limited this trip and[…]

I am about to set sail on a new venture with my next official whistle stop. This year has been plenty full of whistle stops and I plan on continuing. You can read (in full) about previous whistle stops and why they are called whistle stops here. Suffice it to say at this point that it all[…]