October 29, 2018 at 9:56 am
I created an extended event which is great returns the statement I want (filtering on a specific SP) when it was run and by what app. However unlike in profiler where I will see the values passed when the sp is run I am just getting the sp without the values passed. Here is the event created CREATE EVENT SESSION [Query Trace] ON SERVER
ADD EVENT sqlserver.exec_prepared_sql(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%update%') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%OrderImportStaging%') AND [sqlserver].[database_id]=(15))),
ADD EVENT sqlserver.sql_statement_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%OrderImportStaging%') AND [sqlserver].[database_id]=(15)))
ADD TARGET package0.event_file(SET filename=N'c:\media\Query-Trace.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
October 29, 2018 at 10:56 am
tcronin 95651 - Monday, October 29, 2018 9:56 AMI created an extended event which is great returns the statement I want (filtering on a specific SP) when it was run and by what app. However unlike in profiler where I will see the values passed when the sp is run I am just getting the sp without the values passed. Here is the event created CREATE EVENT SESSION [Query Trace] ON SERVER
ADD EVENT sqlserver.exec_prepared_sql(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%update%') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%OrderImportStaging%') AND [sqlserver].[database_id]=(15))),
ADD EVENT sqlserver.sql_statement_starting(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle,sqlserver.session_id,sqlserver.sql_text,sqlserver.username)
WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%UPDATE %') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%OrderImportStaging%') AND [sqlserver].[database_id]=(15)))
ADD TARGET package0.event_file(SET filename=N'c:\media\Query-Trace.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
GO
This is a great article on that - discusses the exact scenario and how/when those are catpured:
Extended Events and Stored Procedure Parameter Values
Sue
October 29, 2018 at 2:07 pm
close only issue is I am looking for any statement, not just sp's (SSIS) that do the update, will see if I can tweak it
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply