| |
 |
|
SQL Server Tips by Burleson |
Applications of XP_SMTPSENDMAIL
One interesting application of XP_SMTPSENDMAIL is to get email
addresses from a table and send an email to all of them. The sample
databases Northwind and Pubs have several tables with contact
information for employees, customers, etc. but, surprisingly, none
has an email field. It really does not matter because we need real
email addresses to send the emails, in case this code is used to
test the XP, and verifying that they reached their destination. For
testing purposes we will create a table named EmployeeInfo, with
only one field named email:
CREATE TABLE EmployeeInfo(email
varchar(50))
The table will get three records (these email addresses should be
replaced with valid ones, even one email will do for the test):
INSERT EmployeeInfo(email)
VALUES('Adam@xpbook.org')
INSERT EmployeeInfo(email) VALUES('Briana@xpbook.org')
INSERT EmployeeInfo(email) VALUES('Carla@xpbook.org')
Finally, a cursor will read the email address field from the table
and feed it into XP_SMTPSENDMAIL:
DECLARE @email VARCHAR(50)
DECLARE cur_email CURSOR FOR
SELECT email FROM EmployeeInfo
OPEN cur_email
FETCH NEXT FROM cur_email INTO @email
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC master..XP_SMTPSENDMAIL @From='boss@company.com',
@To=@email, @Subject='Friday meeting',
@Body='Meeting has been cancelled.'
FETCH NEXT FROM cur_email INTO @email
END
CLOSE cur_email
DEALLOCATE cur_email
This example, for minimalism, assumes that the sender, body and
subject will be the same for all the emails. They could be taken
from the table itself or from other source.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
