Column 'X' does not belong to underlying table 'events'

[zikato]

I have a peculiar problem. I'm tracking an event and collecting the sql_text global field/action. But when the event has column is_system = true then sql_text is not collected at all (the schema shape has changed)

Service Broker activation is considered a system process even though it's arguably a user process.

I propose that the XESmartTarget return a NULL when the column is not found instead of the error. The SSMS XE viewer acts the same.

I've also tried to split the collection into two responses - one where is_system = 'False' collects the sql_text and the other one which doesn't. My filter snippet looks like this:

"Filter": "query_hash_signed <> 0 OR is_system = 'True'"

Then I've got an error:

Error - XESmartTarget.Core.Target : servername Error - XESmartTarget.Core.Target : Cannot find column [is_system]. Error - XESmartTarget.Core.Target : at System.Data.NameNode.Bind(DataTable table, List1 list) at System.Data.BinaryNode.Bind(DataTable table, List1 list) at System.Data.DataExpression.Bind(DataTable table) at System.Data.DataView.set_RowFilter(String value) at XESmartTarget.Core.Utils.XEventDataTableAdapter.ReadEvent(PublishedEvent evt) in C:\github\XESmartTarget\XESmartTarget.Core\Utils\XEventDataTableAdapter.cs:line 242 at XESmartTarget.Core.Responses.TableAppenderResponse.Enqueue(PublishedEvent evt) in C:\github\XESmartTarget\XESmartTarget.Core\Responses\TableAppenderResponse.cs:line 130 at XESmartTarget.Core.Target.TargetWorker.Process() in C:\github\XESmartTarget\XESmartTarget.Core\Target.cs:line 158

This is weird because the column is definitely there. The column is recognized when I set up a different XE with a different JSON.

[spaghettidba]

Hi Tom, thanks for reporting the issue. Regarding the possibility to return NULL when the column is missing, I'm quite skeptic. The data type of the column is based on the data type of the underlying field/action, so I would have nothing base the choice on. Maybe I can work on a solution if I have an example. Could you please share the session script and json file you used?

[zikato]

I've found the problem with the Cannot find column [is_system]. The column has to be in the OutputColumns section to be recognized even though I only use it for filtering.

I can share the session and json, but the XE data will be hard to reproduce. It monitors Linked server calls through a Service Broker activation.

[zikato]

This is the XE session (I'm planning to blog about it after SQLbits ;) )

CREATE EVENT SESSION [LinkedServer_Outgoing]
ON SERVER
ADD EVENT sqlserver.oledb_query_interface
(
    ACTION
    (
        sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.server_principal_name
        , sqlserver.server_instance_name
        , sqlserver.database_name
        , sqlserver.query_hash_signed
        , sqlserver.sql_text
        , sqlserver.tsql_stack
        , sqlserver.is_system /* to distinguish ServiceBroker Activation */
        )
    WHERE   
        [opcode]='Begin'
        AND [sqlserver].[like_i_sql_unicode_string]([parameters],N'%CommandWithParameters%')
)

And this is the json

{
    "Target": {
        "ServerName": "$ServerName",
        "SessionName": "LinkedServer_Outgoing",
        "FailOnProcessingError": false,
        "Responses": [
            {
                "__type": "GroupedTableAppenderResponse",
                "ServerName": "localhost",
                "DatabaseName": "tempdb",
                "TableName": "LinkedServer_OutgoingAudit",
                "AutoCreateTargetTable": false,
                "OutputColumns": [
                    "server_instance_name",
                    "client_app_name",
                    "client_hostname",
                    "server_principal_name",
                    "database_name",
                    "query_hash_signed",
                    "linked_server_name",
                    "provider_name",
                    "is_system",
                    "COUNT(collection_time) AS count_occurance",
                    "MAX(collection_time) AS last_occurance"
                ],
                "Events": [
                    "oledb_query_interface"
                ],
                "Filter": "query_hash_signed <> 0 OR is_system = 'True'"
            },
            {
                "__type": "GroupedTableAppenderResponse",
                "ServerName": "localhost",
                "DatabaseName": "tempdb",
                "TableName": "LinkedServer_OutgoingAudit",
                "AutoCreateTargetTable": false,
                "OutputColumns": [
                    "server_instance_name",
                    "client_app_name",
                    "client_hostname",
                    "server_principal_name",
                    "database_name",
                    "query_hash_signed",
                    "linked_server_name",
                    "provider_name",
                    "is_system",
                    "sql_text",
                    "COUNT(collection_time) AS count_occurance",
                    "MAX(collection_time) AS last_occurance"
                ],
                "Events": [
                    "oledb_query_interface"
                ],
                "Filter": "query_hash_signed = 0 AND is_system = 'False'" 
            }
        ]
    }
}

And the table definition

DROP TABLE IF EXISTS dbo.LinkedServer_OutgoingAudit
CREATE TABLE dbo.LinkedServer_OutgoingAudit
(
    server_instance_name sysname NOT NULL
    , client_app_name nvarchar(100) NOT NULL
    , client_hostname nvarchar(50) NOT NULL
    , server_principal_name sysname NOT NULL
    , database_name sysname NOT NULL
    , query_hash_signed bigint NULL
    , linked_server_name sysname NOT NULL
    , provider_name varchar(30) NOT NULL
    , sql_text nvarchar(max) NULL
    --, tsql_stack xml NULL /* xml is not supported  */
    --, tsql_stack nvarchar(max) NULL /* nvarchar is supported, but the stack uses handle, which is unique  */
    , count_occurance bigint NOT NULL
    , is_system bit NULL
    , last_occurance datetime2(3) NULL
)

I've tried to use the is_system = 'False' to get around the sql_text missing in "system" events

[spaghettidba]

Thanks! I'll have a look as soon as possible (might take a while...)

[spaghettidba]

Huh, sorry, which version?

[zikato]

According to GitHub - Latest 1.4.5 (downloaded yesterday). Even though I'm not sure how to check it on the installed version

[spaghettidba]

Ah, gotcha. So it didn't work in 1.4.3 but it works in 1.4.5. Thanks for confirming.

[zikato]

Sorry, it was probably confusing. I only had the 1.4.5 version.

Problem 1 - I didn't realize filtering requires the column to also be on the output (that was mistake on my part) Problem 2 - The missing sql_text. Non-system XE rows have that column and system XE rows don't have it (the shape of the events table changes based on that).

I thought maybe doing filter on the is_system and splitting it into two responses could get around that. But the events table check is probably before the filter check. It still fails.

The only workaround I can think of is to split my Extended event into two session (one for system and one without)

[zikato]

Ok, I had another wrong assumption. is_system show either True or False in the SSMS or export, but it really is 1 or 0 respectively.

Updating my filter to is_system = 0 helped.

I think the issue can be closed as there is a workaround by using several responses with the relevant filter.

[spaghettidba]

Awesome, thanks for the feedback. I guess I have to document the limitations you ran into. I'll do that ASAP

[zikato]

Update: Sadly it's not fixed for me. For some events, the sql_text is just not collected and I can't see any pattern explaining why.

You said the data type is based on the underlying field. Would it be possible to always map the global fields? They should have static data types and when the underlying data isn't there a NULL can be passed.