When running sql in selectMethod=cursor, SET STATISTICS_XML_ON crashes TDSParser

[siggemannen]

Driver version

12.8.1.jre8

SQL Server version

Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64)

Client Operating System

Windows 10

JAVA/JVM version

1.8

Problem description

When using connection string with: selectMethod=cursor, and running something like

set statistics xml on
select 1

driver throws exception in TDSReader#throwInvalidTDSToken

This is probably because unlike TDSTokenHandler#onColMetaData, TDSTokenHandler#onRow doesn't properly handle extra metadata/resultset coming from SHOWPLAN.

Expected behavior

I expect to be able to run the above query without any issues.

Actual behavior

Below error message. Interesting enough, the result is returned back to the client and can be parsed, but then the connection is terminated.

Error message/stack trace

May 13, 2025 10:52:22 PM com.microsoft.sqlserver.jdbc.TDSReader throwInvalidTDSToken
SEVERE: ConnectionID:41 ClientConnectionId: 5b22c202-f6c6-40b2-a3a6-81e22d6ff14d got unexpected value in TDS response at offset:153

Any other details that can be helpful

A complete example to recreate the issue:

public class CursorExecTest
{
    @Test
    public void test_cursor() throws Exception
    {
        try (Connection c = DriverManager.getConnection(
                "jdbc:sqlserver://localhost\\sql2022;user=user;password=pass;lastUpdateCount=false;encrypt=false;trustServerCertificate=true;selectMethod=cursor;responseBuffering=adaptive;");
                Statement s = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);)
        {
            s.execute("SET STATISTICS XML ON;");
            boolean exec = s.execute("SELECT 1");
            if (exec)
            {
                ResultSet rs = s.getResultSet();
                while (rs.next())
                {
                    System.out.println(rs.getInt(1));
                }
            }
        }

    }
}

[siggemannen]

I added some code on how to recreate the issue. The pom is: <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>12.8.1.jre8</version> </dependency>

[Ananya2]

Hi @siggemannen,
We've reviewed the behavior and confirmed that without SET STATISTICS XML ON, the query executes through the expected server cursor flow when selectMethod=cursor is set. However, when SET STATISTICS XML ON is enabled, the server does not return the cursor-related output parameters, and the flow diverges from typical cursor execution. The driver is still expecting cursor-related tokens, which leads to the observed discrepancy.
This appears to be expected server behavior when statistics collection is enabled in this way.

Note:
Server cursors are only supported for a single result set. However, SET STATISTICS XML ON causes the server to return an additional result set (the XML showplan), which violates this constraint.
Reference: Understanding cursor types - (SQL Server restricts server cursors to a single result set. If a batch or stored procedure contains multiple statements, then a forward-only read-only client cursor must be used.)

To work around this, you can either:

• Run the query without cursor flow if you need to capture the XML showplan, or
• Use the cursor flow but omit SET STATISTICS XML ON

[siggemannen]

Hi, i have a generic sql client where users can run queries and see their query plans. So STATISTICS XML is part of the functionality. The cursor jdbc is part of url which the user provides, i don't have any control over it.

mssql-jdbc/src/main/java/com/microsoft/sqlserver/jdbc/tdsparser.java

Line 296 in 52303d8

* SHOWPLAN or something else that produces extra metadata might be ON. Log info instead of throwing an exception, warn

line made me think the same logic can be apply to the onRow-method of the TdsParser, and which would solve the issue i think

[Ananya2]

@siggemannen - You can run the following code:

try (Connection c = DriverManager.getConnection(
         "jdbc:sqlserver://localhost\\sql2022;user=user;password=pass;encrypt=false;trustServerCertificate=true;selectMethod=cursor;");
     Statement stmt = c.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) {

    boolean exec = stmt.execute("SET STATISTICS XML ON; SELECT * FROM sys.objects;");
    if (exec) {
        ResultSet rs = stmt.getResultSet();
        while (rs.next()) {
            System.out.println(rs.getObject(1));
        }

        stmt.getMoreResults();
        rs = stmt.getResultSet();
        while (rs.next()) {
            System.out.println(rs.getObject(1));
        }
    }
}

This approach will not trigger cursor flow and will return the Showplan XML data as expected.
It's important to note that setting selectMethod=cursor isn't the sole determinant for triggering cursor flow. The nature of the query execution also plays a role. For instance, if the query is executed directly (i.e., stmtIn.executedSqlDirectly is true), it triggers direct flow instead of cursor flow.

[siggemannen]

Yes, i can workaround this in a couple of ways, but in my mind, if i driver crashes connection due to normal sql server operations, it's a bug, so i would suggest re-opening the issue.

[Ananya2]

While the current behavior of the driver is technically correct and aligns with SQL Server documentation, we agree that the driver should handle this scenario more gracefully by throwing a clear and descriptive error message, rather than failing due to an invalid token. We’ll track this as an improvement in error handling for unsupported server cursor scenarios.