SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


using msdb..sp_send_dbmail


using msdb..sp_send_dbmail

Author
Message
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 216
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.
jeetsingh.cs
jeetsingh.cs
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 618
Can You please specify the error message you receive .
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 216
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.
jeetsingh.cs
jeetsingh.cs
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 618
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 .
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 216
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
jeetsingh.cs
jeetsingh.cs
Old Hand
Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)Old Hand (339 reputation)

Group: General Forum Members
Points: 339 Visits: 618
Hey this happens sometime .
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16685 Visits: 10070
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
kingdonshel
kingdonshel
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 216
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search