CODEWITHSUNDEEP
sql-servert-sqlloggingerror-handlingwarnings
Mark Williams
Mark Williams

Reputation: 612

How to log warnings (low-severity errors) in SQL 2008 R2

I would like to log warnings thrown from my Transact SQL scripts that aren't going to get caught in a TRY...CATCH block. Is there any way to do this? ERROR_NUMBER(), etc. won't work outside of a catch block and I'm unsure of how to even know to know a warning was thrown. Googling hasn't yielded much.

Upvotes: 0

Views: 1601

Answers (2)

JBone
JBone

Reputation: 3203

You cannot catch these errors with a try catch even if you wrap it in a proc and try. Here is an example.

CREATE PROC P
AS 
BEGIN
    RAISERROR('TEST',9,-1,-1)
END;

BEGIN TRY
    EXEC P
END TRY
BEGIN CATCH
    PRINT 'CAUGHT'
END CATCH;

Upvotes: 0

erikxiv
erikxiv

Reputation: 4075

The documentation seems to intend that the error message be passed pack to the caller. It does also however state that if you wrap the statements in a stored procedure, and then call that one within a try-catch block, you will catch low-severity errors.

-- Verify that the stored procedure does not exist.
IF OBJECT_ID ( N'usp_ExampleProc', N'P' ) IS NOT NULL 
    DROP PROCEDURE usp_ExampleProc;
GO

-- Create a stored procedure that will cause an 
-- object resolution error.
CREATE PROCEDURE usp_ExampleProc
AS
    SELECT * FROM NonexistentTable;
GO

BEGIN TRY
    EXECUTE usp_ExampleProc;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Upvotes: 1

Related Questions