Trying to trace error in Stored Procedure using XEvents

Question

Here is the situation:

I have a SQL server ETL application with a stored procedure that is inserting/updating some data into a table. The table has 1 INT column (it is an identity column and there is no inserting of values into that column by the stored procedure) and the rest of the columns are VARCHAR's. In the catch block, the code is logging the stored procedure and Error_Message() to a logging table.

The error message that is being logged is: The conversion of the VARCHAR value '010234567898789' overflowed an int column.

So, I am attempting to find out how to use XEvents to see the exact INSERT/UPDATE statement that the error is really occurring in, but I cannot seem to find the Event that I need to look at to see this.

Can anyone please either point me to a good tutorial or tell me what event(s) that I need to look at to see the INSERT/UPDATE SQL code and the associated error Event? I know it can be done using Profiler, but I was hoping to be able to use XEvents since it is supposed to be much lighter and this is a production server were the issue is occurring.

Thank you.

Answer 1

You can configure an extended event session with an event:

error_reported: This event gets fired every time that an error happens in the server.

For your particular case you are able to filter events for message_id = 248.

SELECT text FROM sys.messages WHERE message_id = 248

The conversion of the %ls value '%.*ls' overflowed an int column.

That event contains a field tsql_frame which looks like one below (just an example):

<frame level="2" handle="0x03000500A711127995BB6600B4AA000001000000000000000000000000000000000000000000000000000000" line="83" offsetStart="2634" offsetEnd="3288" />

So, you got sql_handle plus start and end offsets of the statement you are looking for. And getting the statement (values are from the example above):

SELECT 
    CASE   
        WHEN s.offsetStart > 0 THEN  
            CASE s.offsetEnd  
               WHEN -1 THEN  
                  SUBSTRING(t.TEXT, (s.offsetStart/2) + 1, 2147483647) 
               ELSE   
                  SUBSTRING(t.TEXT, (s.offsetStart/2) + 1, (s.offsetEnd - s.offsetStart)/2+1)   
            END  
        ELSE  
            CASE s.offsetEnd  
               WHEN -1 THEN  
                  RTRIM(LTRIM(t.[text]))  
               ELSE  
                  LEFT(t.TEXT, (s.offsetEnd/2) +1)  
            END  
        END AS [executing statement]
FROM sys.dm_exec_sql_text(0x03000500A711127995BB6600B4AA000001000000000000000000000000000000000000000000000000000000) t
CROSS JOIN (VALUES (2634, 3288) ) s(offsetStart, offsetEnd)