Solved

How to send email of query results in tsql

Posted on 2008-10-24
4
1,499 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:66chawger
4 Comments
 
LVL 19

Accepted Solution

by:
folderol earned 400 total points
Comment Utility
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
 
LVL 3

Expert Comment

by:raminhos
Comment Utility
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
 
LVL 13

Assisted Solution

by:sm394
sm394 earned 100 total points
Comment Utility
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
 

Author Comment

by:66chawger
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now