Re: Merge filter inconsistant

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: kjvt (kjvt_at_newsgroups.nospam)
Date: 06/14/04 

Date: Mon, 14 Jun 2004 10:21:57 -0500

Vikrant, Lan:

Help!

It's now over a month since my original posting of this problem (six
days since my last posting), and we aren't much closer to
understanding what is causing the problems we're seeing. Nearly a
month ago we sent complete scripts to reproduce the problem, but have
no evidence that anyone has attempted to work with our scripts to
reproduce the problem. In the meantime, project deadlines are
imminent and we still have a severe problem.

What can be done to get attention focused on resolution?

Thank you.

Kees

On Tue, 08 Jun 2004 12:51:03 -0500, kjvt <kjvt@newsgroups.nospam>
wrote:

>Vikrant, Lan:
>
>We installed 818 with no change in the result. We're still seeing the
>same problem.
>
>I think the next step is for you to set up a test with the scripts I
>included on a prior post.
>
>Here's a test you can run that's simpler than the prior steps that I
>described, but also fails under our schema:
>Delete a work_item (including related data) from the publisher and the
>corresponding rows are not deleted from the subscriber.
>Interestingly, the msmerge_delete_conflicts table displays conflict
>descriptions stating that the foreign key constraint prevented the
>delete. For example, work_item could not be deleted because of a
>foreign key constraint to audit_dtl. The merge job runs successfully
>and the only way to identify that there was a problem was to either
>notice the data still in the subscriber or query the
>msmerge_delete_conflicts table on the publisher.
>
>Thank you.
>
>Kees
>
>
>On Tue, 08 Jun 2004 15:26:00 GMT, vikrantd@online.microsoft.com
>(Vikrant V Dalwale [MSFT]) wrote:
>
>>
>>
>>Hello Kees,
>>
>>Microsoft Engineer Lan has posted following reply. [ Due to our internal
>>tool issues, its appearing as a new thread ]. Please let us know if you
>>need further
>>assistance.
>>
>>*******************************************
>>From: lanlb@online.microsoft.com (Lan Lewis-Bevan [MS])
>>Date: Tue, 08 Jun 2004 04:08:26 GMT
>>Subject: id 22880492 Re: Merge filter inconsistant
>>Newsgroups: microsoft.public.sqlserver.replication
>>
>>Hi Kees,
>>
>>1. You may bring SQL server up to 818 by applying
>>
>>MS03-031:Security Patch for SQL Server 2000 Service Pack 3 from
>>
>>http://support.microsoft.com/default.aspx?scid=kb;en-us;821277
>>
>>2. If you still have the same problem with 818, and if you still haven't
>>heard from Baisong Wei, please let me know, and I can test it here.
>>
>>Thanks,
>>
>>Lan Lewis-Bevan
>>MS SQL support
>>
>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>>
>>**********************************
>>
>>Thanks for using MSDN Managed Newsgroup.
>>
>>Vikrant Dalwale
>>
>>Microsoft SQL Server Support Professional
>>
>>
>>Microsoft highly recommends to all of our customers that they visit the
>>http://www.microsoft.com/protect site and perform the three straightforward
>>steps listed to improve your computer’s security.
>>This posting is provided "AS IS" with no warranties, and confers no rights.
>>
>>
>>
>>--------------------
>>>From: kjvt <kjvt@newsgroups.nospam>
>>>Subject: Re: Merge filter inconsistant
>>>Date: Fri, 28 May 2004 13:40:53 -0500
>>>Message-ID: <nv0fb0pi0t952d0vce6o7d56rtn6lp606r@4ax.com>
>>>References: <EYOtxifQEHA.3996@cpmsftngxa10.phx.gbl>
>>>X-Newsreader: Forte Free Agent 2.0/32.652
>>>MIME-Version: 1.0
>>>Content-Type: text/plain; charset=us-ascii
>>>Content-Transfer-Encoding: 7bit
>>>Newsgroups: microsoft.public.sqlserver.replication
>>>NNTP-Posting-Host: i144152.cpa.state.tx.us 204.64.144.152
>>>Lines: 1
>>>Path:
>>cpmsftngxa10.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12
>>phx.gbl
>>>Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.replication:51228
>>>X-Tomcat-NG: microsoft.public.sqlserver.replication
>>>
>>>Lan,
>>>
>>>Thank you for looking into this problem. There's an extra wrinkle in
>>>my scenario that could affect the result. My filter condition is on
>>>another table. The filter condition excludes rows based on values in
>>>the second table, yet because of the filter join, those excluded rows
>>>are appropriately included in the replication. You may need to add
>>>that second table to see the same results.
>>>
>>>I posted my table defns and replication scripts to this newsgroup and
>>>received a reply from Baisong Wei that they would try to duplicate the
>>>error (see 5/18, appears as a separate thread in Agent Newsreader). I
>>>have not heard back from them. I'd love to get this solved sooner,
>>>but don't want to see you duplicating effort.
>>>
>>>We have version 8.00.760 on our server and locally. I believe this is
>>>sp3. Where do I obtain v8.18? It would be worth a retest on that
>>>version.
>>>
>>>Kees
>>>
>>>@@version on publisher:
>>>Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>>> Dec 17 2002 14:22:05
>>> Copyright (c) 1988-2003 Microsoft Corporation
>>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack
>>>4)
>>>
>>>
>>>@@version on subscriber:
>>>Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
>>> Dec 17 2002 14:22:05
>>> Copyright (c) 1988-2003 Microsoft Corporation
>>> Personal Edition on Windows NT 5.1 (Build 2600: Service Pack
>>>1)
>>>
>>>On Tue, 25 May 2004 01:36:27 GMT, lanlb@online.microsoft.com (Lan
>>>Lewis-Bevan [MS]) wrote:
>>>
>>>>Hi Kees,
>>>>
>>>>I checked your replication logic, couldn't figure out why the replication
>>>>behaves like that.
>>>>
>>>>So I setup a merge replication to test, using one table:
>>>>
>>>>1. Create the merge table:
>>>>
>>>>create table T3 (c1 int, c2 int, c3 char(8), c4 int)
>>>>insert T3 values (1, 1, 'one', 0)
>>>>
>>>>2. Checked the filter statement:
>>>>Select * from T3 where c1 in (select c1 from T3 where c3 = 'one' and c4
>>=1)
>>>> => 0 row returned
>>>>Select * from T3 where c3 = 'one' and c4 =1 => 0 row returned.
>>>>
>>>>3. Setup a merge replication for this table (T3) with row filter:
>>>>SELECT <published_columns> FROM [dbo].[T3] WHERE c1 in (select c1 from T3
>>>>where c3 = 'one' and c4 = 1)
>>>>
>>>>4. Run Snapshot and merge agents. Checked the subscriber table, no row
>>was
>>>>copied, This is as expected, since no row satisfies the filter
>>condition..
>>>>
>>>>5. updated publisher:
>>>>update T3 set c4=1
>>>>
>>>>6. Run merge agent, one row went to the sub, as expected. Now both pub
>>and
>>>>sub has one row with c4 = 1
>>>>
>>>>7. Updated subscriber:
>>>>update T3 set c4=0
>>>>
>>>>8. Run merge agent. The row at the sub was removed and the row at pub
>>was
>>>>updated. This is by design. At any time, as soon as a row becomes
>>>>dissatisfied to the filter condition, it will be removed from the
>>>>subscriber table.
>>>>
>>>>After Step 6, if I run step 7, before run merge agent, make another
>>update
>>>>statement:
>>>>update T3 set c1=0, c2=3 where c1=1
>>>>Then run merge agent, I see the row in subscriber was removed and the
>>row
>>>>in publisher was updated.
>>>>
>>>>
>>>>In your case, your first action (below) should remove the row from the
>>>>subscriber, then you won't have a chance to do the other updates:
>>>>
>>>>Action:
>>>>DNLD_MERG_CD is set to 0.
>>>>Merge is executed.
>>>>Result:
>>>>Merge code is 0 on both databases (expected)
>>>>
>>>>
>>>>Please test again. If confirmed, please let me know all the versions of
>>>>your publisher, subscriber and distributor (I am using 818 for all), and
>>if
>>>>possible, send your table schemas and replication script to me for
>>further
>>>>check.
>>>>
>>>>Regards,
>>>>
>>>>
>>>>Lan Lewis-Bevan
>>>>MS SQL support
>>>>
>>>>This posting is provided "AS IS" with no warranties, and confers no
>>rights.
>>>
>>>

Relevant Pages