July 16, 2004 at 1:26 pm
Hey all,
First time poster here.
I'm writing a trigger using the xp_sendmail XSP to send emails to users based on events. Things seem to be going Ok so far, but I can't seem to get multiple email recipients to get the email notification. I don't want to explicitly list the email addresses, so I'm attempting to pass values pulled from a query. Here is some of the code I'm using. Because the @email_recipients query returns more than 1 result, it bombs. Is there a way for me to do this? Any help appreciated.
set @email_recipients = (select b.emailid from workgroup_user a inner join user b
on a.userid = b.userid
where workgroupid = 'Administrators'
and b.emailid <> ' ')
EXEC master.dbo.xp_sendmail @recipients=@email_recipients
July 16, 2004 at 2:01 pm
hey R B
try one of the following:
(1) Add an emailID to the workgroup_user table which is the mailing list e-mail address containing the email addresses of all users in that group. Then, your query would be like this:
SELECT @email_recipients = a.emailid FROM workgroup_user a WHERE a.workgroupid = 'Administrators'
EXEC master.dbo.xp_sendmail @recipients=@email_recipients
(2) the other option is to use a cursor with the query posted as follows:
DECLARE email_recipients CURSOR LOCAL FAST_FORWARD FOR
SELECT
b.emailid
FROM
workgroup_user a INNER JOIN user b ON a.userid = b.userid
WHERE
workgroupid = 'Administrators' AND
b.emailid <> ' '
DECLARE @email_recipient AS VARCHAR(255) -- check if this is the correct datatype for you
OPEN email_recipients
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM email_recipients INTO @email_recipient
IF ( @@FETCH_STATUS != 0 )
BREAK
EXEC master.dbo.xp_sendmail @recipients=@email_recipient
END
CLOSE email_recipients
DEALLOCATE email_recipients
Either way, I would not define such an operation in a database trigger though since i think transactions might get aborted in cases xp_sendmail errors out. Try doing this in a scheduled job.
July 16, 2004 at 2:32 pm
GHopper,
Thanks for the post. I follow what you're saying and actually, I ended up writing a cursor to filter through the SMTPs which seems to work good so far.
I do have a question about your last statement though. I don't see how I can do this in a scheduled job since I actually want to send out these emails upon the update and insert events of documents in our EDMS, which happens randomly throughout the day. Thoughts, etc.?
July 16, 2004 at 10:34 pm
One approach I originally thought about was a straightforward queued approached to sending e-mails:
1) Whenever a document is inserted or updated in the EDMS database, have the corresponding insert/update trigger(s) add the event record information into some queue table, say something like Email_Events table. This table should also have a bit column (i.e. sent_flag) to determine whether an e-mail has already been sent for the event. Whenever an event record is added, the default value for sent_flag column should be 0 (e-mail not sent).
2) Create a stored procedure, something similar like the one I posted earlier, that sends e-mails for each event record in the Email_Events table where sent_flag = 0. The stored procedure also needs to update sent_flag to 1 of the event record after the e-mail is sent.
3) Create a job that calls the stored procedure. Schedule the job to run daily, every X amount of times a day. You would need to determine (i.e. guesstimate) what might be a good value for X.
Another approach is to leverage the notification functionality provided in SQL Server Notification Services. I've never played with it but it does sound like something that might be worth exploring:
The advantages that I see with either approach are:
1) Transactions' stability will not be affected by errors that occur with sending e-mails because it is no longer in the triggers, hence not part of the transactions.
2) Transactions' performance will not be directly degraded by all the e-mails being sent (same reason as 1).
Good luck ...
JP
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply