SQL Server Profiler Exception tracing - Error procedure and line number omitted 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?



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