Join filters an generation ids

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JorgenD - 17 Jan 2006 17:16 GMT

Hi,

In the flowering simplified example there is three tables.
ProjectAssignmentTable, ProjectTable, and ProjectTransactionsTable.
The idea is that an employee is assigned a project in the in the assignment
table and he or she uses merge replication to replicate the project and
transactions to his or her client.
I have set up the appropriate join filters so that the client’s ProjectTable
is filtered by the ProjectAssignmentTable and the ProjectTransactionsTable is
filtered by the result of the first join.
This all works fine as long as the employee is assigned the project form
project start.
When a project (let's say project no 100) has been assigned to other
employees for a while and a new employee is assigned later he or she only
gets transactions replicated from the day he or she is assigned the project.

I believe that it has do with the generation ID on the
ProjectTransactionsTable on the client that has replicated transactions from
day 1 but has skiped transactions for project no. 100 since it wasn't
assigned to him/her. Now he or she want these transactions. How do I set this
up?

Regards JorgenD

Reply to this Message

Hilary Cotter - 17 Jan 2006 21:17 GMT

I think you are possibly filtering on the wrong criteria.

Could you possible post the schema for the ProjectAssignmentTable, the
ProjectTable, and the ProjectTransactionsTable so we could verify this?

Note that the generationID (actually the generation or Gen_Cur value)
reflects how many times a merge agent has sync'd with that table and there
have been changes to merge. The first thing a merge agent does when it runs
is increments the generation on sysmergearticles so subsequent changes
occurring during the merge are not merged until the next sync. It only does
this if there are changes to sync. GenerationIDs (gen_cur) are per article
and per publication specific.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi,
>
[quoted text clipped - 25 lines]
>
> Regards JorgenD

Reply to this Message

JorgenD - 18 Jan 2006 08:16 GMT

Thanks for your reply and your highly appreciated effort to this group.

Yes I will try to post the scheme for the tables if necessary in my next post.
But I think the problem lies in the generationIDs, so let me try to explain
further.

Lets look at the flowering scenario:

John is assigned project no. 1
Jeff is assigned project no. 2
They both do a sync and gets the project and transactions replicated to
their clients.
They both make transactions and do several syncs.

Now Jeff has synchronized several times to the ProjectTransactionsTable but
the join filters has prevented him to get transactions from project no. 1
(since it is not assigned to him)
For some reason Jeff is now also assigned project no. 1 and the join filters
allows him to get all transactions for project no. 1. But now the merge agent
is not merging the old transactions made by John, only new ones from the day
Jeff is assigned project no 1.

I think that the since the Jeffs merge agent has already been merging with
the ProjectTransactionTable it has a higher generationIDs than the old
transactions made by John, so no matter what the join filters do, it’s not
possible to get these “old” transactions.
Am I totally wrong here? If so how can I set it up so I get the wanted
functionality?

Regards JorgenD

> I think you are possibly filtering on the wrong criteria.
>
[quoted text clipped - 38 lines]
> >
> > Regards JorgenD

Reply to this Message

Hilary Cotter - 19 Jan 2006 02:53 GMT

Check the conflict viewer to see if they got stuck there.

It would be really really helpful if you could post the schema of the three
tables along with their DRI.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Thanks for your reply and your highly appreciated effort to this group.
>
[quoted text clipped - 83 lines]
>> >
>> > Regards JorgenD

Reply to this Message

JorgenD - 19 Jan 2006 10:55 GMT

No conflicts and it all works fine except that the employees only get the
project transactions that are added/changed after the day that they get
assigned to the project.

As I mentioned in my first post, this was a simplified example, but here is
the real schema.
In addition I should probably mention that the subscribers are anonymous SQL
CE clients.

WkOrderEmpl: (ProjectAssignmentTable)
Here the employee is associated with a work order.

Workorders:
This is the work order which has a reference to a project (ProjTable)

ProjTable: (ProjectTable)
This is the projects which has a reference to a customer (DebTable)

ProjTrans: (ProjectTransactionsTable)
The transactions belonging to a project.

CREATE TABLE [dbo].[WkOrderEmpl] (
    [WorkOrder] [varchar] (10) NOT NULL ,
    [Employee] [varchar] (10) NOT NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
    [Finished] [tinyint] NULL ,
    [NoItems] [tinyint] NULL ,
    [Updated] [tinyint] NULL
)

CREATE TABLE [dbo].[WorkOrders] (
    [Proj] [varchar] (10) NOT NULL ,
    [Workorder] [varchar] (10) NOT NULL ,
    [Section] [varchar] (10) NULL ,
    [Activity] [varchar] (10) NULL ,
    [Created] [smalldatetime] NULL ,
    [PromisedDateStart] [smalldatetime] NULL ,
    [PromisedTimeStart] [varchar] (5) NULL ,
    [ReceivedBy] [varchar] (10) NULL ,
    [OrderedBy] [varchar] (20) NULL ,
    [Requisition] [varchar] (10) NULL ,
    [AltPhone] [varchar] (10) NULL ,
    [Finished] [tinyint] NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
    [Signature] [varbinary] (1000) NULL ,
    [Updated] [tinyint] NULL ,
    [EmailSent] [tinyint] NULL
)

CREATE TABLE [dbo].[ProjTable] (
    [Proj] [varchar] (10) NOT NULL ,
    [Name] [varchar] (50) NULL ,
    [Created] [smalldatetime] NULL ,
    [DebtorAccount] [varchar] (10) NULL ,
    [CustomerRef] [varchar] (30) NULL ,
    [Delname] [varchar] (40) NULL ,
    [Deladdr1] [varchar] (40) NULL ,
    [Deladdr2] [varchar] (40) NULL ,
    [Deladdr3] [varchar] (40) NULL ,
    [Employee] [varchar] (10) NULL ,
    [Department] [varchar] (10) NULL ,
    [Centre] [varchar] (10) NULL ,
    [Purpose] [varchar] (10) NULL ,
    [FixedPrice] [tinyint] NULL ,
    [Country] [varchar] (30) NULL ,
    [Zip] [varchar] (10) NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
)

CREATE TABLE [dbo].[ProjTrans] (
    [Proj] [varchar] (10) NOT NULL ,
    [Section] [varchar] (10) NULL ,
    [Activity] [varchar] (10) NULL ,
    [Number_] [varchar] (20) NULL ,
    [Ctype] [varchar] (10) NULL ,
    [Date_] [smalldatetime] NULL ,
    [Txt] [varchar] (40) NULL ,
    [Qty] [decimal](28, 2) NULL ,
    [SalesPrice] [decimal](28, 2) NULL ,
    [Department] [varchar] (10) NULL ,
    [Centre] [varchar] (10) NULL ,
    [Purpose] [varchar] (10) NULL ,
    [Type] [tinyint] NULL ,
    [Employee] [varchar] (10) NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
    [Note] [varchar] (1000) NULL ,
    [Purchase] [tinyint] NULL ,
    [LastEditDate] [smalldatetime] NULL ,
    [LastEditUser] [varchar] (10) NULL ,
    [LastEditTime] [int] NULL ,
    [MyGuid] [uniqueidentifier] NULL
)

CREATE TABLE [dbo].[DebTable] (
    [AccountNumber] [varchar] (10) NOT NULL ,
    [Name] [varchar] (40) NULL ,
    [Address1] [varchar] (40) NULL ,
    [Address2] [varchar] (40) NULL ,
    [Address3] [varchar] (40) NULL ,
    [Attention] [varchar] (30) NULL ,
    [Phone] [varchar] (20) NULL ,
    [Fax] [varchar] (20) NULL ,
    [Blocked] [tinyint] NULL ,
    [Department] [varchar] (10) NULL ,
    [Centre] [varchar] (10) NULL ,
    [Purpose] [varchar] (10) NULL ,
    [Country] [varchar] (30) NULL ,
    [Zip] [varchar] (10) NULL ,
    [Email] [varchar] (100) NULL ,
    [Url] [varchar] (100) NULL ,
    [SearchName] [varchar] (40) NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
)

> Check the conflict viewer to see if they got stuck there.
>
[quoted text clipped - 88 lines]
> >> >
> >> > Regards JorgenD

Reply to this Message

Michael Hotek - 23 Jan 2006 12:52 GMT

It has nothing to do with filtering criteria. The merge engine sends a
change once and only once. Once that change has been applied to the
subscriber it won't be sent again. If you have filtering criteria and all
of a sudden change the filtering characteristics to cause a subset of data
to now move from one filter to another, then any data that no longer obeys
the filter criteria will be removed from the databases it no longer matches
on WHEN it is changed by your application. When rows migrate into a new
filter, you have in effect invalidated by data subset on all subscribers
using that slice of the data and then need to be reinitialized to ensure
that they have the correct snapshot of data.

Signature

Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.

>I think you are possibly filtering on the wrong criteria.
>
[quoted text clipped - 38 lines]
>>
>> Regards JorgenD

Reply to this Message

JorgenD - 23 Jan 2006 14:00 GMT

Hi Michael

Thank you very mouch for your reply.

I see your point but I am still a bit confused, you see I'm not changing the
filters only the data which the filters are based upon.

Lets make a simple example:

Order Table:
OrderNo, OrderName, CustomerAccount,....
10, "Red Bicycle", 100001
20, "Green Bicycle", 100002

OrderLineTable:
OrderNo, ItemNumber, ItemName, Qty, Price
10, 201, "Red Bicycle", 1, $800
10, 302, "Tire Pump", 1, $50
20, 323, "Green Bicycle, 1, $900

Now we have a table that specifies the salesperson and order relations.

EmployeeOrderTable:
Employee, OrderNo
Jeff, 10
john, 20

A join filter makes sure that when Jeff is synchronizing his getting orderno
10 and its lines, and John is getting orderno 20 and its lines. And they can
now visit the customers and add lines to thees orders.
Lets say Jeff gets sick anf John is taking over for him.
We just add the record John, 10 to the EmployeeOrderTable. But John is not
getting orderno. 10 or its lines?

Is this the way is is supposed to be?

Hope you see my point.

> It has nothing to do with filtering criteria. The merge engine sends a
> change once and only once. Once that change has been applied to the
[quoted text clipped - 49 lines]
> >>
> >> Regards JorgenD

Reply to this Message

Hilary Cotter - 23 Jan 2006 15:03 GMT

Jorgen, could you repost the schema showing all pk and fk relationships?

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi Michael
>
[quoted text clipped - 102 lines]
>> >>
>> >> Regards JorgenD

Reply to this Message

JorgenD - 24 Jan 2006 09:38 GMT

Hi.

Here are the schema with PK. I'm not using FK's because that business ligic
is handled in the application.
I have also included the filters from the publication.

By the way Hilary, when is your book on merge replication getting published?

Two subsetfilters:
On atricle WkOrderEmpl :
@subset_filterclause = N'(Employee = SUSER_SNAME()) AND (Finished <> 1)'

On article ProjTrans(I know a bit odd):
@subset_filterclause = N'(number_ = suser_sname() and date_ >
dateadd(month,-1,getdate())) or exists(select proj from wkorderempl where
employee=suser_sname() and finished=0 and proj=projtrans.proj')'

And the join filters:
exec sp_addmergefilter @publication = N'PDAW_Pub', @article = N'WorkOrders',
@filtername = N'WorkOrders_WkOrderEmpl', @join_articlename = N'WkOrderEmpl',
@join_filterclause = N'(WorkOrders.Proj = WkOrderEmpl.Proj) AND
(WorkOrders.Workorder = WkOrderEmpl.Workorder)', @join_unique_key = 0

exec sp_addmergefilter @publication = N'PDAW_Pub', @article =
N'WorkOrderNotes', @filtername = N'WorkOrderNotes_WorkOrders',
@join_articlename = N'WorkOrders', @join_filterclause =
N'(WorkOrderNotes.Proj=WorkOrders.Proj) AND
(WorkOrderNotes.Workorder=WorkOrders.Workorder)', @join_unique_key = 0

exec sp_addmergefilter @publication = N'PDAW_Pub', @article = N'ProjTable',
@filtername = N'ProjTable_WorkOrders', @join_articlename = N'WorkOrders',
@join_filterclause = N'(ProjTable.Proj = Workorders.Proj)', @join_unique_key
= 0

Schema and PK's:

CREATE TABLE [dbo].[DebTable] (
    [AccountNumber] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Name] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Address1] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Address2] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Address3] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Attention] [varchar] (30) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Phone] [varchar] (20) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Fax] [varchar] (20) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Blocked] [tinyint] NULL ,
    [Department] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Centre] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Purpose] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Country] [varchar] (30) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Zip] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Email] [varchar] (100) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Url] [varchar] (100) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [SearchName] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ProjTable] (
    [Proj] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
    [Name] [varchar] (50) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Created] [smalldatetime] NULL ,
    [DebtorAccount] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [CustomerRef] [varchar] (30) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Delname] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Deladdr1] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Deladdr2] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Deladdr3] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Employee] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Department] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Centre] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Purpose] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [FixedPrice] [tinyint] NULL ,
    [Country] [varchar] (30) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Zip] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ProjTrans] (
    [Proj] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Section] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Activity] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Number_] [varchar] (20) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Ctype] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Date_] [smalldatetime] NULL ,
    [Txt] [varchar] (40) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Qty] [decimal](28, 2) NULL ,
    [SalesPrice] [decimal](28, 2) NULL ,
    [Department] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Centre] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Purpose] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Type] [tinyint] NULL ,
    [Employee] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
    [Note] [varchar] (1000) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Purchase] [tinyint] NULL ,
    [LastEditDate] [smalldatetime] NULL ,
    [LastEditUser] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [LastEditTime] [int] NULL ,
    [MyGuid] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WkOrderEmpl] (
    [Proj] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
    [WorkOrder] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
    [Employee] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
    [Finished] [tinyint] NULL ,
    [NoItems] [tinyint] NULL ,
    [Updated] [tinyint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkOrderNotes] (
    [Proj] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
    [Workorder] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
    [LineNumber] [decimal](28, 12) NOT NULL ,
    [Txt] [varchar] (254) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
    [Owner] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[WorkOrders] (
    [Created] [smalldatetime] NULL ,
    [Proj] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
    [Section] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Activity] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [PromisedDateStart] [smalldatetime] NULL ,
    [PromisedTimeStart] [varchar] (5) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [ReceivedBy] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [OrderedBy] [varchar] (20) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Requisition] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [AltPhone] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NULL ,
    [Workorder] [varchar] (10) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
    [Finished] [tinyint] NULL ,
    [RecID] [int] NULL ,
    [FileID] [int] NULL ,
    [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
    [Signature] [varbinary] (1000) NULL ,
    [Updated] [tinyint] NULL ,
    [EmailSent] [tinyint] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[DebTable] ADD
    CONSTRAINT [DF__DebTable__rowgui__01342732] DEFAULT (newid()) FOR [rowguid],
    CONSTRAINT [IX_AccountNumber] UNIQUE NONCLUSTERED
    (
        [AccountNumber]
    ) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [index_4195065] ON [dbo].[DebTable]([rowguid]) ON
[PRIMARY]
GO

CREATE INDEX [IX_Name] ON [dbo].[DebTable]([Name]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ProjTable] ADD
    CONSTRAINT [DF__ProjTable__rowgu__534D60F1] DEFAULT (newid()) FOR [rowguid]
GO

CREATE UNIQUE INDEX [index_2025058250] ON [dbo].[ProjTable]([rowguid]) ON
[PRIMARY]
GO

CREATE UNIQUE INDEX [IX_Proj] ON [dbo].[ProjTable]([Proj]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ProjTrans] ADD
    CONSTRAINT [DF__ProjTrans__rowgu__4D94879B] DEFAULT (newid()) FOR [rowguid]
GO

CREATE UNIQUE INDEX [index_2041058307] ON [dbo].[ProjTrans]([rowguid]) ON
[PRIMARY]
GO

CREATE INDEX [IX_ProjTrans] ON [dbo].[ProjTrans]([Proj]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WkOrderEmpl] ADD
    CONSTRAINT [DF__WkOrderEm__rowgu__3C69FB99] DEFAULT (newid()) FOR [rowguid]
GO

CREATE UNIQUE INDEX [index_2009058193] ON [dbo].[WkOrderEmpl]([rowguid])
ON [PRIMARY]
GO

CREATE INDEX [IX_ProjWo] ON [dbo].[WkOrderEmpl]([Proj], [WorkOrder]) ON
[PRIMARY]
GO

CREATE UNIQUE INDEX [IX_EmplProjWo] ON [dbo].[WkOrderEmpl]([Employee],
[Proj], [WorkOrder]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkOrderNotes] ADD
    CONSTRAINT [DF__WorkOrder__rowgu__36B12243] DEFAULT (newid()) FOR [rowguid]
GO

CREATE UNIQUE INDEX [index_1993058136] ON
[dbo].[WorkOrderNotes]([rowguid]) ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_ProjWoLine] ON [dbo].[WorkOrderNotes]([Proj],
[Workorder], [Owner], [LineNumber]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[WorkOrders] ADD
    CONSTRAINT [DF__WorkOrder__rowgu__30F848ED] DEFAULT (newid()) FOR [rowguid]
GO

CREATE UNIQUE INDEX [index_1977058079] ON [dbo].[WorkOrders]([rowguid])
ON [PRIMARY]
GO

CREATE UNIQUE INDEX [IX_ProjWo] ON [dbo].[WorkOrders]([Proj],
[Workorder]) ON [PRIMARY]
GO

> Jorgen, could you repost the schema showing all pk and fk relationships?
>
[quoted text clipped - 104 lines]
> >> >>
> >> >> Regards JorgenD

Reply to this Message

Michael Hotek - 25 Jan 2006 19:14 GMT

The merge engine works against the MSmerge_contents and MSmerge_genhistory
tables (MSmerge_tombstone for deletes). If a row doesn't exist within the
merge metadata tables, there is absolutely no way at all that it will ever
get sent to a publisher or subscriber. Once a change has been sent, an
entry is made into MSmerge_genhistory and that table prevents the change
from being sent more than once.

So, you define filters that dictate John's orders (and related data) only go
to him and Sue's orders (and related data) only go to her. You control this
by an entry within a table inside your database. John enters orders and
makes changes, those rows get logged to MSmerge_contents. Same thing
happens with Sue. The merge engine kicks off and sees that there are rows
which need to be sent. So, John's changes are uploaded as are Sue's
changes. The uploaded changes are then logged into MSmerge_genhistory which
tells the engine they have been received. From that point forward, those
changes are not going to be sent again. If you now go in an say that John's
orders can now be seen by Sue based on changing a piece of data, how is the
replication engine going to have any idea what it is that you want? Every
change related to John's data is sitting in a database somewhere, but it
doesn't necessarily have entries in MSmerge_contents for all of that data
and even if it did, not all of the changes are pending to be sent. So, just
because you changed a piece of data in the database, the replication engine
isn't going to know that you want it to dig through all of the base tables
and do an incremental snapshot of all of the data related to John's orders.

If you reconfigure the filters within replication, it invalidates the
subscriptions and forces a reinitialization to ensure that each subscriber
has the proper data according to the new filter definition. If you don't do
this, but are simply changing a piece of data, then the only way to make
sure everything is there is to reinitialize the subscription.

Signature

> Hi.
>
[quoted text clipped - 368 lines]
>> >> >>
>> >> >> Regards JorgenD

Reply to this Message

Hilary Cotter - 26 Jan 2006 05:03 GMT

Mike, you might want to look up join filters in BOL - this is precisely what
happens. A change in a table can affect data in other tables related by DRI.

I have attached a repro and provided a copy at
http://www.indexserverfaq.com/mike3.sql

This is the situation JorgenD is running into.

You will have to add the -HostName 'mike' switch to your pull agent.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> The merge engine works against the MSmerge_contents and MSmerge_genhistory
> tables (MSmerge_tombstone for deletes). If a row doesn't exist within the
[quoted text clipped - 410 lines]
>>> >> >>
>>> >> >> Regards JorgenD

Reply to this Message

JorgenD - 30 Jan 2006 20:52 GMT

Hi again.

So still no solution?
What about a workaround?

The only one I can think about is to make triggers on relevant tables at the
publisher that makes dummy updates to all related rows, to make sure that
they are included in the next merge. Or is it unthinkeable to make triggers
make inserts directly to msmerge_contents?

Regards,
Jorgen D.

> Mike, you might want to look up join filters in BOL - this is precisely what
> happens. A change in a table can affect data in other tables related by DRI.
[quoted text clipped - 268 lines]
> >>
> >> CREATE UNIQUE INDEX [index_1993058136] ON

Reply to this Message