Solved

Merge replication agent fails, don't understand why.

Posted on 2014-01-10
10
244 Views
Last Modified: 2014-01-22
Hi experts,

I obviously don't understand how merge replication works.  I have created a publication and a subscriber. The subscriber database does not have some views that the publisher does.

I imagined that the first thing the replication would do is take a copy of the publication as a snapshot and put that on the subscriber database, but when I initialize and start the merge agent it fails, these are the errors shown...it's complaining that one of the views isn't present on the subscriber, but I thought it would create it.

Thanks in advance for your help.  No rush, it's Friday afternoon here and I have just about had enough in the office!

Error messages:
•      The schema script 'VW_ST_RESULT_R56b7b40_241.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 'VW_ST_RESULT_WITH_SAMPLE'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208
0
Comment
Question by:colinspurs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39772785
Did you include those articles in the Merge Replication?
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39773187
Yes, I included everything.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39773453
Then I am afraid I have no idea.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 3

Author Comment

by:colinspurs
ID: 39773501
Well, OK, thanks for getting back.  Then my understanding is correct - it shouldn't have failed?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39773864
Correct.

But here is a thought are you sure those VIEWs compile?  Perhaps the table structure has changed.  If you have something like Red-Gate's SQL Prompt this is easy to find out, as you can use there "Find Invalid Objects" function to determine that.
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39776166
Now I am back in the office I have re-checked everything.  All objects are included, the views compile, the processing_order seems correct...

In true IT style I am doing the same thing again expecting a different result!
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39779413
OK...I got the same error, unsurprisingly.

a) I reduced the publication to just those articles necessary for the view mentioned in the error messages, and "successfully" ran the snapshot and merge agents into a new empty database (more on this later).

b) The new smaller publication also worked OK with the original database as the subscriber.

c) The original large publication failed pushing a subscription to a new empty database.

Going back to a), when I looked at the view at the subscriber database, it had had a guid column added, which invalidates the view. So what is the recommended best practice for replicating views?  Just replicate tables and build views at the subscriber?

Thanks,

    Colin
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 39781028
I suspect you will find that all tables have a new column added that has a uniqueidentifier data type.

So what is the recommended best practice for replicating views?  Just replicate tables and build views at the subscriber?
Typically you would replicate all VIEWs, that way if they change they will be copied over.  But perhaps there is something about this VIEW that does not allow it to be replicated.
0
 
LVL 3

Author Comment

by:colinspurs
ID: 39784920
Yes, I understand.  But the view went in the cut-down version.

I'll continue to investigate.

Cheers.
0
 
LVL 3

Author Closing Comment

by:colinspurs
ID: 39800209
The processing order was incorrect, as I eventually discovered.  Thanks for your help.  

The process as a whole is still not running smoothly, but I have got past this point!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question