I have a table with rows which should not be there, means the data is wrong according to business logic. And there is a lot of possible sources the insert statement of the "bad" row is coming from. I'm not able to repro this it's just happening from time to time. And I wanna know which exact SQL statement and module (eg. procedure/trigger / ad-hoc statement) is the source of that insert. To catch the culprit.
Using Profiler or Extended Events is not the way - because you don't see the data (just statements) and while I'm not able to repro this I cannot correlate the output with the data.
I've tried collect info from sys.dm_exec_input_buffer(@@SPID,0) inside my after triggering on insert (made for logging purposes, collecting every insert in that table). But this is too wide. It's giving just initial SQL statement, let say outer stored procedure, but the insert statement could be invoked in some nested stored procedure or trigger.
I've tried to collet output of sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) - see the whole code below. But this is too narrow in opposite - it is giving me statement of my collecting after trigger.
Is there a way to catch something like it?
SELECT OuterSQL = CAST(T.text as NVARCHAR(255)) ,InnerSQL = CAST((SUBSTRING(t.text,(s.statement_start_offset/2) + 1, (CASE WHEN s.statement_end_offset = -1 THEN DATALENGTH(t.text) ELSE s.statement_end_offset END - s.statement_start_offset)/2 + 1)) as NVARCHAR(255)) FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) T INNER JOIN sys.dm_exec_query_stats s ON sys.dm_exec_connections.most_recent_sql_handle = s.sql_handle WHERE session_id = @@SPID;