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.
| Author |
Topic |
|
lw1990
Yak Posting Veteran
85 Posts |
 Posted - 2009-11-23 : 16:17:37
|
| Hi,I have a database table, the records in the table are updated periodically (maybe everyday, maybe twice a week), but when the records updated, I need to send an email only to that people listed in the table with his/her contain information. The table has the information of the people and the email address:Name Email Short_Message AA aa@myfirm.com Contact customer BB bb@myfirm.com Client needs help So, when the table is updated: One email will be sent to AA(aa@myfirm.com) with a short message (Contact customer); One email will be sent to BB(bb@myfirm.com) with a short message (Client XYZ needs help)...How can I get this approach? Thanks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2009-11-23 : 18:55:19
|
| Thank you Tara,It looks like I have to use "sp_send_dbmail" to send email. I don't have to worry about the which records I need to send as I'll "Drop the table" to delete all records after email sent out. The problem is that how I can use @recipients = select email from infotable, and loop through the whole table to send all records out.Thanks. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lw1990
Yak Posting Veteran
85 Posts |
Posted - 2009-11-25 : 12:23:28
|
| Thank you Tara for the response.Now the question is how to pass the selected variable to the @recipients. Surely if I use EXEC msdb.dbo.sp_send_dbmail @recipients='aa@mycompany.com',@body=... There is no problem to send out the email.But if I use DECLARE @emailaddress varchar(100) select @emailaddress = select email from infotable ...and pass the email address to Database Mail:EXEC msdb.dbo.sp_send_dbmail @recipients=’@emailaddress’,@body=... I’ll get error message from mail log:The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2009-11-24T12:03:47). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.).)How to get the value fro the table and pass to the @recipients in Database Mail?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
How to send out a record by email automatically