?
Solved

Merge replication agent fails, don't understand why.

Posted on 2014-01-10
10
Medium Priority
?
250 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
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.

 
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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

770 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