Sample Header Ad - 728x90

Why can I not capture dynamic SQL called with EXEC using the sqlserver.sql_batch_completed Extended Event?

0 votes
1 answer
161 views
It is my understanding that dynamic SQL is part of a batch. Yet, when I listed for sqlserver.sql_batch_completed as follows
CREATE EVENT SESSION [CaptureBatch] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text)
    WHERE ([sqlserver].[database_id]>(4))) 
ADD TARGET package0.event_file(SET filename=N'CaptureBatch',max_rollover_files=(0))
WITH (STARTUP_STATE=ON);

GO

ALTER EVENT SESSION [CaptureBatch] ON SERVER 
STATE = START;
I cannot see any calls to EXEC that also call EXEC. For example, the final line here does not show in my Extended Event's log.
CREATE PROCEDURE [TestThis]
AS
BEGIN
	SELECT 'Test';
END

GO

EXEC (N'EXEC TestThis');
Why is this? Are these kinds of EXEC not considered a batch or am I missing something? I cannot seem to find any extensive documentation on this event, so forgive me if I have missed some.
Asked by J. Mini (1237 rep)
Nov 20, 2024, 09:30 PM
Last activity: Nov 21, 2024, 08:18 AM