XESmartTarget icon indicating copy to clipboard operation
XESmartTarget copied to clipboard

Missing column in the TableAppenderResponse created table

Open 0x7FFFFFFFFFFFFFFF opened this issue 11 months ago • 0 comments

I'm playing with XESmartTarget and found that it is not able to create the batch_text column. The following is my event session code.

CREATE EVENT SESSION test_session
ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (ACTION (
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.database_name,
         sqlserver.plan_handle,
         sqlserver.query_hash_signed,
         sqlserver.query_plan_hash_signed,
         sqlserver.request_id,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.transaction_sequence
     )
     WHERE (
         sqlserver.like_i_sql_unicode_string(statement, N'%%')
         OR sqlserver.like_i_sql_unicode_string(sqlserver.sql_text, N'%%')
     )
    ),
    ADD EVENT sqlserver.sp_statement_completed
    (SET collect_object_name = (1)
     ACTION (
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.database_name,
         sqlserver.plan_handle,
         sqlserver.query_hash_signed,
         sqlserver.query_plan_hash_signed,
         sqlserver.request_id,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.transaction_sequence
     )
     WHERE (
         sqlserver.like_i_sql_unicode_string(statement, N'%%')
         OR sqlserver.like_i_sql_unicode_string(sqlserver.sql_text, N'%%')
     )
    ),
    ADD EVENT sqlserver.sql_batch_completed
    (SET collect_batch_text=(1)
	ACTION (
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.database_name,
         sqlserver.plan_handle,
         sqlserver.query_hash_signed,
         sqlserver.query_plan_hash_signed,
         sqlserver.request_id,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.transaction_sequence
     )
     WHERE (
         sqlserver.like_i_sql_unicode_string(sqlserver.sql_text, N'%%')
         OR sqlserver.like_i_sql_unicode_string(batch_text, N'%%')
     )
    ),
    ADD EVENT sqlserver.sql_statement_completed
    (ACTION (
         package0.collect_system_time,
         package0.event_sequence,
         sqlserver.client_app_name,
         sqlserver.client_connection_id,
         sqlserver.client_hostname,
         sqlserver.database_name,
         sqlserver.plan_handle,
         sqlserver.query_hash_signed,
         sqlserver.query_plan_hash_signed,
         sqlserver.request_id,
         sqlserver.server_principal_name,
         sqlserver.session_id,
         sqlserver.sql_text,
         sqlserver.transaction_id,
         sqlserver.transaction_sequence
     )
     WHERE (
         sqlserver.like_i_sql_unicode_string(statement, N'%%')
         OR sqlserver.like_i_sql_unicode_string(sqlserver.sql_text, N'%%')
     )
    )
    ADD TARGET package0.event_file
    (SET filename = N'f:\test\test_session.xel', max_file_size = (64), max_rollover_files = (10))
WITH (
    MAX_MEMORY = 4096KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = ON,
    STARTUP_STATE = OFF
);
GO

This is my XESmartTarget config file.

{
    "Target": {
        "ServerName": ".",
        "SessionName": "test_session",
        "UserName": "",
        "Password": "",
        "FailOnProcessingError": false,
        "Responses": [
            {
                "__type": "TableAppenderResponse",
                "ServerName": "(local)",
                "DatabaseName": "pubs",
                "TableName": "test_session_data",
                "AutoCreateTargetTable": true,
                "UploadIntervalSeconds": 10,
                "UserName": "",
                "Password": "",
                "OutputColumns": [
                    "name",
                    "event_sequence",
                    "collect_system_time",
                    "duration",
                    "cpu_time",
                    "logical_reads",
                    "session_id",
                    "request_id",
                    "sql_text",
                    "batch_text",
                    "statement",
                    "transaction_id",
                    "transaction_sequence",
                    "plan_handle",
                    "query_hash_signed",
                    "query_plan_hash_signed",
                    "object_name",
                    "database_name",
                    "client_app_name",
                    "client_hostname",
                    "server_principal_name",
                    "client_connection_id"
                ]
            }
        ]
    }
}

As you can see, I have batch_text column listed under OutputColumns. I also confirmed that the SSMS live data can show the batch_text column, which is from the sql_batch_completed event. image

When I run XESmartTarget, it created a table like this:

USE [pubs]
GO

/****** Object:  Table [dbo].[test_session_data]    Script Date: 7/19/2023 3:49:53 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[test_session_data](
	[name] [nvarchar](max) NULL,
	[duration] [bigint] NULL,
	[cpu_time] [decimal](20, 0) NULL,
	[logical_reads] [decimal](20, 0) NULL,
	[statement] [nvarchar](max) NULL,
	[transaction_sequence] [decimal](20, 0) NULL,
	[transaction_id] [bigint] NULL,
	[sql_text] [nvarchar](max) NULL,
	[session_id] [int] NULL,
	[server_principal_name] [nvarchar](max) NULL,
	[request_id] [bigint] NULL,
	[query_plan_hash_signed] [bigint] NULL,
	[query_hash_signed] [bigint] NULL,
	[plan_handle] [varbinary](max) NULL,
	[database_name] [nvarchar](max) NULL,
	[client_hostname] [nvarchar](max) NULL,
	[client_connection_id] [uniqueidentifier] NULL,
	[client_app_name] [nvarchar](max) NULL,
	[event_sequence] [decimal](20, 0) NULL,
	[collect_system_time] [datetimeoffset](7) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Why there is no batch_text column in the table? Thanks.

0x7FFFFFFFFFFFFFFF avatar Jul 18 '23 19:07 0x7FFFFFFFFFFFFFFF