sp_BlitzQueryStore does not work on databases running in compatibility mode 100

[ray13eezy]

Version of the script SELECT @Version = '8.11', @VersionDate = '20221013';

What is the current behavior?

Running sp_BlitzQueryStore against a Query Store enabled DB running compatibility mode 100 on SQL Server 2017 throws an error:

"TRY_CONVERT is not a recognized built-in function name"

If the current behavior is a bug, please provide the steps to reproduce.

Download latest version of scripts, apply to server, run against a Query Store enabled database that is running compatibility mode 100.

I was able to eliminate the issue by changing, on line 2309, [TRY_CONVERT(XML, qsp.query_plan)] to [CONVERT(XML, qsp.query_plan)]

What is the expected behavior?

Logic that can use TRY_CONVERT for newer databases and CONVERT for older databases, or a disclaimer stating this procedure is incompatible with on-prem SQL Server databases running in Compatibility Level < 110.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

I have only tested this SQL Server 2017 CU18 on Windows Server 2016, and have not tested it prior to today.

[BrentOzar]

We do need the try in there because not all plans will resolve to XML.Can you elaborate more on why you’re using compat level 100? If you need the performance analysis that Query Store provides, why wouldn’t you also want higher compat levels?---Tiny glass keyboardTypos flowing like riversAs winter snow thawsOn Oct 13, 2022, at 12:39 PM, Ray @.***> wrote: Version of the script SELECT @Version = '8.11', @VersionDate = '20221013'; What is the current behavior? Running sp_BlitzQueryStore against a Query Store enabled DB running compatibility mode 100 on SQL Server 2017 throws an error: "TRY_CONVERT is not a recognized built-in function name" If the current behavior is a bug, please provide the steps to reproduce. Download latest version of scripts, apply to server, run against a Query Store enabled database that is running compatibility mode 100. I was able to eliminate the issue by changing, on line 2309, [TRY_CONVERT(XML, qsp.query_plan)] to [CONVERT(XML, qsp.query_plan)] What is the expected behavior? Logic that can use TRY_CONVERT for newer databases and CONVERT for older databases, or a disclaimer stating this procedure is incompatible with on-prem SQL Server databases running in Compatibility Level < 110. Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures? I have only tested this SQL Server 2017 CU18 on Windows Server 2016, and have not tested it prior to today.

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: @.***>

[ray13eezy]

Management and old guard are hesitant about bumping compat levels - this DB has been in existence since the 90s. I have successfully coerced them into upgrading to SQL Server 2017, adding AlwaysOn, and the Query Store. Baby steps.

At any rate, this sproc is not documented as not working on compat level < 100 on SQL Server 2017, so I figured I would open this issue so you are at least aware of the oddity.

[BrentOzar]

Ok, cool. I’m going to close the issue for now, but you’re welcome to modify the code on your side.---Tiny glass keyboardTypos flowing like riversAs winter snow thawsOn Oct 13, 2022, at 12:46 PM, Ray @.***> wrote: Management and old guard are hesitant about bumping compat levels - this DB has been in existence since the 90s. I have successfully coerced them into upgrading to SQL Server 2017, adding AlwaysOn, and the Query Store. Baby steps. At any rate, this sproc is not documented as not working on compat level < 100 on SQL Server 2017, so I figured I would open this issue so you are at least aware of the oddity.

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>

[erikdarlingdata]

This is an easy fix -- we can use TRY_CAST, which works across more versions/compat levels. I started using it in my own procs to avoid shameful errors.

TRY_CAST is not a new reserved keyword and is available in all compatibility levels.

[BrentOzar]

Thanks for the pull request! Looks good. Merging into the dev branch, will be in the December release with credit to you in the release notes.