July 18, 2003 at 10:02 am
I am new at this forum, so please forgive me if this topic has recently been covered.
I want to be able to generate an email message to someone, when an event occurs, during either an insert or update to a table. So, I want to put this into a trigger. I have been reading about how to do this, and asking around. I've been told that I should use the extended stored procedure xp_sendmail. I tried doing that with something like this in the SQL Query Analyzer:
xp_sendmail @recipients='myself', @message='This is a test'
However, when I do this, I get an error that the parameter @user was not specified. So then I added it, something like this:
xp_sendmail @recipients='myself', @user='myself', @message='This is a test'
However, when I executed this command, I got an error saying that @user was an invalid parameter?! One error message tells me to use @user and the other one tells me not to.
My first question is: how DO I generate an email in a trigger in SQL Server 2000?
Secondly, why am I getting conflicting error messages?
(We use MS Exchange Server 2000 as our email server.)
Doctor Who
July 18, 2003 at 10:18 am
I suspect that the user executing the trigger may not have rights to the xp_sendmail procedure.
Keep in mind that generating things from triggers can result in lots of emails, which can be really annoying. I prefer to have a job running that checks the table for changes and then sends an email with a number of items at once. We ran into this with regitrations for classes and needing to get approvals. Having 50 people register and 50 emails appear in someone's box wasn't too cool. People got annoyed.
So we set a job to run every hour and bulk send one email with the data in it.
Steve Jones
July 18, 2003 at 11:42 am
Steve,
I am ignorant enough of SQL Server to know know how to schedule a job to do what you're suggesting. How do I go about doing that?
quote:
I suspect that the user executing the trigger may not have rights to the xp_sendmail procedure.Keep in mind that generating things from triggers can result in lots of emails, which can be really annoying. I prefer to have a job running that checks the table for changes and then sends an email with a number of items at once. We ran into this with regitrations for classes and needing to get approvals. Having 50 people register and 50 emails appear in someone's box wasn't too cool. People got annoyed.
So we set a job to run every hour and bulk send one email with the data in it.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
Doctor Who
July 19, 2003 at 12:38 pm
steve is just asking you to create a sp to check for all the changes, generate and send one single email with all those changes. to create a job, go to EM - Management - SQL Server Agent - Jobs and create a new job and schedule it.
July 21, 2003 at 5:31 am
Been there, done this...leads to too many users requesting the same thing - then you get a comm bottleneck and complex jobs...
We had a need for this and ended up writing a service (similar to weatherbug) that lives in the tray...
try not to use sendmail in an sproc though.
good luck
July 21, 2003 at 9:03 am
Steve,
I checked out what you suggested, by going to the server, getting into Query Analyzer under the sa account and trying to execute the xp_sendmail command from there. I still got the same error message.
I must be doing something else wrong, or leaving something out. Any ideas?
quote:
I suspect that the user executing the trigger may not have rights to the xp_sendmail procedure....
Doctor Who
July 21, 2003 at 6:07 pm
I am using xp_sendmail to send emails to user in a trigger, and it works fine so far. Our SQL Server is 7. I don't have @user specified. I only use @recepient=a variable of email address,@subject=a variable, @message=another variable. In the trigger, you assign values to those variables.
Also,make sure your SQM Mail is running.
Good luck.
July 21, 2003 at 6:15 pm
Another two remiders. Please make sure you run xp_sendmail from master. You can call the procedure from a trigger in another table by
exec master.dbo.xp_sendmail ...
Also you need to set @no_output parameter to true if calling this procedure within a trigger. Otherwise you need to handle the result returned by it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply