Sql-server – Trying to trace error in Stored Procedure using XEvents

errorsextended-eventssql serverstored-procedurest-sql

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.

Best Answer

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)

Related Solutions

Error calling stored procedure from trigger in Oracle Database 11g

Your trigger doesn't need the CALL keyword.

create or replace trigger TRYTABLE_BEF_UPD_ROW
before update or insert on TryTable
for each row
begin
    TryJavaHelperRun(:new.arg1, :new.arg2);
end;
/

Generally, you should never use CALL in a PL/SQL block-- just execute the procedure. I assume that CALL is some ancient leftover syntactic remnant from some ancient version of Oracle. It allows you to replace the entire PL/SQL block that is the trigger body with a single CALL statement, i.e.

create or replace trigger TRYTABLE_BEF_UPD_ROW
  before update or insert on TryTable
  for each row
  call TryJavaHelperRun(:new.arg1, :new.arg2)
/

Personally, I've never come across a case where there was any benefit to ditching the "normal" PL/SQL syntax in favor of using CALL in a trigger.

This assumes that you can call the TryJavaHelperRun procedure successfully in general.

Sql-server – How to create a stored procedure using xp_fixeddrives

I've recreated your scenario on my test server that has 2 linked servers that were created manually.

CREATE TABLE SERVERNAME
(
    [Name] nvarchar(255)
);

INSERT INTO SERVERNAME ([Name]) SELECT name from SYS.SERVERS;    
GO

CREATE PROCEDURE freespace
AS
BEGIN
    CREATE TABLE #freespace
    (
        drive VARCHAR(50)
        , freedrivespace VARCHAR(50)
    );
    DECLARE @servername VARCHAR(50);
    DECLARE @sql VARCHAR(100);
    DECLARE curspace CURSOR LOCAL FORWARD_ONLY FOR
    SELECT [Name] FROM SERVERNAME;
    OPEN curspace;
    FETCH NEXT FROM curspace INTO @servername;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = '[' + @servername + ']...xp_fixeddrives';
        INSERT INTO #freespace
        EXEC @sql;
        FETCH NEXT FROM curspace INTO @servername;
    END
    CLOSE curspace;
    DEALLOCATE curspace;
    SELECT * FROM #freespace;
END
GO

EXEC FREESPACE;

The results for my server are:

drive   freedrivespace
C       75305
I       277458
K       861878
C       24378
D       90630
F       7575392
G       972578
H       768495
I       50952
J       7150657
C       13799
D       25162
F       6745607
G       982818
H       580167
I       50881
J       7224593

Granted the results are not exactly usable since the name of the server is not shown, however it does work. This tells me the problem is not in your dynamic SQL; the problem lies with your linked server configuration.

Are you able to manually run a simple query such as:

SELECT * FROM [server1].master.sys.tables;

This should either return results, or most likely the same error you are seeing above.

Best Answer

Related Solutions

Error calling stored procedure from trigger in Oracle Database 11g

Sql-server – How to create a stored procedure using xp_fixeddrives

Related Question