SQL Server 2019 Upgrade – Troubleshooting Stored Procedure Failures

sql serversql-server-2019upgrade

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.

Best Answer

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:

Turn off TSQL_SCALAR_UDF_INLINING for the database:

 USE YourDatabase;
 ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Add INLINE OFF to the problem function header. This will allow the benefits of scalar inlining for other functions.
Refactor the problem query as to avoid the error.

Related Solutions

Maximum Number of Local Variables in SQL Server SET Operation

Msg 8631, Level 17, State 1, Line xxx
Internal error: Server stack limit has been reached.
Please look for potentially deep nesting in your query, and try to simplify it.

This error occurs with long SET or SELECT variable assignment concatenation lists due to the way SQL Server parses and binds this type of statement - as a nested list of two-input concatenations.

For example, SET @V = @W + @X + @Y + @Z is bound into a tree of the form:

ScaOp_Arithmetic x_aopAdd
    ScaOp_Arithmetic x_aopAdd
        ScaOp_Arithmetic x_aopAdd
            ScaOp_Identifier @W 
            ScaOp_Identifier @X 
        ScaOp_Identifier @Y 
    ScaOp_Identifier @Z

Each concatenation element after the first two results in an extra level of nesting in this representation.

The amount of stack space available to SQL Server determines the ultimate limit to this nesting. When the limit is exceeded, an exception is raised internally, which eventually results in the error message shown above. An example process call stack when the error is thrown is shown below:

Repro

DECLARE @SQL varchar(max);

SET @SQL = '
    DECLARE @S integer, @A integer = 1; 
    SET @S = @A'; -- Change to SELECT if you like

SET @SQL += REPLICATE(CONVERT(varchar(max), ' + @A'), 3410) +';'; -- Change the number 3410

-- SET @S = @A + @A + @A...
EXECUTE (@SQL);

This is a fundamental limit due to the way multiple concatenations are handled internally. It affects SET and SELECT variable assignment statements equally.

The workaround is to limit the number of concatenations performed in a single statement. This will also typically be more efficient, since compiling deep query trees is resource-intensive.

SQL Server – Unable to Generate Scripts for Publications

DECLARE @distributor sysname

DECLARE @distributiondb sysname
DECLARE @distproc NVARCHAR (255)
DECLARE @publisher sysname

SELECT @publisher = @@SERVERNAME

EXEC sys.sp_MSrepl_getdistributorinfo @publisher = @publisher,
@rpcsrvname = @distributor OUTPUT,
@distribdb = @distributiondb OUTPUT

select @distributor, @distributiondb

SELECT @distproc = QUOTENAME (@distributor) + '. ' + QUOTENAME (@distributiondb) + '.dbo.sp_MSenumdistributionagentproperties'

select @distproc ---what do you get here?

---Line 537
EXEC @distproc @publisher = @publisher,
@publisher_db = @dbname,
@publication = @orig_publication,
@show_security = 1

The exec @distproc section is roughly at Line 537 of sp_MSrepl_helpsubscription. It's building the proc call and should be something like [SeverName].distribution.dbo.sp_MSenumdistributionagentproperties

From the error, it appears that @distproc variable is not getting set properly, or at least getting set to an empty string.

Why? That's hard to know without more information or being able to test on your system. Some unusual setup with the distributor, perhaps? But this will hopefully point you in the right direction so you can walk through it. (Run that code on the Database that is being published).

Best Answer

Related Solutions

Maximum Number of Local Variables in SQL Server SET Operation

Repro

SQL Server – Unable to Generate Scripts for Publications

Related Question