Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Missing column in the TableAppenderResponse created table #32

Open
0x7FFFFFFFFFFFFFFF opened this issue Jul 18, 2023 · 0 comments
Open

Comments

@0x7FFFFFFFFFFFFFFF
Copy link

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant