July 18, 2008 at 3:45 am
Hi All,
What I am wanting is a trigger that runs the exec email code, if there is an error to email a different group that an error occured with the email ID. This trigger runs of a table called EmailHistory, and the values used in the email come from the inserted records...
But it isn't working at all. What am I missing?
/****** Object: Trigger [dbo].[trg_ScriptEmail] Script Date: 07/17/2008 15:36:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_ScriptEmail] ON [dbo].[EmailHistory]
AFTER INSERT
AS
BEGIN
DECLARE@EmailID int
SET @EmailID = (Select EmailID From Inserted)
DECLARE@EmailTo nvarchar(500)
SET @EmailTo = (Select EmailTo From Inserted)
DECLARE@EmailCC nvarchar(500)
SET @EmailCC = (Select EmailCC From Inserted)
DECLARE@EmailBC nvarchar(500)
SET @EmailBC = (Select EmailBC From Inserted)
DECLARE@EmailSubject nvarchar(500)
SET @EmailSubject = (Select EmailSubject From Inserted)
DECLARE@EmailImportance nvarchar(10)
SET @EmailImportance = (Select EmailImportance From Inserted)
DECLARE@EmailAttachments nvarchar(500)
SET @EmailAttachments = (Select EmailAttachments From Inserted)
DECLARE@EmailBody nvarchar(MAX)
SET @EmailBody = (Select EmailBody From Inserted)
DECLARE@EmailQuery nvarchar(MAX)
SET @EmailQuery = (Select EmailQuery From Inserted)
DECLARE@EmailQueryFilename nvarchar(100)
SET @EmailQueryFilename = (Select EmailQueryFilename From Inserted)
DECLARE@EmailAttachToEmail nvarchar(1)
SET @EmailAttachToEmail = (Select EmailAttachToEmail From Inserted)
DECLARE@return_value int
EXEC@return_value = msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = @EmailTo,
@copy_recipients = @EmailCC,
@blind_copy_recipients = @EmailBC,
@subject = @EmailSubject,
@importance = @EmailImportance,
@file_attachments = @EmailAttachments,
@body = @EmailBody,
@query = @EmailQuery,
@query_attachment_filename = @EmailQueryFilename,
@attach_query_result_as_file = @EmailAttachToEmail;
IF @return_value = 0
UPDATE dbo.EmailHistory
SET dbo.EmailHistory.EmailSent = 'TRUE'
WHERE dbo.EmailHistory.EmailID = (SELECT EmailID From INSERTED)
ELSE
DECLARE@EmailError int
SET @EmailError = '***FAILED EMAIL - ' & (Select EmailID From Inserted) * '***'
EXEC@return_value = msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = 'diallerbridge@voicegroup.co.uk',
@copy_recipients = NULL,
@blind_copy_recipients = NULL,
@subject = @EmailError,
@importance = 'High',
@file_attachments = NULL,
@body = 'WARNING, The following email has fialed',
@query = NULL,
@query_attachment_filename = NULL,
@attach_query_result_as_file = 0;
END
July 18, 2008 at 4:32 am
Depending on the error, the mail procedure may not be able to set it's return code, so I'd be inclined to check @@Error, as well as the return code:-
IF @return_value = 0 AND @@Error = 0
UPDATE....
ELSE
As an aside, your trigger doesn't handle the case where > 1 row is inserted by the insert statement.
July 18, 2008 at 5:37 am
I'm not sure what you mean? The trigger works on every insert (the 3rd party tool can only insert 1 record at a time)...
July 18, 2008 at 5:54 am
Ok, first, you should rewrite the trigger to handle having multiple records in the INSERTED table. I understand your application currently cannot insert multiple records, but if the application ever gets fixed you will have a problem. Assuming a table can only get a single record inserted at a time is really bad practice and completely unnecessary - this would be easily handled in your trigger.
Next, don't select from the INSERTED table once per variable. You can speed things up and clean up your code with something like this:
[font="Courier New"]SELECT
@EmailID = EmailID
, @EmailTo = EmailTo
, @EmailCC = EmailCC
FROM INSERTED[/font]
Next, your error handling will work when msdb.dbo.sp_send_dbmail returns an error. However, database mail rarely returns an error - and never because of a problem with sending the email. It will return an error if you have a syntax error in the query or some other major malfunction. Otherwise, database mail uses the service broker to queue up your mail message (and you get the message "Mail Queued". If you need to make sure the mail message was completely successful, you need to get the returned mailitem_id from the procedure and check the queue to determine if it was successful.
It would be a really bad idea to wait for the success or failure of the mail message itself as you do not have control over when the service broker will process the queue. In you case, I would recommend you keep the mailitem_id and have another process that checks the status of mail items on a regular basis and notifies your group when there are one or more items is a problem.
And finally...you should read the books online section about TRY CATCH blocks regarding error handling.
July 18, 2008 at 6:33 am
Okay, some great tips there... I have modified the procedure as follows, taking out the error handling (in terms of checking the procedure itself), and really like the sound of another procedure to check the service borker for delayed returns. You mentions getting the mailitem_id from the servce broker... How can I get that returned?
I have modified the EmailHistory table with a column called MailItemID sdo as to store the ID, but no idea of how to get. Also any suggestions on what procedure I can use to check the service broker email successes?
--V2.4
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trg_ScriptEmailTest] ON [dbo].[EmailHistoryTest]
AFTER INSERT
AS
BEGIN
DECLARE @EmailID int
DECLARE@EmailTo nvarchar(500)
DECLARE@EmailCC nvarchar(500)
DECLARE@EmailBC nvarchar(500)
DECLARE@EmailSubject nvarchar(500)
DECLARE@EmailImportance nvarchar(10)
DECLARE@EmailAttachments nvarchar(500)
DECLARE@EmailBody nvarchar(MAX)
DECLARE@EmailQuery nvarchar(MAX)
DECLARE@EmailQueryFilename nvarchar(100)
DECLARE@EmailAttachToEmail nvarchar(1)
SELECT
@EmailID = EmailID,
@EmailTo = EmailTo,
@EmailCC = EmailCC,
@EmailBC = EmailBC,
@EmailSubject = EmailSubject,
@EmailImportance = EmailImportance,
@EmailAttachments = EmailAttachments,
@EmailBody = EmailBody,
@EmailQuery = EmailQuery,
@EmailQueryFilename = EmailQueryFilename,
@EmailAttachToEmail = EmailAttachToEmail
FROM INSERTED
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = @EmailTo,
@copy_recipients = @EmailCC,
@blind_copy_recipients = @EmailBC,
@subject = @EmailSubject,
@importance = @EmailImportance,
@file_attachments = @EmailAttachments,
@body = @EmailBody,
@query = @EmailQuery,
@query_attachment_filename = @EmailQueryFilename,
@attach_query_result_as_file = @EmailAttachToEmail;
UPDATE dbo.EmailHistoryTest
SET dbo.EmailHistoryTest.EmailSent = 'TRUE'
WHERE dbo.EmailHistoryTest.EmailID = (SELECT EmailID From INSERTED)
END
July 18, 2008 at 6:51 am
It is an output parameter of the procedure you are already calling.
Check books online, but I think this is it:
[font="Courier New"]EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'VoiceSQLMail',
@recipients = @EmailTo,
@copy_recipients = @EmailCC,
@blind_copy_recipients = @EmailBC,
@subject = @EmailSubject,
@importance = @EmailImportance,
@file_attachments = @EmailAttachments,
@body = @EmailBody,
@query = @EmailQuery,
@query_attachment_filename = @EmailQueryFilename,
@attach_query_result_as_file = @EmailAttachToEmail,
@NewMailItemID = @mailitem_id OUTPUT[/font]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply