Sql-server – No email alert on Procedure fail

database-mailsql server

enter image description here I have configured Database mail and alert, test email is being sent successfully to designated recipient who were part of OPERATORS but I have created an alert on EXECUTE PROCEDURE (exec sp_insert_shift_A_data; which is not sending email on Procedure fail error, I have set SEVERITY to "016 Miscellaneous user error" and CHECK on raise alert when message contains and MESSAGE TEXT given "Violation of UNIQUE KEY constraint".

Kindly guide me if there is any mistake in configuration or How it is configured? Why it is not sending email on Procedure fail error while procedure failed to execute?

Best Answer

The severity value for a unique key violation error is 14, not 16. You need to set your alert to fire on severity 14 instead. You can see the severity returned using the below code:

CREATE TABLE Test123 (Num INT)
GO

INSERT INTO Test123 (Num)
VALUES (1)
GO

CREATE UNIQUE INDEX UQ123 ON Test123(Num)
GO

BEGIN TRY
    INSERT INTO Test123 (Num)
    VALUES (1)
    DROP TABLE Test123
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS Severity, ERROR_MESSAGE() AS Message
    DROP TABLE Test123
END CATCH
GO

Alternatively, change your alert to fire on error number 2601 (the error number for duplicate key value errors).

EDIT: The above is valid for a unique index. For a unique constraint, the severity is still 14, but the error number is 2627.

Related Question