Multiple Recipients (sp_send_dbmail )

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple Recipients (sp_send_dbmail )

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-10-14 : 06:06:56

Hi Team,
Can you help me please.
I have a stored procedure that sends out an email which is working fine but the client now wants the email to go to a second person. I thought it would just be a matter of just adding the second person to the @recipients variable but I cannot get it to work.
When I try to save the SP with execute I get a "query complete with errors" message pointing to my @recipients line.
Originally this was the code that works fine:

EXEC @rc = msdb.dbo.sp_send_dbmail
@recipients = @FirstEmail,

This is the code I am trying to use now:

EXEC @rc = msdb.dbo.sp_send_dbmail
@recipients = RTRIM(@FirstEmail) + ';' + RTRIM(@SecondEmail),
...

Any ideas?

Thanks for your help,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 06:12:38

try

@recipients = '''' + RTRIM(@FirstEmail) + ';' + RTRIM(@SecondEmail) + '''',

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-10-14 : 08:37:30

Hi There,
Thanks for the reply.

With your code suggestion, When I try to execute I get this error message:

Msg 102, Level 15, State 1, Procedure sp_CompExpiredWarnEmailTester, Line 58
Incorrect syntax near '+'.

Appreciate the help.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 09:07:49

try with a variable




declare @reclist varchar(1000)

set @reclist = RTRIM(@FirstEmail) + ';' + RTRIM(@SecondEmail) 

exec @rc = msdb.dbo.sp_send_dbmail
@recipients = @reclist ,
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Steve2106
Posting Yak Master

183 Posts

Posted - 2011-10-14 : 11:12:03

Hi There,
Thanks for the reply.

That seems to have worked but it is not sending out 2 emails.
Is msdb.dbo.sp_send_dbmail smart enough to know that the @FirstEmail & @SecondEmail is the same person. For testing reasons I am using my email address for both receipients. Only the email for @FirstEmail is being delivered.

Thanks again for your help.

Best Regards,

Steve

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-14 : 11:16:11

yep. if its same you will get it only once i guess. Try with another email address if possible.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources