Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - Internal error: An expression services limit has been reached

Tags:

sql

sql-server

I have a SQL Server stored procedure with nearly 300 variables within it.

The variables were created as follows:

CASE WHEN Grp1 = 'SALES' AND grp_mnth = MONTH_0 THEN SUM(Col)  OVER(PARTITION BY grp_loc,COMM) ELSE 0 END AS "SALES_1"

As this has data issue all the variables were replaced like:

SUM(CASE WHEN Grp1 = 'SALES' AND grp_mnth = MONTH_0 THEN Col ELSE 0 END)  OVER(PARTITION BY grp_loc,COMM) AS "SALES_1"

The data issue has been solved by the above statement, but the procedure throws the below error when we add all the 300 variables, if only about 100 is added, the procedure runs fine.

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

As per the answer posted I have tried to split the query to multiple select queries and the error has been resolved, but while combining the result the data has not been fetched correctly.

BEGIN

CREATE TABLE #TEMPTAB1
(
TYPE_1 char(15),
NUMBER_1 char(7),
STATUS_1 char(1),
...
)
CREATE TABLE #TEMPTAB2
(
TYPE_2 char(15),
NUMBER_2 char(7),
STATUS_2 char(1),
...
)
CREATE TABLE #TEMPTAB3
(
TYPE_3 char(15),
NUMBER_3 char(7),
STATUS_3 char(1),
...
)

SELECT * FROM 
    #TEMPTAB1 T1 
     INNER JOIN 
    #TEMPTAB2 T2 ON T1.TYPE_1=T2.TYPE_2 AND T1.NUMBER_1 = T2.NUMBER_2 AND T1.STATUS_1 = T2.STATUS_2
     INNER JOIN 
    #TEMPTAB3 T3 ON T1.TYPE_1=T3.TYPE_3 AND T1.NUMBER_1 = T3.NUMBER_3 AND T1.STATUS_1 = T3.STATUS_3

END

Can anyone please suggest a way to correct the joins in the above code.

like image 412
user Avatar asked Dec 20 '22 01:12

user


2 Answers

Please re-write your query!

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.

one approach might be :

You can split the select query to multiple select queries.Store the result in temp tables and combine the results at the end.

more info

like image 157
Biswabid Avatar answered Dec 29 '22 00:12

Biswabid


Fo me it was the SQL Server version. I had no issue with running the same query against SQL Server 2017 on production but was facing the issue with SQL Server 2018 on staging. I downgraded my SQL Server version back to 2017 from 2018 on production and the issue was resolved. This might be an issue with SQL Server 2018 as of now

like image 38
Raghav Avatar answered Dec 29 '22 00:12

Raghav