Include UDFs in replication publication on SQL Server 2012 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.
This issue was posted on Connect:


  We can't snapshot any User Defined functions to SQL 2016 version from
  existing SQL 2014 servers. They work fine within 2016 versions and
  from SQL 2016 to SQL 2014\2012.


MS's answer is:


  This issue is fixed in the following cumulative update for SQL Server:
  
  Cumulative Update 4 for SQL Server 2016.
  
  It contains one fix: FIX: Snapshot Agent fails when you publish UDF’s
  to SQL Server 2016 Distributor in Transactional Replication
  https://support.microsoft.com/en-us/help/3197883/fix-snapshot-agent-fails-when-you-publish-udf-s-to-sql-server-2016-distributor-in-transactional-replication