5

We just upgraded from SQL Server 2008 R2 to SQL Server 2019(Compability lvl 150).

We have two different stored procedures that started failing after the upgrade, with error messages like this:

Msg 8632, Level 17, State 2, Procedure BuildSelfSaleStats, Line 14 [Batch Start Line 4]
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Whats really strange is that this particular stored procedure doesnt take any arguments, and when we simply execute the body of the SQL code in SSMS, it works fine(!?).

What might cause some SQL code that works fine when executed in SSMS, to suddenly start failing when its wrapped in a stored procedure?

We managed to isolate the error to a date formatting function we had:

This works when run in SSMS:

select dbo.fn_formatdatetime(DATEADD(month, -6, GETDATE()), 'yyyy-mm')
output:
(No column name)
2019-12

But when called in testproc, it fails:

CREATE PROCEDURE testproc
AS
BEGIN
    SET NOCOUNT ON;
    select dbo.fn_formatdatetime(DATEADD(month, -6, GETDATE()), 'yyyy-mm')
END

exec testproc

Msg 8632, Level 17, State 2, Procedure testproc, Line 9 [Batch Start Line 11]
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
0
7

This looks to be a problem with UDF inlining. As @ScottHodgin suggested, the first step is to install the latest CU to see if the problem persists. If that doesn't resolve the problem, your options are:

  1. Turn off TSQL_SCALAR_UDF_INLINING for the database:

     USE YourDatabase;
     ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;
    
  2. Add INLINE OFF to the problem function header. This will allow the benefits of scalar inlining for other functions.

  3. Refactor the problem query as to avoid the error.

2
  • Yes that worked :) And it appears there are some fixes for UDF inlining specifically in CU4, which we havent installed yet: support.microsoft.com/en-us/help/4538581/…
    – btb
    Jun 18 '20 at 11:34
  • @btb if installing CU4 doesn't fix the problem, it would be of value to post the function definition here by editing your question, and open a user voice item about the bug. Jun 18 '20 at 13:59
-3

I just called sp_recompile for all functions and it worked ok.

I faced this same problem when i moved from SQL Server 2014 to 2019. After altering the database option "Compatibility Level" to 150 (SQL 2019) the errors started to come up.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.