Solved

populating @recipients value using msdb.dbo.sp_send_dbmail from a database table

Posted on 2008-10-09
20
2,312 Views
Last Modified: 2010-04-21
Hello

I'm using the stored procedure msdb.dbo.sp_send_dbmail in sql server 2005 to send out emails.

I need to be able to send emails to email addresses stored in a table called emailadd. The msdb.dbo.sp_send_dbmail stored procedure uses the @recipients parameter to guage who the email is sent to, for example:

@recipients = 'me@me.com; myfriend@friend.com,

rather than hard coding these values i want the values to be derived from the emailadd table so that if the email addresses change in the table, the email will go to the right people.

Any ideas?

Thanks
0
Comment
Question by:pedley123
  • 8
  • 4
  • 3
  • +3
20 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 22678803
are you sending one email for each record in the emailadd table, or the same email to all of them?
0
 

Author Comment

by:pedley123
ID: 22679625
at the moment this would be the same email to all email addresses in the emailadd table
0
 

Author Comment

by:pedley123
ID: 22690937
increased to 500, can any one assist?
0
 
LVL 10

Assisted Solution

by:AaronAbend
AaronAbend earned 100 total points
ID: 22751015
create procedure sendmails as
begin
declare getemails cursor for
select emailaddress from mytable
declare @onerecipient varchar(40) -- make variables large enough for your needs
declare @allrecipients varchar(2000)

open getemails;
fetch next from getemails into @onerecipient;

while (@@fetch_status=0)
begin
        set @allrecipients = @allrecipients+@onerecipient+';' --- i do not think a trailing semicolon will be a problem - if it is you need to eliminate with logic
        fetch next from getemails into @onerecipient;
end
close getemails;
deallocate getemails;

-- execute the send email command here with @allrecipients...
end
   
-- this is one approach (sorry - it is past midnight in my timezone and i do not have time to test right now)
-- another would be to execute the emailing process within the cursor loop.
-- if you create  an exception block make certain that the cursor is closed and deallocated regardless of the logic path
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22751201
G'Day,

Enjoy these types of problems, not sure why I haven't seen it before actually...

Can use the e-mail addresses from the table, and can either string them together, or, send individual e-mails. But, identifying which email goes to what email address might be your challenge. Or, are you saying that all emails via this procedure should always go to the recipients found in the email address table...

If you could give some thought as to how to identify an "appropriate" recipient, and / or, how you envisage seeing that happen - I did see your comment that every email would go to everyone in the table, but that is also a small deviation from your original question...

basically the approach will be to either string together the recipients as a single receipient, or do a "loop" to send individually. Kind of depends on how many recipients there could be... @recipients is varchar(max) so that is about 2 gig of addresses :)

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22751208
Sorry aronabend - did not see your post - again forgot a quick refresh... The cursor is a good idea - should declare with READ_ONLY and FAST_FORWARD...

And using that as an example, aronabend is building up the address so it is one email to lots of recipients - could also do the individual sp_send_dbmail for each recipient (rather than build a string, do the actual send_dmail) if you want to personalise a bit more...
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22751368
There is a really cool way of stringing together columns without looping through - if you do not need to personalise each e-mail.

Below, I create a little test table, populate it and then use the "trick" - check it out...




-- step 1 create a test table
 

create table test_email_recipients (id int identity, email_address varchar(100))
 

-- step 2 populate
 

insert test_email_recipients (email_address) values ('email_1')

insert test_email_recipients (email_address) values ('email_2')

insert test_email_recipients (email_address) values ('email_3')
 

-- step 3 - declare a variable - thout not really needed, could populate @recipient directly
 

declare @recipient varchar(max)
 

-- step 4 string together all the email addresses in the table
 

set @recipient = (select email_address + ';' from test_email_recipients for xml path(''))
 

-- step 5 print it out just to make sure
 

print @recipient
 

-- in sp_send_dbmail you can use directly...
 

exec sp_send_dbmail @recipients = (select email_address + ';' from test_email_recipients for xml path('')),

                    @subject = 'Test bulk e-mail',
 

etc...

Open in new window

0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 22751628
Kudos/points to mark_wills - avoids the cursor. It really was past midnight - I must have been really tired!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22751803
Question is not over yet, and I do prefer "personalising" e-mails - cannot do that with a string, so have to loop around, and may as well be a cursor...  
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 150 total points
ID: 22751994
And here is THE COOLEST way to concatenate column values whithout looping through and without using any fancy tricks like XML and stuff. This should be a classic by now.

Thanks Mark for your testing code:
-- step 1 create a test table

create table test_email_recipients (id int identity, email_address varchar(100))

 

-- step 2 populate

insert test_email_recipients (email_address) values ('email_1')

insert test_email_recipients (email_address) values ('email_2')

insert test_email_recipients (email_address) values ('email_3')
 
 

-- step 3 - declare a variable - thout not really needed, could populate @recipient directly

declare @recipient varchar(max)
 
 

-- step 4 string together all the email addresses in the table

select @recipient=isnull(@recipient,'')+email_address+';' from test_email_recipients
 

-- get rid of the last ; might not be necessary

select @recipient=left(@recipient, len(@recipient)-1)

 

-- step 5 print it out just to make sure

select @recipient as Recipients

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22752599
Yep, that is indeed pretty cool... But, we can probably agree to debate if it is still the coolest (classic yes).

and I have also stated something incorrectly : you cannot use mine directly in the "exec msdb.dbo.sp_send_dbmail"  without using some dynamic sql, so just as easy to set up the variable first.

and the last semicolon does not matter.
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 22753412
Zberteoc  - I did not know you could do that with mssql.  I will take that knowledge as my reward. Thanks.
 
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22754388
OK, OK, then this is why I think the for XML is probably the coolest - it is more adaptable/flexible because it implicitly becomes a string... and do not know why people are so put off by the "xml" keyword...

Try this using the "classic" approach:

create table #test_email_recipients (id int identity, email_address varchar(100), company varchar(100))
 

insert #test_email_recipients (email_address,company) values ('email_1','EE')

insert #test_email_recipients (email_address,company) values ('email_2','EE')

insert #test_email_recipients (email_address,company) values ('email_3','BB')
 

select distinct company,(select email_address + ',' from #test_email_recipients e where c.company = e.company for xml path('')) as co_emails

from #test_email_recipients c
 

select 'The following emails can be dynamically strung together : '+(select email_address + ',' from #test_email_recipients for xml path(''))
 

drop table #test_email_recipients

Open in new window

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 22754798
And just so I might also get a nice little "thats cool" instead of "it was late" from AaronAbend, or even "I learnt something new", what is really cool is being able to loop through each distinct company and provide a single email to all the recipients that belong to that company - all in ONE "select" statement and ONE "exec", no cursors, no loops... while it does use some elements of "classic", don't think you can do that without using the XML version - is it cool yet ?
create table #test_email_recipients (id int identity, email_address varchar(100), company varchar(100))

 

insert #test_email_recipients (email_address,company) values ('email_1','EE')

insert #test_email_recipients (email_address,company) values ('email_2','EE')

insert #test_email_recipients (email_address,company) values ('email_3','BB')
 

-- if you want to acually run it, need to change your profile_name accordingly... ie change MY_MAIL_PROFILE to whatever is your real profile, and change the above e-mail addresses to something real, and remove the comments from below...
 

declare @sql varchar(max)

select @sql = isnull(@sql,'') + 'exec msdb.dbo.sp_send_dbmail @profile_name=''MY_MAIL_PROFILE'',@recipients='''+

              (select email_address + ';' from #test_email_recipients e where c.company = e.company for xml path('')) + 

              ''',@subject=''Email Listings for '+company+''',@body = ''Dear Staff member of '+company+

              ','+replicate(char(13)+char(10),2)+'You Belong to Company '+company+replicate(char(13)+char(10),2)+

              'Cheers,'+char(13)+char(10)+'Mark Wills'';'+char(13)+char(10)

              from #test_email_recipients c group by company

print @sql
 

--exec (@sql)  -- currerntly less than 500 characters
 

drop table #test_email_recipients

Open in new window

0
 

Author Closing Comment

by:pedley123
ID: 31504663
thanks to all for providing the solutions to this
0
 
LVL 10

Expert Comment

by:AaronAbend
ID: 22791535
I always appreciate points - and I think I gave a good example of how to use a cursor (which might help someone in a situation that is more complex than the stated problem) but nobody looking at this thread and conclude that my solution was a good solution to this problem. Cursors are quite slow in TSQL and not a good idea when other approaches are possible.  
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22792097
@aaronabend: apart from not getting a "gosh mark, I have learned something new" or suitably gooey accolade as was afforded zberteoc (see ID:22753412), I am pleased to be sharing the points with you in this solution - it is a legitimate approach, it works, and ever so humble, you have joined in the conversation and definitely assisted me (at least) to arrive at the "accepted" solution. In fact depending on the response to personalised / individualised e-mails, then cursors might be the easiest way to solve. So, what are you saying ?
0
 

Expert Comment

by:hessfirm01
ID: 24615521
Hi Mark and Aaron,
Wondered if you could help with a similar question.

I'm have been able to send a View that display's multiple records in a nice table based on certain criteria. I use sp_send_dbmail and MSSQL 05

What I can't figure out, is how to send to the email address associated with those records, not a static one, then loop through and send the next result set to the next recipient.

In the code below, i've declared a static @recipients...but i want it to be pulled from the joined table, and i want it to loop to the next record set. So in the joined tables, 1 email will be associated with, let's say, 10 records...etc.

Any ideas? Thanks!

I think this is a cursor thing...not sure. Any ideas?
DECLARE @tableHTML  NVARCHAR(MAX) ;

 

SET @tableHTML =

    N'<H1>Work Order Report</H1>' +

    N'<table border="1">' +

    N'<tr><th>Work Order ID</th><th>Product ID</th>' +

    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +

    N'</tr>' +

    CAST ( ( SELECT td = m.ln,       '',

                    td = m.Lot, '',

                    td = m.Br, '',

                    td = m.Baf, '',

                    td = m.Lp, ''

              FROM mydb.dbo.table as m

              WHERE subd = 'village grove'

              ORDER BY lp ASC

              FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +

    N'</table>' ;

 

 

EXEC msdb.dbo.sp_send_dbmail

	@profile_name = 'local',

	@recipients='myemail@gmail.com',

    @subject = 'Current Homesites',

    @body = @tableHTML,

    @body_format = 'HTML' ;

Open in new window

0
 

Expert Comment

by:hessfirm01
ID: 24615527
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 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

13 Experts available now in Live!

Get 1:1 Help Now