CODEWITHSUNDEEP
sqlsql-server
hhhh
hhhh

Reputation: 11

Send Email From Database

Im trying to send an email from a sever to a couple of recipients.

I do query my database using:

SELECT Email from payrollchecksystem

And than use the following to send the email 

USE msdb
GO
EXEC sp_send_dbmail @profile_name='SQL Server Alerts System',
@recipients = I DONT KNOW WHAT TO PUT HERE
@subject='Test message',
@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

My problem is that I dont know what to put for @recipients when trying to send all the emails I got from the payrollchecksystem table?

Upvotes: 1

Views: 3103

Answers (2)

bitxwise
bitxwise

Reputation: 3604

To separate your e-mails into a semi-colon delimited string, use COALESCE:

DECLARE @EmailList varchar(MAX)

SELECT  @EmailList = COALESCE(@EmailList + ';', '') + Email
FROM    payrollchecksystem
WHERE   Email IS NOT NULL

SELECT  @EmailList

Then you can use @EmailList like so:

USE msdb
GO
EXEC sp_send_dbmail @profile_name='SQL Server Alerts System',
     @recipients = @EmailList,
     @subject='Test message',
     @body='This is the body of the test message. Congrates Database Mail Received By you Successfully.'

Upvotes: 5

Dan Grossman
Dan Grossman

Reputation: 52372

http://msdn.microsoft.com/en-us/library/ms190307.aspx

@recipients Is a semicolon-delimited list of e-mail addresses to send the message to

Upvotes: 2

Related Questions