Sql-server – SQL Server Profiler Exception tracing – Error procedure and line number omitted

error handlingprofilersql server

Is there a trace configuration or setting that will enable the profiler to output ERROR_PROCEDURE() and ERROR_LINE() information that is always available when writing manual error handling in CATCH blocks?

enter image description here

Doing a full trace of every statement being executed is very overwhelming on active systems (I got 45k entries trying it on my test system), and even then you have to guess where the error came from based on the preceding entries.

Best Answer

If you're able to use Extended Events, this is a lot easier. As you said:

Doing a full trace of every statement being executed is very overwhelming on active systems

You can set up the XE session like this:

CREATE EVENT SESSION exceptions
    ON SERVER
    ADD EVENT sqlserver.error_reported
    ( ACTION ( sqlserver.sql_text, sqlserver.tsql_frame ))
    ADD TARGET package0.event_file
    ( SET filename = N'c:\temp\exceptions' )
    WITH ( MAX_MEMORY = 4096KB,
           EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
           MAX_DISPATCH_LATENCY = 5 SECONDS,
           MAX_EVENT_SIZE = 0KB,
           MEMORY_PARTITION_MODE = NONE,
           TRACK_CAUSALITY = OFF,
           STARTUP_STATE = OFF );
GO

A simple proc to throw an error:

CREATE PROCEDURE dbo.error_out
AS 
BEGIN
    SELECT CASE WHEN 1 = 1 
                THEN 1 /0 
                ELSE NULL 
           END
END
GO

Start the session and run the procedure a couple few times:

ALTER EVENT SESSION exceptions ON SERVER STATE = START;
GO 

EXEC dbo.error_out;
GO

Then check your XE session:

SELECT CONVERT(XML, x.event_data) AS x
INTO #xml
FROM   sys.fn_xe_file_target_read_file('c:\temp\exceptions*.xel', NULL, NULL, NULL) AS x;

SELECT  x.x.value('(event/@timestamp)[1]', 'DATETIME2(7)') AS event_date,
        x.x.value('(event/data[@name="message"]/value)[1]', 'VARCHAR(2044)') AS message,
        x.x.value('(event/action[@name="sql_text"]/value)[1]', 'VARCHAR(2044)') AS sql_text,
        ca.c.value('(@line)[1]', 'INT') AS line_number,
        ca.c.value('(@offsetStart)[1]', 'INT') AS offset_start,
        ca.c.value('(@offsetEnd)[1]', 'INT') AS offset_end
FROM #xml AS x
CROSS APPLY x.nodes('//frame') AS ca (c)

You should get results something like this:

NUTS

Related Question