send dbmail on @@error from sql server 2005
- by Ved
Hi,
I am trying to send database mail when error occurs inside the transaction.My setup for dbo.sp_send_dbmail is correct , when I execute the proc I do get an email within 1 min.
However when I try to use dbo.sp_send_dbmail inside another proc within transactions than I do not get the email. Sql server does show in the result window that "Mail queued" but I never receive it.
BEGIN TRANSACTION
DECLARE @err int
DECLARE @test nvarchar(max)
RAISERROR('This is a test', 16, 1)
SELECT @err = @@ERROR
IF @err < 0
BEGIN
SET @test = error_message()
EXEC msdb.dbo.sp_send_dbmail
@recipients= '[email protected]',
@body = 'test inside',
@subject = 'Error with proc',
@body_format = 'HTML',
@append_query_error = 1,
@profile_name ='Database Mail Profile';
ROLLBACK TRANSACTION
RETURN
END
COMMIT TRANSACTION
And I get result as
Msg 50000, Level 16, State 1, Line 7
This is a test
Mail queued.
