Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

using msdb..sp_send_dbmail Expand / Collapse
Author
Message
Posted Thursday, February 7, 2013 2:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 36, Visits: 182
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.
Post #1417349
Posted Friday, February 8, 2013 2:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:05 AM
Points: 251, Visits: 579
Can You please specify the error message you receive .
Post #1417533
Posted Friday, February 8, 2013 3:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 36, Visits: 182
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.
Post #1417561
Posted Friday, February 8, 2013 5:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:05 AM
Points: 251, Visits: 579
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 .
Post #1417645
Posted Friday, February 8, 2013 6:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 36, Visits: 182
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
Post #1417657
Posted Sunday, February 10, 2013 11:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 5:05 AM
Points: 251, Visits: 579
Hey this happens sometime .
Post #1418225
Posted Monday, February 11, 2013 5:55 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
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




Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1418364
Posted Monday, February 11, 2013 6:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 2:17 AM
Points: 36, Visits: 182
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
Post #1418372
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse