Dynamic Filter Not Updating Correctly

I have several SQL Mobile devices (WM5) syncronizing with a SQL 2005 database through merge replication. We are using the HOST_NAME parameter to filter several tables. A few of these filters use a sub-query to filter the data because of the complexity of the filter (need to do more than join to one table). The subquery will use the HOST_NAME parameter. For example, I want to get a list of all users in the current user's company I need to use host_name to get the current user, find their company ID, then filter the user table on that company ID.

Everything works fine until a user is removed from the table. In that case, the delete statement is not replicated to the device until they reinitialize the mobile database.

Thanks for the help!!

Mike

it's unclear what the problem is without knowing what your filter clause looks like. Could you script out your publication, article and any merge/join filter clauses?|||

One of the affected pieces consists of 2 tables. A USER_LOCATION table that contains the locations a user want to see and a LOCATION table that contains all locations (see script below). I filter the USER_LOCATION table with the following filter: WHERE cast(person_id as varchar(36)) = host_name(). I then use a join to extend the filter on the location_id to the LOCATION table. On the device, I do not use the USER_LOCATION table as it is really just used to filter the proper locations for the user. I have also tried a filter on the LOCATION table directly using a subquery (so that the user_location table never makes it to the device). The same problem still shows up: If a user removes a location from their list from our website (used to configure their settings, which are replicated to the device), the next sync does not delete the record from the device. In fact, the only way to get the delete to take is to reinitialize the database on the device.

Tables:

CREATE TABLE [dbo].[USER_LOCATION](

[user_location_id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_USER_LOCATION_user_location_id] DEFAULT (newsequentialid()),

[user_id] [uniqueidentifier] NOT NULL,

[location_id] [uniqueidentifier] NOT NULL)

GO

CREATE TABLE [dbo].[LOCATION](

[location_id] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF__LOCATION__locati__48CFD27E] DEFAULT (newsequentialid()),

[name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[abbreviation] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL)

|||

Mike, it would be much easier if you scripted your publication, articles (maybe just for USER_LOCATION and LOCATION for now) and merge filter joins, can you do that please?

Just to be clear, when a row from LOCATION is deleted at the publisher, the delete isn't replicated down to the subscriber, correct? That would mean there's nonconvergence, and validation would fail. Could you verify that validation for the given subscriber fails?

|||

Actually, we just updated some of our code and upgraded to SP1 and the problem seems to be gone. Hopefully we won't see it in the future. Thanks for the help!

Mike

|||Hello,
I am facing the same problem. I have dynamic filters on most tables and the rows dont get deleted from the subscriber once they cease to satisfy the filter conditions.
HEre is a scenario: A Dynamic filter checks for suser_sname() and based on that(and a few other conditions) sends rows to the subscriber. when the data in the column used to compare the suser_sname() value changes at the publisher , the related row automatically got deleted from the subscriber. Recently this has stopped happening. Validation of the subscription also fails. Mike- What did you upgrade to SP1? We are running off SQL Server2005 SP2? thanks in advance. Rocky