Adjusting compatibility level to SQL Server 2019 causes the application to freeze

[MCIR]

After applying the 7.2.1.6 update on the client's computer, I adjusted the compatibility level in the database properties options tab from SQL Server 2008 to SQL Server 2019 and saved the changes as you had recommended, then tested how the application would run with the modified compatibility level.

The application opens with no issues but attempting to perform tasks results in the application freezing, followed by the display of about 2 or 3 error messages seen below. Despite logging out and back in to both the application and the SQL server, the problem has persisted.

To investigate if the freezing is related to the compatibility level changes, I replicated the process on my personal computer and got the same outcome. Attempting to perform tasks in the application caused it to freeze and showed error messages like the ones shown on the client’s computer. Reverting the compatibility level back to SQL Server 2008 on my personal computer, then restarted both the SQL server and application resolves the freezing issue.

Should the compatibility level be maintained at SQL Server 2008? Not sure how to address this issue

[christillman]

I have the fix for fn_attribute_description, which is the source of this issue, in release 7.2.1.7. I also submitted feedback to Microsoft about the issue.

[MCIR]

Ok, thanks. So keep the compatibility level at 2008?

…

On Fri, 8 Mar 2024 at 21:05, Christopher Tillman ***@***.***> wrote: I have the fix for fn_attribute_description, which is the source of this issue, in release 7.2.17. I also submitted feedback to Microsoft about the issue. — Reply to this email directly, view it on GitHub <#46 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJT6WKLXREL57GUAAWN2JWDYXIDYTAVCNFSM6AAAAABEM52UCWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOBWGI2TAMZTG4> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[christillman]

I meant 7.2.1.7. On my end, it is doing fine with compatibility 150. But you would probably want to set it up to 150 only on your own computers for now, to detect any other issues as you use it. On Sun, 10 Mar 2024, 18:02 Madeline Wanjiru, ***@***.***> wrote:

…

Ok, thanks. So keep the compatibility level at 2008? On Fri, 8 Mar 2024 at 21:05, Christopher Tillman ***@***.***> wrote: > I have the fix for fn_attribute_description, which is the source of this > issue, in release 7.2.17. I also submitted feedback to Microsoft about the > issue. > > — > Reply to this email directly, view it on GitHub > < #46 (comment)>, > or unsubscribe > < https://github.com/notifications/unsubscribe-auth/AJT6WKLXREL57GUAAWN2JWDYXIDYTAVCNFSM6AAAAABEM52UCWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOBWGI2TAMZTG4> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> > — Reply to this email directly, view it on GitHub <#46 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AGEGTUYLUVNLILLHKESTV2LYXPSPJAVCNFSM6AAAAABEM52UCWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTSOBXGA4TCNBZGE> . You are receiving this because you commented.Message ID: ***@***.***>

[christillman]

I found a new compatibility problem this morning. Don't bother trying to set 7.2.1.7 to 150 yet. I got this message after clicking on a patient:

Message Severity: 4 - ERROR
Message Date/Time: 13/03/2024 06:52:06
Epro Version: PB Client 7.2.1.7 (10/03/2024)
Object Class: u_sqlca
Object Script: u_sqlca.check:0108
Error Message: SQL ERROR = (8632) SQLSTATE = 42000
Microsoft OLE DB Driver for SQL Server
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Rolling back to SQL 2008 compatibility solved it.

[MCIR]

Okay. Thanks for the heads up

[christillman]

I fixed the second problem I found, which was in fn_workplan_item_owned_by_2 called by sp_add_workplan_item. I found a workaround to avoid the same out-of-memory message. This fix will be in 7.2.1.9.

[christillman]

I've learned that Azure always maintains the most recent version of SQL Server in Azure SQL Database. So the version we are using in Azure is equivalent to SQL 2022. I have set the compatibility level in GreenOliveDemo on Azure to be 160, also equivalent to SQL 2022, so this would be a good place to re-test. Let me know if you do try it out.

I also set the environment to "Serverless" so while it may take a minute or two to get "warmed up", you should get better performance than with the DTU setting (without needing to manually change it). It also costs a bit less when idle. So your usage would also be a good test of the Serverless environment to observe how much it will cost.

[christillman]

The Serverless setting was using money for resources just to do its own backups, much more than the DTU level of usage. I set it back to 5 DTU for now. Let me know if you need to do any testing.