i have upgraded my db to 2019 , but i am forced to keep the compitablity to 12, is there any over around for this "An expression services limit"?

Question

hi,
I have old database and many quries which i can not change right now , so i am forced to keep my compitablilty to 12.
is there any way around for below problem. so that i can keep compitablity to 19 with old stored proceidure.

"Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.,ReportSyncQueueId"

yours sincerley

Answer 1

Worked for me Thank you

Answer 2

Hi @rajesh yadav ,

Let's start our analysis with your error.
This issue occurs because SQL Server limits the number of identifiers and constants that can be contained in a single expression of a query. This limit is 65,535.
You can try the following:
Split the select query to multiple select queries. Store the result in temp tables and then combine the results.

Best regards,
Seeya

---

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.

Answer 3

The mention limit are 65,535 expressions/identifier; do you really have queries/view with such an amount? Then you should really re-thing database design and re-write queries.

See https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-8632-database-engine-error?view=sql-server-ver15

I doupt a bit that this is really related to the compatibility level of the database?

Answer 4

Make sure you have the current CU installed and test again.

https://support.microsoft.com/en-us/topic/kb4518398-sql-server-2019-build-versions-782ed548-1cd8-b5c3-a566-8b4f9e20293a

Answer 5

step 1) there is function which accepts an id and returns comma separated text. the function has order by and some concatenation. to get comma separated text.

Rather than using your own home-brew, you can use the string_agg function. (even in compat level 120). Here is an example:

SELECT o.name, string_agg(convert(nvarchar(MAX), c.name), ' - ') WITHIN GROUP (ORDER BY c.column_id)
FROM  sys.objects o
JOIN  sys.columns c ON c.object_id = c.object_id
GROUP BY o.name
ORDER BY o.name