using msdb..sp_send_dbmail

  • 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.

  • Can You please specify the error message you receive .

  • 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.

  • 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 .

  • 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

  • Hey this happens sometime .

  • 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

  • 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