I have set up a transactional replication from multiple Databases (6 Different Server) to 1 consolidated database. The schema is identical. Segregation is done by the column SiteName . My problem is :

Every time when a table is altered , the MSIns,MSUpd and MSDel stored procedures at subscriber get altered and MSUpd and MSDel SPs where condition get changed based on only ID (PK) , and it overwrites the SPs where my condition was based on ID and Sitename. Thus , the destination table data gets updated for all the rows based on First database ID.

Example : If we alter table employee and add/alter column with update command for all 6 sites , when 1st site is applying the changes it's taking where condition based on ID only and if ID is 2 all the records will updated in destination DB with ID 2.

Is there any way to disable the replication as soon as table get altered so that i will have time to change the stored procedure at subscriber to add Sitename in where condition .

  • Rather than modifying the system-generated Replication procedures, have you looked into specifying custom stored procedures to use instead? – AMtwo Jun 5 at 11:51
  • @AMtwo No , have looked into specifying custom stored procedure , but for that i would be needing a longer downtime to re sync back billion of rows . That's the reason i am looking for the solution as above. – user26979 Jun 5 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.