Re: Merge filter inconsistant - MS Replication Problem.zip (0/1)
From: kjvt (kjvt_at_newsgroups.nospam)
Date: 05/18/04
- Next message: kjvt: "Re: Merge filter inconsistant - MS Replication Problem.zip (1/1)"
- Previous message: Paul Ibison: "Re: Dynamic Filter, Inline Table & HOST_NAME() - Does it work or MS BUG?"
- In reply to: Michael Cheng [MSFT]: "Re: Merge filter inconsistant"
- Next in thread: kjvt: "Re: Merge filter inconsistant - MS Replication Problem.zip (1/1)"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 18 May 2004 18:22:31 -0500
Michael,
I have attached the files you need to reproduce the problem. I have
run these scripts and successfully reproduced the problem here in our
environment.
Contents:
create_test_loc.sql - script to create test databases on subscriber
create_test_rep.sql - script to create test databases on publisher
fGetCurrentUserID.sql - script to create filter criteria function on
publisher
TEST_REP pub.sql - script to create publication on publisher
sub_local_test.sql - script to create subscription on subscriber
TEST_REP_audit_dtl.txt - data for audit_dtl table
TEST_REP_wi_asgn.txt - data for wi_asgn table
TEST_REP_work_item.txt - data for work_item table
Notes:
- in some of the scripts you'll need to replace OWNERNAME??? with the
publication owner name and PUBLISHERSERVERNAME??? with the server name
of the publisher server
- the publication is set to load the snapshot from an alternate UNC
path: \\PUBLISHERSERVERNAME???\SNAPSHOT
- our subscriber's SQL Server Agent runs under their Windows User
Name, which corresponds to the Asgn_to_user_id column in the wi_asgn
table; the fGetCurrentUserID function formats the SUSER_SNAME for use
in the filter
Steps to reproduce:
1) setup databases, load with data, run replication scripts, run
merge, result: data merges succesfully
2) Update TEST_LOC.dbo.WI_ASGN for a specific WI_GUID set the
DNLD_MERG_CD = 0, run merge, result: dnld_merg_cd updated on publisher
3) Update TEST_LOC.dbo.AUDIT_DTL or TEST_LOC.dbo.WORK_ITEM for the
same WI_GUID (set any column to a different value on the subscriber)
and merge, result: data is merged to publisher even though
dnld_merg_cd is 0 (violation of filter)
4) Update TEST_REP.dbo.AUDIT_DTL or TEST_LOC.dbo.WORK_ITEM for the
same WI_GUID (set any column to a different value on the publisher)
and merge, result: no data is merged
5) Repeat step 3), this time there's a different result: no data is
merged
I have also answered your questions below. Let me know if you need
further information to reproduce.
Thank you.
Kees
On Tue, 18 May 2004 12:50:11 GMT, v-mingqc@online.microsoft.com
("Michael Cheng [MSFT]") wrote:
>Hi Kees,
>
>Thanks for your update.
>
>>From your descriptions, I understood that
>1.
>WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =1)
>the WHERE clause works well, why didn't you use this clause only?
Work item has a one-many relationship with wi_asgn (there may be many
employees assigned to a single work item). If I am assigned to a work
item, I need to replicate all the assignment records for the work item
(including other employees' assignment records). But I don't want any
assignments for work items that I'm not assigned to. So for example:
wi_guid asgn_to_user_id dnld_merg_cd
1 kees 1
1 john 1
1 jane 1
2 john 1
3 jane 1
If kees merges, then he should get the first three rows. If john
merges, then he should get the first four rows. And if jane merges,
she should get rows 1-3 and 5.
By filtering on work_item, then joining wi_asgn to work_item
on wi_guid, I can return the proper rows.
>
>2.
>WHERE [Work_Item].[WI_GUID]
>IN
>(SELECT WI_GUID
> FROM [dbo].[WI_ASGN]
> WHERE (ASGN_TO_USER_ID = dbo.fGetCurrentUserID()) and (DNLD_MERG_CD =1))
>By using WHERE clauses above, could you make the right result back in Query
>Analyzer?
Yes, and this works when the dnld_merg_cd = 1. The problem begins
when the value changes to 0 causing a record to go out of the
partition.
>Could you make all other clause and condition right back from
>Query Analyzer?
A join also will work, but cannot be used as a filter criteria in the
publication.
> I am not sure what dbo.fGetCurrentUserID() send back, but
>based on my testing, the above could work well.
>Could you make it without dbo.fGetCurrentUserID()?
This identifies the subscriber from SUSER_SNAME. The account we use
for Windows authentication for the SQL Agent service matches with the
column values in asgn_to_user_id. See the attached sample code.
Identifying the subscriber is essential to filtering the correct
partition.
I think the problem we're having could be reproduced without
fGetCurrentUserID and just filtering on dnld_merg_cd, but I have not
tried this.
>
>Would you please send me some sample data that could reproduce the problem?
Attached.
>Could you ensure dbo.fGetCurrentUserID() send the right result and
>condition for WHERE clause?
Yes, I've tested this as the only filter clause, with multiple
subscribers (user id's).
>
>Thank you for your patience and cooperation. If you have any questions or
>concerns, don't hesitate to let me know.
>
>Sincerely yours,
>
>Michael Cheng
>Microsoft Online Support
>***********************************************************
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and confers no rights.
>Please reply to newsgroups only, many thanks.
- Next message: kjvt: "Re: Merge filter inconsistant - MS Replication Problem.zip (1/1)"
- Previous message: Paul Ibison: "Re: Dynamic Filter, Inline Table & HOST_NAME() - Does it work or MS BUG?"
- In reply to: Michael Cheng [MSFT]: "Re: Merge filter inconsistant"
- Next in thread: kjvt: "Re: Merge filter inconsistant - MS Replication Problem.zip (1/1)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
