dynamic recipients in DB mail - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

dynamic recipients in DB mail

Author

Topic

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-09-25 : 16:50:39

Table importedrecords

Name Item email
Joe 03 joe@company.com
peter 05 peter@company.com
Adam 07 adam@cpmpany.com

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'Administrator', 
@recipients = 'joe@company.com; peter@company.com; adam@company.com', 
@query = 'SELECT Name, Item FROM db.dbo.importedrecords' , 
@subject = 'Imported Records Count', 
@attach_query_result_as_file = 1 ;

I need to hard code the recepients. How can i do a dynamic search of email address?

So if there were only two names (joe / peter), email will just go to just two of them and lets say if a new record is added (John), now the emil should go to four recipients.

Can anyone help me make it dynamic? Thank you!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-26 : 02:15:25

you can just use like:-


DECLARE @EmailList varchar(3000)
SELECT @EmailList = COALESCE(@EmailList,'') + email + ';'
FROM importedrecords

EXEC msdb.dbo.sp_send_dbmail 
@profile_name = 'Administrator', 
@recipients = @EmailList, 
@query = 'SELECT Name, Item FROM db.dbo.importedrecords' , 
@subject = 'Imported Records Count', 
@attach_query_result_as_file = 1 ;

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

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-09-26 : 03:52:11

hi Visakh,

I get the following error :


Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: ?Msg 208, Level 16, State 1, Server MYSERVER, Line 1
Invalid object name 'importedrecords'

Note : select * from importedrecords is a valid query with 'email' as a valid field and it does give me the results when run independently.

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-09-28 : 02:54:41

I'd really appreciate f someone can help. Its probably a syntax error which is causing the issue.

s_anr
Yak Posting Veteran

81 Posts

Posted - 2010-09-29 : 23:17:41

Any suggestions please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 11:53:12

quote:
Originally posted by s_anr

hi Visakh,

I get the following error :

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 478
Query execution failed: ?Msg 208, Level 16, State 1, Server MYSERVER, Line 1
Invalid object name 'importedrecords'

Note : select * from importedrecords is a valid query with 'email' as a valid field and it does give me the results when run independently.

are you running it in same db? else you need to add db.dbo.importedrecords

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources