Solved

Merge replication agent fails, don't understand why.

Posted on 2014-01-10
10
246 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

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!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 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