SQL: Error, Expression services limit reached?
Solution 1
This issue occurs because SQL Server limits the number of identifiers and constants that can be contained in a single expression of a query. The limit is 65,535. The test for the number of identifiers and constants is performed after SQL Server expands all referenced identifiers and constants. In SQL Server 2005 and above, queries are internally normalized and simplified. And that includes *(asterisk), computed columns etc.
In order to work around this issue, rewrite your query. Reference fewer identifiers and constants in the largest expression in the query. You must make sure that the number of identifiers and constants in each expression of the query does not exceed the limit. To do this, you may have to break down a query into more than one single query. Then, create a temporary intermediate result.
Solution 2
The same issue happens to me when we tried to change the Database Compatibility Level to 150. It is not an issue when it is 140 or lower.
Solution 3
I just had this problem and fixed it by removing the UNIQUE index on my table. For some reason, that seems to trigger this error, although it cannot figure out why.
By the way, the same query does work with several other indexes.
Solution 4
What worked for me was replacing several COALESCE
statements with ISNULL
whenever was possible
Comments
-
Standage almost 2 years
"Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them."
Has anyone seen this before and found a good workaround?
I managed to get around this issue by splitting my SQL query into two parts essentially and writing the first SQL select query to a temp table and the second part, a new SQL select statement selects from the temporary table and uses alot of CROSS APPLY operator to Calculate cascading computed columns.
This is an example of how the second part looks but I'm using alot more Cross Applys to produce new columns which are calculations:
Select * from #tempTable cross apply ( select HmmLowestSalePrice = round(((OurSellingPrice + 1.5) / 0.95) - (CompetitorsLowestSalePrice) + 0.08, 2) ) as HmmLowestSalePrice cross apply ( select checkLowestSP = case when adjust = 'No Room' then 'No Room' when OrginalTestSalePrice >= CompetitorsLowestSalePrice then 'Minus' when OrginalTeslSalePrice < CompetitorsLowestSalePrice then 'Ok' end ) as checkLowestSP cross apply ( select AdjustFinalNewTestSP = case when FinalNewTestShipping < 0 Then NewTestSalePrice - (FinalNewTestShipping) when FinalNewTestShipping >= 0 Then NewTestSalePrice end ) as AdjustFinalNewTestSP cross apply ( select CheckFinalSalePriceWithWP = case when round(NewAdminSalePrice, 2) >= round(wholePrice, 2) then 'Ok' when round(NewAdminSalePrice, 2) < round(wholePrice, 2) then 'Check' end ) as CheckFinalPriceWithWP DROP TABLE #tempTable
My goal to to put this into a sql report and it work fine if there is 1 user only as the
#tempTable
will get created and dropped in the same execution and the results are displayed in the report correctly. But in the future if there are concurrent users I'm concerned that they will be writing to the same#tempTable
which will affect the results?I've looked at putting this into stored procedures but still get the error message above.
-
Standage over 12 yearsDid you just copy and past this from the mirosoft site? I've read the same thing and I did split the results into a temp table = intermediate resuly (and got it working) but concerned that if I had a few users this wouldn't work
-
TheBoyan over 12 years@Paul - I don't think that having multiple users will cause any problems. Check out this post: stackoverflow.com/questions/4725812/…