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.
66chawgerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

folderolCommented:
See these BOL articles.  You should be using sp_send_dbmail, which is the SQL2005 mail solution.
xp_sendmail is for older versions.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/f1d7a795-a3fd-4043-ac4b-c781e76dab47.htm
http://msdn.microsoft.com/en-us/library/ms175887.aspx


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
raminhosCommented:
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

0
sm394Commented:
few more good article about setting up and sending database mail in sql server 2005
http://www.codeproject.com/KB/database/SQLServer2008DatabaseMail.aspx
http://www.mssqltips.com/tip.asp?tip=1438
0
66chawgerAuthor Commented:
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

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.