asked on

How to send email of query results in tsql

I have some tsql that performs a query to return the lastest DB backup info for a given backup(s). I want to email the results of this query. I have found some xp_send stuff, but wanted to get a whole solution.

ASKER CERTIFIED SOLUTION

folderol

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

raminhos

you can try something like this:

exec master.dbo.xp_sendmail
@recipients = 'myemail@email.com',
@subject = 'my email subject',
@message = 'My email message...',
@query = 'select * from table',
@attach_results = false

Open in new window

SOLUTION

Faizan Sarwar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

66chawger

ASKER

First of all, thanks for the info. Attached is a code snippet (very crude) which performs a query and emails me the results. What I want to do is (1) update the query so I can query for multiple DB's instead of duplicating the query multiple times (2) Format the Query so the results line up with the headings (column names).

EXEC sp_send_dbmail 
@recipients='me@mycompany.com',
@subject = 'DB Backups',
@body ='Please review latest SQL DB Backup files',
@query ='SELECT DISTINCT TOP 20
s1.type,
s1.backup_start_date,
s1.backup_finish_date,
--s1.first_lsn,
--s1.last_lsn,s1.checkpoint_lsn,
--s1.database_backup_lsn,
s2.physical_device_name
from msdb..backupset s1 inner join msdb..backupmediafamily s2
on s1.media_set_id = s2.media_set_id
where s1.database_name ="Database name 1" 
and s1.backup_start_date >= "10/10/2008 06:41:25 AM"
-- You will need to change to the database you are querying
and s1.type in("D","L","I") -- sl.type in ("D","L") means full or Transaction Log backups (if "I", Differential backups) or ("F" for File Group, "G" for File Group Differential)
and s1.backup_start_date >= (select max(backup_start_date) from msdb..backupset where database_name ="Database name 1" and type ="D") 
order by s1.backup_start_date desc'

Open in new window