Solved

Merge replication agent fails, don't understand why.

Posted on 2014-01-10
10
231 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now