SqlSyncScopeProvisioning - 复杂过滤器的问题 [英] SqlSyncScopeProvisioning - Problem with complex filter

问题描述

您好，

      我正在研究SQL Server 2005数据库之间的Sync（Peer-to-Peer）。我面临与过滤器同步的问题。以下是这种情况：

       I am working on the Sync (Peer-to-Peer) between SQL Server 2005 databases. I am facing problem for sync with filter. Following is the situation :

我的数据库包含Practice，Address，Patient，Doctor表格：

My database contains Practice, Address, Patient, Doctor tables as :

练习表的列：PracticeUid，Name

Practice table's columns : PracticeUid, Name

地址表的列：AddressUid，City，State，Zip，Country，.....

Address table's columns : AddressUid, City, State, Zip, Country, .....

患者表的列：PatinetUid，FirstName，LastName，.. ..........，AddressUid，PracticeUid

Patient table's columns : PatinetUid, FirstName, LastName, ............, AddressUid, PracticeUid

医生表的列：DoctorUid，ListName，Specialty，.........，AddressUid，PracticeUid

Doctor table's columns : DoctorUid, ListName, Specialty, ........., AddressUid, PracticeUid

我用Practice，Address&创建了范围。带过滤器的患者表：

I have created scope with Practice, Address & Patient tables with the filters :

Practice =>使用PracticeUid ='practice1Uid'

Practice => with PracticeUid = 'practice1Uid'

地址=>使用AddressUid IN（SELECT AddressUid FROM Patient WHERE PracticeUid ='practice1Uid'）[注意：我只想同步患者的地址而不是医生。]

Address => with AddressUid IN (SELECT AddressUid FROM Patient WHERE PracticeUid = 'practice1Uid') [Note : I want to sync only the Patient's Addresses and not of the Doctors.]

患者=>使用PracticeUid ='practiceUid'。

Patient => with PracticeUid = 'practiceUid'.

我正在尝试同步，但是地址条目有问题。

I am trying to sync but having trouble with the Address entry.

以下是步骤的问题：

1。）我在Peer1数据库中创建了一个新地址（地址表中的新条目）

1.) I created a new address (new entry in the Address table) in Peer1 database

2.）启动了与Peer2数据库的同步。

2.) Initiated the sync with Peer2 database.

3。）新创建的地址dint来到了Peer2（因为它没有被任何病人引用）。

3.) Newly created address dint came over to the Peer2 (because it was not referenced to any Patient).

4。）我创建了患者（患者表中的新条目）在Peer1数据库中，并引用了在同步之前创建的地址。

4.) I created a Patient (new entry in the Patient table) in Peer1 database and referenced the address that was created before sync.

5.）启动与Peer2数据库的同步。

5.) Initiated the sync with Peer2 database.

6。）新创建的患者服务转到了Peer2。原因是：'INSERT语句与FOREIGN KEY约束冲突'。我检查了统计数据，发现只有Patient条目被发送到了Peer2，而没有被这个Patient条目引用
的Address条目。

6.) Newly created patient dint came over to Peer2. Reason is : 'The INSERT statement conflicted with the FOREIGN KEY constraint'. I checked the statistics and found that only the Patient entry was sent to the Peer2 but not the Address entry that is referenced by this Patient entry.

7。）然后我更新了地址条目并再次启动同步。

7.) Then I updated the Address entry and initiated sync again.

8。）这次我看到上传了两个更改。但是我对患者入口也看到同样的事情，即  'INSERT语句与FOREIGN KEY约束冲突'。并且地址更新因冲突'LocalCleanedupDeleteRemoteUpdate'而失败。

8.) This time I see two changes were uploaded. But I see same thing with the Patient entry i.e.  'The INSERT statement conflicted with the FOREIGN KEY constraint'. And the Update of the Address failed with the conflict 'LocalCleanedupDeleteRemoteUpdate'.

 

这里我期待地址条目在第二个同步周期（步骤5），但是它因此我尝试了Step-7但没有运气。同样的情况也出现在我的数据库中的其他表中，所以你能告诉我我应该做些什么来处理这个问题吗？

Here I was expecting the Address entry to come over in the 2nd sync cycle (Step-5), but it dint so I tried Step-7 but no luck. The same situation is with other tables too in my database, so can you please tell me what I am supposed to do to handle this ?

请帮忙。

 

-Ajinath

推荐答案

第3步是预期的行为，因为您过滤中指定的IN子句。

step 3 is an expected behaviour because of the IN clause specified in you filter.

步骤6没有降低地址我认为也是预期的行为，因为Sync Fx已超过"timestamp"在步骤3中，所以步骤7是正确的方法：只需再次触摸该行以移动其"时间戳"

step 6's not bringing down the address i think is also an expected behaviour since Sync Fx has gone past the "timestamp" in step 3, so step 7 is the right approach: simply touch that row again to move its "timestamp"

执行步骤8，假设您的同步正在处理患者表之前导致外键错误的地址表。你可以在范围定义中重新排序表，以便在Patient表之前添加Address表吗？

for step 8, am assuming your sync is processing the Patient table before the Address table which causes the Foreign Key error. Can you reorder your table in the scope definition so that Address table is added first before the Patient table?

我怀疑'LocalCleanedupDeleteRemoteUpdate'错误是因为Sync Fx正在尝试更新一行确实存在，因为它之前没有同步（在步骤3中）。这实际上是作为冲突触发的，你可以通过指定RetryWithForceWrite
动作来处理它。

I suspect the 'LocalCleanedupDeleteRemoteUpdate' error is because Sync Fx is trying to update a row that does exists since it wasnt synched before (in step 3). This will actually be fired as a conflict and you can handle it by specifying RetryWithForceWrite action.

 

这篇关于SqlSyncScopeProvisioning - 复杂过滤器的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！