Skip to content

Missing column in the TableAppenderResponse created table #32

Open
@0x7FFFFFFFFFFFFFFF

Description

@0x7FFFFFFFFFFFFFFF

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.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions