debugsqlMinor
Subscription Initialization throws Error "The option "INLINE=ON" is not valid for this function" which is incorrect
Viewed 0 times
thiserrorsubscriptiontheincorrectwhichfunctionthrowsoptionvalid
Problem
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
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
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
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
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
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
Solution
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.
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.
Context
StackExchange Database Administrators Q#264596, answer score: 4
Revisions (0)
No revisions yet.