Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using Xp_sendmail With the Recipients Generated From a Query

By Kunal Das, (first published: 2001/07/16)

Xp_sendmail is a great utility that SQL Server provides in order to e-mail messages directly from SQL Server, by using a valid MAPI profile. However, its syntax is rather stringent and if you type in the email address yourself it works fine. Consider

 
Xp_sendmail ‘kdas@it-enterprises.com’, ‘Hello’

But Say you wanted to extract email addresses from a table I will call “TEST_TABLE”, in a database called Test. and use the results of this query as the address list for xp_sendmail. How would you do it?

Here is a simple solution using T-SQL which you can use as a stored procedure:

 

Use Test 

Declare @MyRecipients nvarchar (255)

Declare @MyMessage nvarchar (255)

Set @MyMessage = ‘Your timesheet is overdue, please send it ASAP.'

Declare MyCursor Cursor For 

Select email_address From Test_table  Where Status = 0

Open MyCursor

Fetch Next From MyCursor Into @MyRecipients 

While @@Fetch_Status = 0

BEGIN

Print @MyRecipients 

Fetch Next From MyCursor Into @MyRecipients

Exec Master.dbo.xp_sendmail @MyRecipients, @MyMessage

End 

Close MyCursor

Deallocate MyCursor

We simply get each result of the query one at a time, call xp_sendmail and send the result out. It is very convenient when you want to send reminders to people whose addresses you have in a database, maybe a sales database or some such table. This little piece of code is very useful. I wrote this code and modified it so that I could send out reminders every Friday to salespersons who had not returned their timesheets. Their addresses were queried from a table, based upon their timesheet status being ‘0’, i.e. their timesheet had not been returned. I added this as a job to be run every Friday at 8am, which saved me from building a VB Application that would do the same thing, however not so elegantly. Fetch gets each record one a time, and then we email the message to the current recipient using xp_sendmail, this whole process is enclosed in a while loop, so each recipient will be cycled through.

You could modify this code so that each address is concatenated and only one email is sent, however you would have to change your select statement to something like this

Select email_Address+’;’ From Test_Table Where Status = 0

This is because Outlook requires multiple addresses to be separated by semicolons, as does the syntax of xp_sendmail. Also, do not worry about the last address having a semicolon behind it as Outlook will ignore it.

Enjoy.

Total article views: 23405 | Views in the last 30 days: 8
 
Related Articles
FORUM

query results by email using xp_sendmail

query results by email using xp_sendmail

ARTICLE

TSQL Challenge 41 - Extract email addresses from text data

This challenge involves extracting email addresses from a table that contains text data downloaded f...

FORUM

xp_sendmail to multiple recipients

I'm trying to send email to a group of people using the logic provided by following article:  http:/...

FORUM

Subscription email rights

Change the email address

FORUM

Sendmail either via SSIS or SQL Agent

SSIS Sendmail

Tags
advanced querying    
monitoring    
sql server 7    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones