Sql-server – Subscription Initialization throws Error “The option ”INLINE=ON“ is not valid for this function” which is incorrect

sql serversql-server-2019transactional-replication

Scenario
We are upgrading our SQL estate to SQL Server 2019 and have setup what will be the production environment.

From:
SQL2008R2 Standard/Windows Server 2008R2 – Primary Server/Secondary Server (Windows Fail-over Cluster) & Reports Server (Transactional Replication).

To:
SQL2019 Standard/Windows Server 2019 – Primary Server/Secondary Server (Always-On) & Reports Server (Transactional Replication)

Backups from 2008R2 were restored on 2019 and compatibility level set to latest. Always-On setup has gone relatively smooth and initial testing against current application has shown no issues with compatibility.

The issue is purely with setting up Transactional Replication. There are 2 databases that require replication, one publication each with the primary as the publisher and distributor.

The Issue
During the initialization of the subscription for each DB on the reports server it runs fine until it gets to the point of creating functions and produces the errors below.

1st Database:

Message: The option "INLINE=ON" is not valid for this function. Check the documentation for the constructs supported with INLINE option in a function.
Command Text: CREATE FUNCTION [dbo].[f_clienttels – Mirror Copy ce2d3663eb494f3589bd5000dad1bf1f](@ClientID [int])
RETURNS varchar WITH INLINE = ON, EXECUTE AS CALLER
AS
BEGIN ……

2nd Database:

Message: An invalid option was specified for the statement "CREATE/ALTER FUNCTION".
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near ')'.
Command Text: CREATE FUNCTION [dbo].[GetGroupAndDescendantGroupsSelective – Mirror Copy 46f329d5eed444428f45b052f07c7ea8](@GroupId [int])
RETURNS TABLE WITH INLINE = ON
AS
RETURN (
WITH GroupsCTE AS ( ……..

They are both different errors but i believe both are to do with the "INLINE=ON" option, this option is not present in either of those functions, none of our functions use the inline option explicitly, if you remove those articles from the subscription it just gives the same error on the next function (CTE error if the function starts with a CTE and the INLINE=ON error if it does not).

So it appears replication is inserting "WITH INLINE = ON" to functions prior to replicating and then erroring on the addition it has made.

I have patched all instances to the latest CU4 update 15.0.4033.1, tested those functions on both servers (which work), validated all functions/procs within the database and all are fine. In the current 2008R2 environment I had to recreate the publication a couple months back and didn't get these errors. As a work around for now I'm manually creating functions at the subscriber and removing all function articles from the publication.

Any help with a resolution to this would be greatly appreciated, the only reference to this error (from 1st DB) is mentioning that it's undocumented (link below) and i could not find any other forum posts mentioning it.

16203 – The option “INLINE=ON” is not valid for this function. Check
the documentation for the constructs supported with INLINE option in a
function.

From: Brent Ozar – What’s New in SQL Server 2019’s sys.messages: More Unannounced Features

We don't have an active support contract with Microsoft but are trying to reach them via the supplier that provided the licences so I will give an update here if they get back to us.

I can provide more information if needed.

Best Answer

Refer this article The only solution worked for me was drop and recreate the UDF after upgrading to SQL 2019.

The inline_Type column from Sys.sql_modules will be 1 after your upgrade to SQL 2019. After you drop and recreate the UDF, the Inline_type will be 0 and the replication initialization works fine.