Prev: Replication from 2005 to 2000 fails because of CREATE TYPE command
Next: Preparing for replication on sql server 2000
From: dday515 on 13 Mar 2010 16:31
SQL Server 2008 Standard SP1 Publisher and Server 2005 and 2008 Express
subscribers.

I have merge replication setup and working great, except for a single issue
that I'm hoping someone can help me with – it involves filtered articles and
the assignment process for who gets what records.

I'll list the schema in just a moment, but a quick overview of the tables
involved:

1. Consultant_Table – an internal Consultant Number and a link to the
users Active Directory Account Name
2. Reviews: A table listing a client and a start and end date.
3. Review Consultants: A 1-M table with Reviews – linking Reviews with
Consultants.
4. DRG_Table – On of the “Main” replication tables – this has a filter
based on SUSER_NAME() that determines if the record is replicated to a
specific subscriber.

Schema:

CREATE TABLE [dbo].[Consultant_Table](
[Consultant Number] [varchar](4)
[ADAcct] [varchar](50)
)) ON [PRIMARY]



CREATE TABLE [dbo].[Reviews](
[ID] [int] IDENTITY(1,1) ,
[ClientNum] [varchar](5) ,
[ReviewStartDate] [smalldatetime] ,
[ReviewEndDate] [smalldatetime] ,

CONSTRAINT [PK_Reviews] PRIMARY KEY CLUSTERED
(
[ID] ASC
)ON [PRIMARY]

CREATE TABLE [dbo].[ReviewsConsultants](
[ReviewID] [int] ,
[ConsultantNumber] [varchar](4)
CONSTRAINT [PK_ReviewsConsultants] PRIMARY KEY CLUSTERED
(
[ReviewID] ASC,
[ConsultantNumber] ASC
)) ON [PRIMARY]

CREATE TABLE [dbo].[DRG_Table](
[Client Number] [varchar](5)
[Date] [smalldatetime]
[Patient Number] [varchar](12)
[Consultant Number] [varchar](4)

CONSTRAINT [PK_DRG_Table] PRIMARY KEY CLUSTERED
(
[Date] ASC,
[Client Number] ASC,
[Patient Number] ASC
)


My filter on the DRG_Table is:

WHERE (Date BETWEEN DATEADD(d, - 30, GETDATE())

AND DATEADD(d, 30, GETDATE()))
AND EXISTS
(SELECT [Consultant Number] FROM Consultant_Table
WHERE ('DOMAIN \' + ADAcct = SUSER_NAME()) AND
(DRG_Table.[Consultant Number] = [Consultant Number]))
OR
(Date BETWEEN DATEADD(d, - 30, GETDATE()) AND
DATEADD(d, 30, GETDATE())) AND
EXISTS
(SELECT * FROM ReviewsConsultants INNER JOIN
Reviews ON Reviews.ID = ReviewsConsultants.ReviewID INNER JOIN
Consultant_Table AS Consultant_Table_1 ON
ReviewsConsultants.ConsultantNumber =
Consultant_Table_1.[Consultant Number] AND
'DOMAIN\' + Consultant_Table_1.ADAcct = SUSER_NAME()
AND DRG_Table.Date BETWEEN Reviews.ReviewStartDate
AND Reviews.ReviewEndDate WHERE
(Reviews.ClientNum = DRG_Table.[Client Number]))

Since the filter is on the DRG_Table, the replication triggers that are
created contain the logic to assign each row to the applicable subscriber.
That works perfectly.

However, lets say a record is already created in DRG_Table, and I need to
add an additional user to receive the record – I add the Consultant Number in
ReviewConsultants, but partition is never updated until the records in
DRG_Table are updated as well.

This makes sense to me, but it causes problems because by virtue of updating
the records in DRG_Table, I'm creating potential data conflicts. To work
around this now, I have each person who has the records in question in their
subscription synchronize and then cease work. I run an update query on the
records in question (which updates the subscription mappings), have every
re-synch and we're good to go. However, this is obviously not idea.

Is there any way to either automatically update or even manually update
just the subscriber mappings for the records in DRG_Table when a record in
REviewConsutlants changes? I supposed I could try and mimic the partition
logic from the update triggers in DRG_Table, but I'd like a cleaner solution
than that.
 | 
Pages: 1
Prev: Replication from 2005 to 2000 fails because of CREATE TYPE command
Next: Preparing for replication on sql server 2000