Sql-server – Why merge replication wants to replicate view before replicating related table

merge-replicationsql serversql-server-2016

We have a publication and a subscriber running on 2 SQL 2016 servers. Now the error that we are receiving during subscription initialization is:

Error messages:

The schema script 'vw_BlackList_961.sch' could not be propagated to
the subscriber. (Source: MSSQL_REPL, Error number:
MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001
Unable to replicate a view or function because the referenced objects
or columns are not present on the Subscriber. (Source: MSSQL_REPL,
Error number: MSSQL_REPL20164) Get help: http://help/MSSQL_REPL20164
Invalid object name 'StaffBlacklistedSites'. (Source: MSSQLServer,
Error number: 208) Get help: http://help/208

Where is quite obvious that SQL server wants first to push VIEW instead first to push TABLE. How that is even possible? How to remedy this issue?

Thanks,
Dejan

Best Answer

I don't know if this apply to your issue, but according MS-DOCS about this error:

Error number: MSSQL_REPL-2147201001

Maybe it is not about trying to replicate a VIEW before the TABLE, but another reasons.

Quoting from docs: (Bold is mine)

Explanation

COM object initialization failed for an XML Subscriber. Some reasons why merge replication did not apply schema changes to the Subscriber include the following:

  • A failure to create a directory to write the temporary snapshot files.

  • A failure to enumerate schema articles.

  • For SQL Server Compact Subscribers, a failure to reinitialize the subscription.

  • If the object is message based, a failure to write to the message file.

Perhaps for some reason it can't enumerate the schema properly, and then it is trying to replicate the view before the table.

You can have a look at errors log in this way:

User Action

Run SQL Server Profiler and examine replmerg.log for failures. If you are using Web Synchronization, elevate the severity of the websync log, rerun the scenario, and check for errors in the websync.log file.

Related Question