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:

    RETURN (
        SELECT 1

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.


1 Answer 1


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.