May 4, 2012 at 4:43 pm
So I have an after trigger that sends an email. I want the insert to happen whether the trigger works or not. Meaning if for some reason it can't send the email I still want the insert to work. I tried putting a try catch but it still doesn't work.
This is the error I am getting.
Msg 3930, Level 16, State 1, Procedure tr_Insert_Email, Line 62
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The statement has been terminated.
If I change the try catch to just Print 'It Failed' This is the error I get.
It Failed
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.
What am I missing?
ALTER TRIGGER [dbo].[tr_Insert_Email]
ON [dbo].[ApplicationLog]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @LogDate DateTime
DECLARE @Logger Varchar(255)
DECLARE @Message Varchar(1000)
DECLARE @Exception VARCHAR(1000)
DECLARE @HostName VARCHAR(225)
DECLARE @TableBody VARCHAR(MAX)
-- Insert statements for trigger here
IF EXISTS (SELECT * FROM INSERTED)
BEGIN
--select * from inserted
SELECT
@LogDate = Date,
@Logger = Logger,
@Message = Message,
@Exception = Exception,
@HostName = HostName
FROM Inserted
SET @TableBody = N'<table border="1">' +
N'<tr><td>Log Time</td><td>LogDateText</td></tr>' +
N'<tr><td>Logging Application</td><td>LoggerText</td>' +
N'<tr><td>Message</td><td>MessageText</td></tr>' +
N'<tr><td>Exception</td><td>ExceptionText</td></tr>' +
N'<tr><td>Host Name</td><td>HostNameText</td></tr>' +
N'</table>';
SET @TableBody = Replace(@TableBody, 'LogDateText', @LogDate);
SET @TableBody = Replace(@TableBody, 'LoggerText', @Logger);
SET @TableBody = Replace(@TableBody, 'MessageText', @Message);
SET @TableBody = Replace(@TableBody, 'ExceptionText', @Exception);
SET @TableBody = Replace(@TableBody, 'HostNameText', @HostName);
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail
@recipients='joe.smith@mac.com',
@subject = 'Audit Log Notification',
@body = @TableBody,
@body_format = 'HTML',
@profile_name='' ;
END TRY
BEGIN CATCH
Insert Into Test
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
END
END
May 5, 2012 at 1:05 am
pamozer (5/4/2012)
So I have an after trigger that sends an email. I want the insert to happen whether the trigger works or not. Meaning if for some reason it can't send the email I still want the insert to work. I tried putting a try catch but it still doesn't work.This is the error I am getting.
Msg 3930, Level 16, State 1, Procedure tr_Insert_Email, Line 62
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The statement has been terminated.
If I change the try catch to just Print 'It Failed' This is the error I get.
It Failed
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.
What am I missing?
ALTER TRIGGER [dbo].[tr_Insert_Email]ON [dbo].[ApplicationLog]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @LogDate DateTime
DECLARE @Logger Varchar(255)
DECLARE @Message Varchar(1000)
DECLARE @Exception VARCHAR(1000)
DECLARE @HostName VARCHAR(225)
DECLARE @TableBody VARCHAR(MAX)
-- Insert statements for trigger here
IF EXISTS (SELECT * FROM INSERTED)
BEGIN
--select * from inserted
SELECT
@LogDate = Date,
@Logger = Logger,
@Message = Message,
@Exception = Exception,
@HostName = HostName
FROM Inserted
SET @TableBody = N'<table border="1">' +
N'<tr><td>Log Time</td><td>LogDateText</td></tr>' +
N'<tr><td>Logging Application</td><td>LoggerText</td>' +
N'<tr><td>Message</td><td>MessageText</td></tr>' +
N'<tr><td>Exception</td><td>ExceptionText</td></tr>' +
N'<tr><td>Host Name</td><td>HostNameText</td></tr>' +
N'</table>';
SET @TableBody = Replace(@TableBody, 'LogDateText', @LogDate);
SET @TableBody = Replace(@TableBody, 'LoggerText', @Logger);
SET @TableBody = Replace(@TableBody, 'MessageText', @Message);
SET @TableBody = Replace(@TableBody, 'ExceptionText', @Exception);
SET @TableBody = Replace(@TableBody, 'HostNameText', @HostName);
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail
@recipients='joe.smith@mac.com',
@subject = 'Audit Log Notification',
@body = @TableBody,
@body_format = 'HTML',
@profile_name='' ;
END TRY
BEGIN CATCH
Insert Into Test
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
END
END
Look into Service broker. Create a message queue that accepts a message with the email to be sent. Once the message has been sent to the queue then the trigger succeeds. The queue should have an automated stored proc to send the mail.
Fitz
May 5, 2012 at 7:27 am
sp_senddb_mail Is a service broker; it's already asyncronous
more likely is the person calling the procedure doesn't have permissions to send dbmail, which requires being a user in the DatabasemailUserRole in the msdb database. if the end user is not a memeber, the trigger will roll back due to the permissiosn error when trying to send the dbmail item.
some options would be using EXECUTE AS , or making any logins or windows roles also users int he msdb database,a nd assigning the DatabasemailUserRole role to them , or even assigning the public role to the DatabasemailUserRole ;
Lowell
May 5, 2012 at 8:10 am
Lowell (5/5/2012)
sp_senddb_mail Is a service broker; it's already asyncronousmore likely is the person calling the procedure doesn't have permissions to send dbmail, which requires being a user in the DatabasemailUserRole in the msdb database. if the end user is not a memeber, the trigger will roll back due to the permissiosn error when trying to send the dbmail item.
some options would be using EXECUTE AS , or making any logins or windows roles also users int he msdb database,a nd assigning the DatabasemailUserRole role to them , or even assigning the public role to the DatabasemailUserRole ;
Agreed, it is asynchronous but setting up a service broker to be sent a message will allow all mail to be sent through a single point and ony that stored proc needs the execute as, not all the procedures and triggers.
Fitz
May 7, 2012 at 9:23 am
Lowell (5/5/2012)
sp_senddb_mail Is a service broker; it's already asyncronous
+1
There is no sense in wrapping DB mail with a SB queue IMO.
If you don't want to mess with permissions at all and you're fine with everyone on the instance sending email you can enable the guest user in msdb and add it to the DatabaseMailUserRole.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 7, 2012 at 9:41 am
So I'm thinking I wasn't completely clear in my issue. The dbmail works fine. I am trying to do a try catch in case somebody goes in and plays with the dbmail profile and my trigger fails. The try catch does not seem to allow the insert that triggered the trigger to complete.
Is that clear? I need to make sure that the insert always occurs whether the trigger completes or not.
May 7, 2012 at 9:57 am
pamozer (5/7/2012)
So I'm thinking I wasn't completely clear in my issue. The dbmail works fine. I am trying to do a try catch in case somebody goes in and plays with the dbmail profile and my trigger fails. The try catch does not seem to allow the insert that triggered the trigger to complete.Is that clear? I need to make sure that the insert always occurs whether the trigger completes or not.
hrm; that would need to be tested, i think;
if the profile does not exist, that raises an error level 16:
Msg 14607, Level 16, State 1, Procedure sysmail_verify_profile_sp, Line 42
profile name is not valid
i thought level 16 or above could not be stopped with a try catch, but i may be wrong.
Lowell
May 7, 2012 at 9:59 am
pamozer (5/7/2012)
So I'm thinking I wasn't completely clear in my issue. The dbmail works fine. I am trying to do a try catch in case somebody goes in and plays with the dbmail profile and my trigger fails. The try catch does not seem to allow the insert that triggered the trigger to complete.Is that clear? I need to make sure that the insert always occurs whether the trigger completes or not.
My two cents...you're trying pretty hard to defend against something non-technical happening outside the database, i.e. in case somebody goes in and plays with the dbmail profile is a security and personnel issue, not a trigger code try/catch issue. I would suggest you look into locking down your instance, maybe using Policy Based Management or another monitoring tool to alert you as soon as something gets out of line with what you are expecting, instead of coding around it. If you do this in the trigger, then how many other places are you trying to defend against the possibility? It's a slippery slope. I would recommend trying to tackle the root cause.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 7, 2012 at 10:02 am
I understand what you're saying, but is there any way to do a try catch in the trigger to allow for the originating insert to occur? In case of any error? I was just giving a possible scenario and Ideally we would hope and have enough security in place for that not to happen.
May 7, 2012 at 10:10 am
As Lowell said. Severity 16 errors do not automatically rollback your transaction (unless XACT_ABORT is ON) and are catch-able. It is possible you are running into a fatal error (Severity 19 or higher) inside sp_send_dbmail that is not bubbling out to your output window.
Or you could be seeing an issue when you try to INSERT INTO test inside the CATCH...I have seen issues trying to do additional DML inside a CATCH. When you printed "It Failed" had you removed that insert from the CATCH?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 7, 2012 at 10:13 am
Yes. I had removed the insert. When I did that the error message changed and I got the It Failed along with the Transaction is doomed message.
May 7, 2012 at 10:17 am
What do you get with a CATCH block like this:
BEGIN CATCH;
DECLARE @err_str VARCHAR(2048),
@err_sev INT,
@err_state INT;
SELECT @err_str = ERROR_MESSAGE(),
@err_sev = ERROR_SEVERITY(),
@err_state = ERROR_STATE();
RAISERROR(@err_str, @err_sev, @err_state);
END CATCH;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 7, 2012 at 10:23 am
Msg 50000, Level 16, State 1, Procedure tr_Insert_Email, Line 71
profile name is not valid
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.
May 7, 2012 at 11:01 am
pamozer (5/7/2012)
Msg 50000, Level 16, State 1, Procedure tr_Insert_Email, Line 71profile name is not valid
Msg 3616, Level 16, State 1, Line 1
Transaction doomed in trigger. Batch has been aborted.
Well there is your answer...protect your Mail Profiles 😀
After you clarified the intent of your question, if you feel boxed in by your organization and are not all that confident you can get your arms around protecting your instances then you could consider offloading the mailing...such as with an SB queue, as mentioned earlier. I do not like the idea, it is re-inventing the wheel and is treating a symptom, but if that's your only option then...
BTW error 14607 is being raised out of msdb.dbo.sysmail_verify_profile_sp in this code area with a severity of -1:
ELSE IF (@profile_name IS NOT NULL) -- use name
BEGIN
SELECT @profileid = profile_id FROM msdb.dbo.sysmail_profile WHERE name=@profile_name
IF (@profileid IS NULL) -- name is invalid
BEGIN
RAISERROR(14607, -1, -1, 'profile')
RETURN(4)
END
END
The is being trapped in the catch block but for some reason your transaction is doomed (nice error message from Redmond there) so the trigger has decided to forego committing the original insert...that is puzzling and troubling at the same time.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply