Trigger email on insert, update, delete
-
I am using the following to test for what will ultimatley become a trigger to send mail:
EXEC master..xp_startmail
EXEC master..xp_sendmail
@recipients = 'email@address.com',
@query = 'SELECT * FROM _tblStaff',
@subject = 'mailing list updates',
@message = 'mailing list updates:',
@attach_results = 'TRUE',
@width = 250
EXEC master..xp_stopmail
In QA and other iterations in other interfaces all produce a similar error:
xp_startmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.
xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.
I have set up an email client on the server, tried this logged in as me, with outlook open, etc and get the same error.
Thanks in advace for recommendations.
Jeff
-
You'll want to look at using the parameters available for xp_startmail to select the relevant profile you have created.
BOL -
xp_startmail [[@user =] 'mapi_profile_name']
[,[@password =] 'mapi_profile_password']
But I would warn strongly against sending an email from a trigger. When a trigger runs, it is running inside the same transaction as the event which fired it e.g. the insert statement. If xp_sendmail returns an error you could end up with the transaction rolling back and not only the email not being sent but the item not being inserted. Additionally, transactions should be kept as short as possible on shared tables and sending emails takes a long time compared to regular SQL statements.
Consider inside your trigger inserting a row into another table which acts as a queue. You could have columns, recipient, subject, body, etc etc in your queue table. Insert a row to their and finish the trigger then have a job run through the queue and process the emails one by one.
Dave Hilditch
-
Thanks alot for the temp table suggestion I will definatley do that.
As far as the startmail parameters that was a omission on my part. The code I'm using has my mapi profile info and I still get:
xp_startmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.
xp_sendmail: Either there is no default mail client or the current mail client cannot fulfill the messaging request. Please run Microsoft Outlook and set it as the default mail client.
For whatever reason the sp can't find a default mail client.
-
Can you actually send an email using Outlook on the box? Make sure you actually can send an email, if you can, then leave Outlook running and see if that helps to try and narrow down the problem.
Can't help you much further with the xp_sendmail because I've always used CDONTS to send emails from within VB by reading an email queue table when writing an application like this. Read in a lot of places that the SQL sendmail isn't the best. There's also the problem that Outlook has bugs and most companies don't want it installed on their live boxes.
Maybe someone else has ideas on why it's not working, but one time I did it before it didn't work until I'd actually properly set up the mail client and sent an email from it.
Dave Hilditch.
-
In a desperate effort to debug a trigger, I have sent an email from the triggered event.
I created my own sp to build the email using CDONTS, then called that sendmail sp from the trigger. Not ideal, but at least I confirmed that the trigger was actually firing - I got the message, so to speak.
Here is the sp to send the email:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
--start stored procedure code
Create PROCEDURE spMail
@from VARCHAR(8000),
@to VARCHAR(8000),
@subject VARCHAR(8000),
@body VARCHAR(8000)
AS
DECLARE @result INT
DECLARE @object INT
PRINT 'Creating the CDONTS.NewMail object'
EXEC @result = sp_OACreate 'CDONTS.NewMail', @object OUTPUT
IF @result <> 0
BEGIN
PRINT 'sp_OACreate Failed'
RETURN @result
END
PRINT 'Sending the message using the Send method'
EXEC @result = sp_OAMethod @object, 'Send', NULL, @from, @to, @subject, @body
IF @result <> 0
BEGIN
PRINT 'sp_OAMethod Failed'
RETURN @result
END
PRINT 'Destroying the CDONTS.NewMail object'
EXEC @result = sp_OADestroy @object
IF @result <> 0
BEGIN
PRINT 'sp_OADestroy Failed'
RETURN @result
END
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Thanks for all the help guys. This is a real pain in the butt to set up. It was all in the setup nothing wrong with the code. It must have a bunch of stuff the main thing being domain account with a mailbox for the service logon. Read the documentation eh?
-
Grasshopper
I'm experiencing the same problem here. What were your setup problems ?
Thx in advance
J
JV -
First make sure that you can log on the the computer with the same account that the SQL service uses to log on as a service. You also need a mailbox for that user account. Then you need to log on to the server with the service account and create a user profile and test the mail.
I also found you need to fully identify the path to the table you want to query if you are doing so. Here is my working trigger. I still need to format the output so that it looks nice in email.
CREATE TRIGGER staff_update
ON StaffDATA.dbo._tblStaff
FOR INSERT, UPDATE
AS
IF UPDATE (CreatedBy)
BEGIN
EXEC master..xp_sendmail
@recipients = 'helpdesk',
@query = 'Select firstname + '' '' + lastname as name,
listserveace from staffDATA.dbo._tblStaff',
@subject = 'mailing list updates',
@message = 'mailing list updates:',
@attach_results = 'FALSE',
@width = 40
EXEC master..xp_stopmail
END
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply