SQL Server 2012 – Including UDFs in Replication Publication

When trying to replicate UDFs via snapshot replication on a working database I got an error. So I created a simpler database with only a single UDF to test that I could replicate UDFs, because I've been reading that there might be bugs that depend on how you write the SQL I created a simpler test.

I got the same error with this function, so should I assume that I can't replicate UDFs? I mean, I'm given an option to do exactly this. But even the simplest example fails. Am I doing anything wrong? The error is from the snapshot agent on the publisher's side.

This is the function:

CREATE FUNCTION test1 
(
)
RETURNS INT
AS
BEGIN
    RETURN (
        SELECT 1
    )
END
GO

The stacktrace is:

Source: Microsoft.SqlServer.Smo
Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[], Boolean)
Message: Script failed for UserDefinedFunction 'dbo.test1'. 
Stack:    at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
   at Microsoft.SqlServer.Management.Smo.Scripter.EnumScriptWithList(SqlSmoObject[] objects)
   at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.CreateMirrorCopy(String mirrorCopyName)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.FetchBaseDependenciesWithMirrorCopyTransaction(SqlConnection connection)
   at Microsoft.SqlServer.Replication.RetryableSqlServerTransactionManager.ExecuteTransaction(Boolean bLeaveTransactionOpen)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.FetchBaseDependenciesWithMirrorCopy()
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.FunctionDependencyNode.get_BaseDependencies()
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.BaseDependencyNode.GetEnumerator()
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.DepthFirstSearchVisit(IDependencyNode dependencyNode)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.DepthFirstSearchVisit(IDependencyNode dependencyNode)
   at Microsoft.SqlServer.Replication.Snapshot.ArticleDependenciesAnalyzer.PerformArticleDependenciesAnalysis()
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoReplicationDependenciesAnalysis(Scripter scripter, SqlSmoObject[] smoObjectArray, IDictionary urnKeyArticleLookupTable)
   at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
   at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
   at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
   at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0

I'm trying to publish on SQL Server 2012 to a subscriber that is SQL Server 2016. The Distributor is also SQL Server 2016.

I was able to successfully run a snapshot of this UDF on a publication hosted on SQL Server 2016. So I think that it's actually SQL Server 2012 that is not allowing this, assuming that the snapshot agent runs on the publisher.

I've tried with and without the AS keyword. The value of @type in sp_addarticle is 128.