Issue with SQL Server compatibility level 2022 and sp_executesql

Eckels, Adam 0

One of our customers running SQL standard edition 2022 wanted to explore setting their database's compatibility level to 2022; but when they do this, at least one of our queries fails to return metadata. So they have to keep the compatibility level at 2019 to avoid the problem. The SQL looks like this:

------------------- start SQL:

exec sp_executesql

N' SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;

select (some columns) from (a table) 

where PropertyId=@propertyid and TransKey=@transkey and TransSrc=@transsrc',

N'@propertyid varchar(3), @transkey varchar(6), @transsrc varchar(7)',

@propertyid='001', @transkey='2222.2', @transsrc='XMLTest'

-------------------end of SQL

When troubleshooting in SSMS [at compatibility level 2022], we're getting a response of "Commands completed successfully." (along with the completion time); but no columns (column headers) appear like they normally would.

Here's the weird part:

Then when we change the @transkey parameter's type to nvarchar, it works - even though the column's type is varchar. It also works if we set the other two parameters' types to nvarchar and keep the @transkey parameter's type as varchar. Except for the select statement, the SQL is auto-generated by a .NET Framework 4.8 DataTable object. What the heck is going on here? Thanks for your help!

Erland Sommarskog 112.7K Reputation points MVP

2024-07-19T21:37:09.9566667+00:00

I would like to ask the same question, but more precisely what in .NET 4.8 insists on using SET FMTONLY ON? They should be using sp_describe_first_result_set, which is a much more robust way of finding the shape of a result set. Oh well.

As for the issue you ran into, I try to repro it with a random table in my collection of database, but no. Would it be possible for you to produce a script that creates the table and runs the query and exhibits the error? Since we are talking about SET FMTONLY ON, you should need in any data in the table.
LucyChenMSFT-4874 5,060 Reputation points

2024-07-23T02:31:49.06+00:00

Hi @Eckels, Adam,

We haven't heard any response from you yet.

Could you please let us know how your issue with SQL Server compatibility level 2022 and sp_executesql is going?

If you have any questions, please feel free to share with us.

May I humbly suggest that you accept Answer if they helped. It may help others who see the thread in seek of help.

Your action would be helpful to other users who encounter the same issue and read this thread. :)

Thanks for your understanding. Wish you have a nice day!

Best regards,

Lucy Chen

3 answers

LucyChenMSFT-4874 5,060 Reputation points

2024-07-22T06:06:28.47+00:00

Hi @Eckels, Adam,

Thank you for reaching out and welcome to Microsoft Q&A!

Please refer to this article, we can get the information below:

Best regards,

Lucy Chen

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

https://docs.microsoft.com/en-us/answers/support/email-notifications
Please sign in to rate this answer.
LucyChenMSFT-4874 5,060 Reputation points

2024-07-24T08:20:20.6566667+00:00

Hi @Eckels, Adam,

Could you please let us know how your issue regarding Issue with SQL Server compatibility level 2022 and sp_executesql is going?

If you have any concerns, please feel free to share your issues here, we will offer you more support.

If the answer has successfully resolved your problem, to smoothly close this thread, please don't forget to 'Accept it'. This will greatly encourage our work and help others who have similar issues!

Thanks for your understanding!

Best regards,

Lucy Chen

Eckels, Adam 0 Reputation points

2024-07-24T22:16:02.0066667+00:00

I'm not using the feature; .NET Framework 4.8 is (DataTable component).
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Eckels, Adam 0 Reputation points

2024-07-24T22:10:40.8033333+00:00

The cumulative update released just yesterday resolved the issue.
https://www.microsoft.com/en-us/download/details.aspx?id=105013
Please sign in to rate this answer.
LucyChenMSFT-4874 5,060 Reputation points

2024-07-25T01:29:05.1166667+00:00

Hi @Eckels, Adam,

Great to hear that you were able to solve the issue!

Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.

This will greatly encourage our work and your action would be helpful to other users who encounter the same issue and read this thread. :)! We hope we can help more people!

Thanks for your understanding!

Best regards,

Lucy Chen

LucyChenMSFT-4874 5,060 Reputation points

2024-07-25T01:31:51.4666667+00:00

The latest progress:

Eckels, Adam finds that the cumulative update released just yesterday resolved the issue.

Please refer to this link and try to resolve this issue!

Best regards,

Lucy Chen

Erland Sommarskog 112.7K Reputation points MVP

2024-07-25T09:06:59.6933333+00:00

That's great to hear! Although, when I look at https://learn.microsoft.com/sv-se/troubleshoot/sql/releases/sqlserver-2022/cumulativeupdate14 I see nothing that matches your problem. That could indicate one of two things:

Microsoft has failed to document the fix (that has certainly happened).

It was due to some weird internal memory corruption, and it got resolved because SQL Server was restarted and the cache was cleared. If this was the case, let's hope it does not come back!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Eckels, Adam 0 Reputation points

2024-07-24T22:14:15.75+00:00

The cumulative update released just yesterday resolved the issue:

https://www.microsoft.com/en-us/download/details.aspx?id=105013
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Issue with SQL Server compatibility level 2022 and sp_executesql

3 answers

Your answer