SQL Server Performance Forum – Threads Archive
Merge replication – Missing records
Good afternoon, I’ve had this recurring problem with merge replication where some records appear to be missing, which eventually leads my users to believe the records do not exist, however, after looking for conflicts I am able to see them and resolve them. Some background on my environment: 1 MSSQL 2K Server (central database)7 remote servers using MSDE
Replicating 2 databases (1 db has 36 articles, the other has 26 articles) over T1/DSL VWAN. The both databases, NambePOS and NambeSOP, are used by 7 stores throughout the country to process customer orders. As each store processes an order, that data is replicated to the central server, which then updates the rest of the stores’ database(s). The three tables that are used extensively in NambePOS are: Transaction, TxLine, and Tender. When an order is placed, a transaction is created. When items are added to the order, they are added to TxLine with the corresponding Transaction number. Once the order has been tendered, the tender information is added to the Tender table for the corresponding transaction. Most of the problems I am seeing is there is not any TxLine data, but there is data in the Transaction and Tender tables. When the end-user tries to research a transaction that exhibits this behavior, the application will not show any information regarding the transaction and therefore assumes the transaction is not in the system. When I research it in Enterprise Manager, is see that there are conflicts in the TxLine table, and after resolving them the end-user can see the transaction. This causes major headaches because I am constantly resolving conflicts, and I am not entirely confident that all conflicts are showing up and I may end up having transactions that do not have txline data. — Here is a typical conflict:
The row was inserted at ‘<REMOTE LOCATION>.NambePOS’ but could not be inserted at ‘<CENTRAL SERVER>.NambePOS’. INSERT statement conflicted with COLUMN FOREIGN KEY constraint ‘Transaction_TxLine_FK1’. The conflict occurred in database ‘NambePOS’, table ‘Transaction’, column ‘Tx_ID’. Your help is greatly appreciated. D
Hi, Your problem seems to occur due to the inter-relationship between Transaction, TxLine, and Tender Tables. Is it possible to provide the sub-query while partitioning the Article Horizontally so that it should check if the corresponding data is existing on Transaction table while updating TxLine data. Regards, Arindam Ganguly
Are you performing queued updation?
Run sp_browsereplcmds for further information about where its failing. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>