Forum Discussion

Anna_Heinemann's avatar
Anna_Heinemann
Occasional Reader
Aug 06, 2026

SQL Server 2022 (Compatibility Level 160): GetSchemaTable() returns null

We have encountered a reproducible metadata derivation issue on some customer installations after moving databases to SQL Server 2022 compatibility level 160.

The issue occurs during ADO.NET schema discovery when DbCommandBuilder attempts to derive base-table and key information in order to automatically generate INSERT/UPDATE/DELETE commands.

Environment

  • SQL Server 2022 (different releases)
  • Database compatibility level 160
  • Microsoft.Data.SqlClient (different versions) as well as System.Data.SqlClient
  • ADO.NET DbCommandBuilder
  • Parameterized SELECT statements
  • Identical database schema across customers

Symptoms

For some customers (7 so far, since 2023) for certain tables and columns, DbDataReader.GetSchemaTable() returns null. As a consequence, automatic command generation fails with errors similar to:

Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.

The underlying SELECT statement itself works correctly and returns rows when executed normally. The issue only occurs during metadata derivation.

Example

The following pattern triggers the problem:

SELECT
   *
FROM SomeTable
WHERE SomePrimaryKeyColumn = @Parameter

GetSchemaTable() returns null.

However, if we use a constant value instead of a parameter or cast the value of the column explicitly, GetSchemaTable() returns valid metadata.

SELECT
   *
FROM SomeTable
WHERE SomePrimaryKeyColumn = 0

or

SELECT
   *
FROM SomeTable
WHERE CAST(SomePrimaryKeyColumn AS int) = @Parameter

Characteristics of the issue

The behavior is deterministic.

  • It always affects the same primary key column(s) of a composite primary key.
  • It is independent of the SELECT list.
  • It depends on the presence of a parameterized predicate.
  • The actual parameter value does not matter.
  • NULL, DBNull.Value and non-null integer values all show the same behavior.
  • The parameter type is always int.
  • The issue currently affects only few tables. Different tables for different customers.
  • Other columns in the same tables may work correctly.

Metadata retrieval mode

The issue only occurs when the command is executed using:

CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo

which is the mode internally used by DbCommandBuilder to derive schema and key information If SchemaOnly is removed, metadata retrieval succeeds.

Observed behavior:

  • SchemaOnly | KeyInfo -> GetSchemaTable() returns null
  • KeyInfo only                -> Metadata returned correctly
  • Normal execution       -> Query executes and returns metadata correctly

Therefore the problem appears to be specific to SQL Server's metadata-only derivation path rather than normal query execution.

Things we verified

Database schema

The database schema is identical across customer installations. The issue cannot be correlated with schema differences.

Query text / Select list

The issue is independent of the projection.

For example:

SELECT *

and

SELECT ColumnA

show the same behavior. Only the predicate appears to matter.

Execution vs metadata retrieval

The query executes successfully. Only metadata discovery fails.

Plan cache

The behavior does not appear to be related to plan cache reuse. The same affected predicate consistently fails during schema discovery.

SQL Profiler observations

During schema derivation we observed the following with SQL Profiler (re-worked to an example and reformatted to make it more easily readable for a human being):

 exec sp_executesql N'SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;
        SELECT  
            SomeColumn
       FROM SomeTable
       WHERE         PrimaryKeyColumn1 = @PrimaryKeyColumn1
                 AND PrimaryKeyColumn2 = @PrimaryKeyColumn2
                 AND PrimaryKeyColumn3 = @PrimaryKeyColumn3
                 AND PrimaryKeyColumn4 = @PrimaryKeyColumn4 '
      ,N'@PrimaryKeyColumn1 int,@PrimaryKeyColumn2 int,@PrimaryKeyColumn3 int,@PrimaryKeyColumn4 int'
      ,@PrimaryKeyColumn1=NULL,@PrimaryKeyColumn2=NULL,@PrimaryKeyColumn3=NULL,@PrimaryKeyColumn4=NULL

The issue occurs only in the metadata-only path used by SchemaOnly | KeyInfo. When the same query is actually executed, metadata and result data are returned correctly.

Compatibility level observations

The issue is reproducible at:

COMPATIBILITY_LEVEL = 160

only. It disappears when the database compatibility level is lowered to:

COMPATIBILITY_LEVEL = 150

or smaller. More interestingly, the database can remain at compatibility level 160 while the problem disappears if the query is compiled using compatibility level 150 (or smaller) optimizer behavior:

OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150'))

With this hint, GetSchemaTable() successfully returns metadata.

Additional observations

The following query hints also make schema derivation succeed:

OPTION (RECOMPILE)

and

OPTION (OPTIMIZE FOR UNKNOWN)

This suggests that the metadata derivation path is sensitive to optimizer behavior, despite the fact that the query is not actually executed.

Question

Has anyone encountered a SQL Server 2022 / Compatibility Level 160 issue where:

  • GetSchemaTable() returns null
  • the command is executed with SchemaOnly | KeyInfo
  • the underlying query executes normally
  • the problem only occurs for specific parameterized predicates
  • compatibility level 150 or smaller works
  • QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150 also works while the database remains at level 160

If so, do you have any idea on how to solve this?

1 Reply

  • MW_DEV's avatar
    MW_DEV
    Tin Contributor
    Aug 07, 2026

    I had a very similar issue at the beginning of this year, so I would like to share some notes from my troubleshooting. Hopefully this helps narrow down the root cause and saves you some time. In my case, it turned out to be related to SQL Server 2022 Query Optimizer / Parameter Sensitive Plan.

    One of the strongest clues in your case is that this already helps:

     

    OPTION (USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150'))

    That is important because it keeps the database at compatibility level 160, but makes the query optimizer behave more like compatibility level 150 for that query. Since the issue disappears with this hint, it strongly suggests that the problem is somewhere in the optimizer behavior introduced or enabled with compatibility level 160.

    Starting with compatibility level 160, SQL Server 2022 enables Parameter Sensitive Plan Optimization (PSP) by default: 

    https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization?view=sql-server-ver17 

    There is also a very relevant SQL Server 2022 fix in CU4. Bug reference 2306669 is described by Microsoft as:

    Fixes an issue where parameter sensitive plan (PSP) optimization produces a dispatcher expression but fails to create a query variant when an application attempts to use the SET FMTONLY ON T-SQL statement to return only metadata.

    https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2022/cumulativeupdate4 

     

    What is also worth noting is that CU4 was released back in 2023. That may sound old, but in my case it was still directly relevant because the customer was running SQL Server 2022 and had never installed any cumulative updates after the original installation.

    Installing the CU was ultimately what fixed the issue for me.

    The problem first appeared on a freshly built DEV environment, so I had the advantage of being able to test different options, change compatibility settings, and install updates without much operational impact.

    Anyway, this seems also relevant because ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo) can use the metadata/browse path involving SET FMTONLY ON. And Microsoft also treats SET FMTONLY as a legacy mechanism and recommends newer metadata discovery methods such as sp_describe_first_result_set:

     

    https://learn.microsoft.com/en-us/sql/t-sql/statements/set-fmtonly-transact-sql?view=sql-server-ver17 

    https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-first-result-set-transact-sql?view=sql-server-ver17 

     

    What I would try:

    1. Check the exact SQL Server 2022 build

    SELECT 
@@VERSION, SERVERPROPERTY('ProductVersion') AS ProductVersion, 
SERVERPROPERTY('ProductLevel') AS ProductLevel, 
SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel;

     

    SQL Server 2022 build list:

    https://learn.microsoft.com/en-us/troubleshoot/sql/releases/sqlserver-2022/build-versions 

    https://learn.microsoft.com/en-us/troubleshoot/sql/releases/download-and-install-latest-updates  

    If the instance is still close to the original SQL Server 2022 RTM build, or generally has not been receiving CUs, I would definitely put updating it high on the list. On a production system, of course, upgrading SQL Server may not be something you can do immediately. It can require planning, testing, approvals, and a maintenance window.

     

    2. Before starting a server upgrade, you can quickly test whether PSP is the trigger

    Since QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150 already helps, I would also try the more targeted PSP-specific hint:

    OPTION (USE HINT('DISABLE_PARAMETER_SENSITIVE_PLAN'))

     

    Documentation:

    https://learn.microsoft.com/en-us/sql/relational-databases/performance/parameter-sensitive-plan-optimization?view=sql-server-ver17 

    If GetSchemaTable() starts returning the expected metadata with this hint as well, that would narrow the issue down even further and provide a strong indication that PSP is the problematic part of the compatibility-level-160 optimizer path.

    I would treat this primarily as a diagnostic test or a targeted workaround, not automatically as the final solution. PSP is an intentional SQL Server 2022 optimization feature designed to improve queries where different parameter values require different execution plans. The workload may actually benefit from it, so disabling it without testing can introduce a performance regression.

     

    3. If disabling PSP works for the individual query, then it is worth testing at database scope

    ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

     

    Documentation:

    https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver17 

    This can help confirm whether PSP is really the root cause across the affected workload. I would still be careful with leaving it disabled at database level permanently, because that affects all eligible queries in the database. It is worth validating the performance impact before treating this as a production fix.

     

    4. The other workarounds you already found also fit this theory

    OPTION (RECOMPILE)
OPTION (OPTIMIZE FOR UNKNOWN)

     

    and even changing the predicate with a CAST.

     

    All of these can change the optimizer's handling of parameterized queries and may avoid the problematic execution/compilation path.

    So based on what I saw in my case, I would strongly suspect PSP / Query Optimizer behavior first.

    The fact that QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150 already resolves the issue, combined with Microsoft's documented CU4 fix for:

    PSP + SET FMTONLY ON + metadata-only query processing

    makes this a very strong direction. If the issue still reproduces on a current SQL Server 2022 CU, I would then consider reporting it to Microsoft as a possible regression or another variant of bug 2306669.

     

    I cannot guarantee that the exact same fix will work in your environment, but hopefully these troubleshooting notes at least get you closer to the end of the investigation and help explain what is actually causing the behavior.