locked
Inserting into replicated table RRS feed

  • Question

  • I have a peculiar problem here. I have a table MasterDB.Employee and its being replicated in ReplicationDB.Employee.

    I have a .Net screen which has boxes to key in information about an employee and when they hit save, it gets inserted into the MasterDB.Employee table and it takes about let's say 30 secs for it to get to ReplicationDB.Employee. As soon as they save and exit out it shows a screen with all employees along with their information. The storedproc that loads the employees is only on the ReplicationDB because it has more information than what's on MasterDB. So as soon as one saves and exits out of that screen, that employee is not loaded on the list since there is a latency.

    I  need some help here on how to tackle this issue.

    Wednesday, October 31, 2012 3:26 PM

Answers

  • Hi PrivatePyle1982,

    From the tables name, it seems that you are using replication. I want to inform that replication takes time to synchronize data between Publisher and Subscribers, if we try to pull data from the Subscribers immediately after inserting data into Publisher, the data may not have been synchronized yet. As Tom suggested, it is better to read/write to ReplicationDB and synchronize the data to MasterDB. For more detail information, please refer to the following link:

    Merge Replication:
    http://msdn.microsoft.com/en-us/library/ms152746.aspx


    Allen Li

    TechNet Community Support

    • Marked as answer by pp1299 Thursday, November 1, 2012 3:44 PM
    Thursday, November 1, 2012 9:19 AM

All replies

  • You cannot insert into a replicated table, because it is being updated when replicating.

    There is a delay in the replication, therefore this workaround can help you.

    A good and clean solution is to: append the just inserted row with the dataset returning rows from the stored procedure.

    1. When inserting the row into the Master Database, save new row in "memory" in you .Net Application.
    2. Execute the stored procedure to get all the rows (except the last one which is missing)
    3. Merge the row "in memory" with the dataset.

    • Edited by irusul Thursday, November 1, 2012 2:50 PM
    Wednesday, October 31, 2012 4:15 PM
  • So let me understand what you are saying.

    You have an application which writes to MasterDB and reads from ReplicationDB?   That is going to be prone to the issue you describe.  The simplest solution is to artificially pause the screen after save for 10-15 secs so the data has time to get replicated.


    The best solution is probably to setup MERGE replication and read/write to ReplicationDB.
    • Edited by Tom Phillips Wednesday, October 31, 2012 8:58 PM
    Wednesday, October 31, 2012 8:57 PM
  • Hi PrivatePyle1982,

    From the tables name, it seems that you are using replication. I want to inform that replication takes time to synchronize data between Publisher and Subscribers, if we try to pull data from the Subscribers immediately after inserting data into Publisher, the data may not have been synchronized yet. As Tom suggested, it is better to read/write to ReplicationDB and synchronize the data to MasterDB. For more detail information, please refer to the following link:

    Merge Replication:
    http://msdn.microsoft.com/en-us/library/ms152746.aspx


    Allen Li

    TechNet Community Support

    • Marked as answer by pp1299 Thursday, November 1, 2012 3:44 PM
    Thursday, November 1, 2012 9:19 AM