Merge replication with NAV 5.0SP1 - problem

ottobe

Hi
We have developed a solution on Navision 3.70/ SQL2000 using Merge replication between a Master and 5 - 10 remote databases. This has been working perfect for 5 years.
We are now about upgrading to NAV 5.0/SQL2008 and have encountered a replication problem.
The reason seems to be that in NAV 5.0 the SIFT tables from 3.70 now are SIFT-views in 5.0. If SIFT views are not included in the replicated articles the merge agent fails. In 3.70 it was not necessary to include the SIFT tables.
If SIFT views are included in articles the replication works, but then we cannot open the replicated tables from the designer. (The tables are however accessible from forms..)
The error says that SIFT tables associated with the table we try to open are invalid, so the SIFT tables do not like to be replicated.

Do anybody have a solution or knows a workaround for this?

If necessary we could run SQL2005, but I do not think that is the problem. The best way to handle this would be to run the replication without involving SIFT, as in 3.70

Fisherman

Let's make sure what you're talking about first.

Are you going to NAV 5.0, or NAV 5.0.1? NAV 5.0 SP1 did away with the underlying SQL SIFT Tables in favor in Indexed views, but these shouldn't need to be replicated. They're views. If the data underlying the view is replicated, then the view should always represent that data. At most, you should have to copy the views from one server to another.

ottobe

Luc,
We are running 5.0 SP1. (SQL Client version no: 5.0.26084.0)
Yes, I would absolutely prefer not to replicate the indexed views. But then the subscriber (not merge agent as I wrote above) fails when initializing with error below, saying in short:

Message:
Cannot DROP TABLE 'dbo.Datacompany$Item Ledger Entry' because it is being referenced by object 'Datacompany$Item Ledger Entry$VSIFT$1'.

It does not allow to drop tables tables referring to SIFT views. While typing this I came to think of:
This probably only happens at the subscriber when Initializing schema. If I manually delete (if allowed) SIFT views before I start initializing this might work. What do you think?

regards,
otto

Fisherman

I'm lost.

Why do you want to drop Item Ledger Entry? Is it not a necessary table in your subscriber?

If that's the case, then I think you need to reinitialize your publication with only those articles that you want replicated. If ILE is not one of those tables, then why put it in the publication? You can have multiple publications on the same database if it is valid for replication in other contexts.

I think the ticket is to remove the ILE table and the corresponding SIFT views from the publication at the publisher and reinitialize your subscriptions. Dropping the table on the subscriber doesn't remove it from either the publication or the subscription, and I don't know how SQL would react. It may continue attempting to replicate transactions from the source even though the destination table is no longer there.

ottobe

Fisherman,

ILE have to be replicated.
As I understand it, tables are dropped when you initialize a new subscriber. (Applying schema from the publisher). And it do not want to drop ILE as this is referenced. So for that's sake I might drop all replicated tables and SIFT views on the subscriber, before starting initializing. But I do not know if I am allowed to do that and still get the subscriber initialized. I will test.

regards,
otto