February 7, 2013 at 2:12 pm
I am trying to test my solution for firing off an email after an error is encountered in SQL server.
To do this I have created a stored procedure that has the msdb..sp_send_dbmail functionality. I then use the TRY CATCH T-SQL technique and in the CATCH BLOCK of the query I call my procedure for sending the email.
If I highlight all the code in the CATCH block only I am able to get a blank email with subject and name of my error variables but not the values that I would like from the variables.
However if I run the TRY section of the query no email is generated and the error messa is only displayed in SSMS, the catch block does not work.
------------------------below is sp for sending the error messg.--------------------------
ALTER PROCEDURE SendErrorMessage
@CURRENTTIME VARCHAR(50),
@CURRENTUSER VARCHAR(50),
@SERVERNAME VARCHAR(50),
@ERROR_NUMBER VARCHAR(50),
@ERROR_MESSAGE VARCHAR(MAX),
@ERROR_SEVERITY VARCHAR(50),
@ERROR_LINE VARCHAR(50),
@ERROR_STATE VARCHAR(10)
--@ERROR_PROCEDURE VARCHAR(50)
AS
DECLARE @MESSAGE_BODY VARCHAR(MAX)
SET @MESSAGE_BODY = '@CURRENTTIME' + '@CURRENTUSER' + '@SERVERNAME'
+ '@ERROR_MESSAGE'+ '@ERROR_SEVERITY'+ '@ERROR_LINE' + '@ERROR_STATE'
EXEC msdb..sp_send_dbmail
@profile_name = 'Admin',
@recipients = '@yahoo.co.uk',
@blind_copy_recipients = '@yahoo.com',
@subject = 'FError',
@body = @MESSAGE_BODY ;
-----------Here is my Code for the Try Catch code---------------------
BEGIN TRY
INSERT LookupCar VALUES (21,NOWAY)
--------there is no table called LookupCar so this does generate an error
END TRY
BEGIN CATCH
DECLARE @ERROR_NUMBER VARCHAR(10)
DECLARE @ERROR_SEVERITY VARCHAR(10)
DECLARE @ERROR_STATE VARCHAR(10)
--DECLARE @ERROR_PROCEDURE VARCHAR(1000)
DECLARE @ERROR_LINE VARCHAR(10)
DECLARE @ERROR_MESSAGE VARCHAR(1000)
DECLARE @CURRENTTIME DATETIME
DECLARE @SERVERNAME VARCHAR(100)
DECLARE @CURRENTUSER VARCHAR(50)
SET @ERROR_NUMBER = ERROR_NUMBER()
SET @ERROR_SEVERITY = ERROR_SEVERITY()
SET @ERROR_STATE = ERROR_STATE()
--SET @ERROR_PROCEDURE = @ERROR_PROCEDURE()
SET @ERROR_LINE = ERROR_LINE()
SET @ERROR_MESSAGE = ERROR_MESSAGE()
SET @CURRENTTIME = CURRENT_TIMESTAMP
SET @SERVERNAME = @@SERVERNAME
SET @CURRENTUSER = CURRENT_USER
EXEC SendErrorMessage @CURRENTTIME = @CURRENTTIME ,
@CURRENTUSER = @CURRENTUSER,
@SERVERNAME = @SERVERNAME ,
@ERROR_NUMBER = @ERROR_NUMBER,
@ERROR_MESSAGE = @ERROR_MESSAGE,
@ERROR_SEVERITY = @ERROR_SEVERITY,
@ERROR_LINE = @ERROR_LINE,
@ERROR_STATE = @ERROR_STATE
END CATCH
Could anyone spot why this code does not work, if I highlighr the code in the catch block I do get an email but if I run code from beginning the error only appears in SSMS.
February 8, 2013 at 2:34 am
Can You please specify the error message you receive .
February 8, 2013 at 3:18 am
The error I get states
'Cannot insert explicit value for identity column in table 'TableName' when IDENTITY_INSERT is set to OFF.'
the ERROR_ SEVERITY is 16
when I run the query select * from sysmail_allitems I can see the email message in the bogy column, but the email does not go through, Database Mail has been configured already and is working since test emails do go through but I thing something is wrong with my code.
Please let me know what you think
or suggest a proven way how to have a sp in the catch block that sends an error email, I would like to use this sp all the time in catch blocks to send error emails.
February 8, 2013 at 5:57 am
I think that your the problem is with your table structure
as error is for table and you can see the status of sysmail_mailtiems
it shouldn't be 1 as 1 is for sent .
February 8, 2013 at 6:16 am
I had just set up a hotmail account to send mails from after going to that mail box it said that I should log in to verify account in order guard against spam so hotmail was actually blocking the sending of the emails until I verified my address by logging in again so the code does work I can now see my error emails at various difernt emails that tat they were sent to
Thanks anyway this took my a day and half to figure out, hope no one else have these problems
February 10, 2013 at 11:53 pm
Hey this happens sometime .
February 11, 2013 at 5:55 am
Did you resolve why you get the names of your variables and not the values? When you set @message_body, you shouldn't quote your variable names because they're treated as string literals. Here's the line I'm referring to with the quotes removed.
SET @MESSAGE_BODY = @CURRENTTIME + @CURRENTUSER + @SERVERNAME
+ @ERROR_MESSAGE + @ERROR_SEVERITY + @ERROR_LINE + @ERROR_STATE
February 11, 2013 at 6:08 am
I did get to work and I did remove the single quotes from my varibles, its my hotmail acount that needed verification.
Anyway I have a differnt problem I am trying to send emails using SSIS I have seen where scripting is required to do so since the Send DB Mail task has limited capabilities.
Do you know how this could be accomplished.
Please see my other post at
http://www.sqlservercentral.com/Forums/Topic1417996-364-1.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply