March 8, 2003 at 7:07 am
We are facing a problem replicating update statement on
unique key on a table, which has a detail table associated
to it. Here goes the case:
I have the following tables:
T1: ID (primary key), Name (Unique Key), Remarks
T1DET: ID (primary key), T1ID (foreign key to T1.ID),
SlNo, Amount
Say we have the following data set:
T1 => 1, 'AMIT', 'Kolkata, India'
2, 'Bill', 'Seattle, USA'
3, 'Sanchayan', 'Kolkata, India'
T1DET => 1, 1, 1, 25000
2, 1, 2, 30000
3, 1, 3, 50000
4, 2, 1, 70000
5 2, 2, 10000
=> whenever I am updating Name of a record of T1, with an
associated record in T1DET : publisher database
synchronisation fails with a error similar to : delete
statement fails violating foreign key constraint.
The following statement in the source database is
generating errors in the subscriber database, through this transaction is successful in source database without any error/warning:
UPDATE T1 SET Name = 'AMIT KHAN' Where ID=1
Regards,
Amit Khan
Ontrack Systems Limited
276B Lake Gardens
Kolkata - 700045
India
Phone - 91-33-24178434,35
Fax - 91-33-24221274
Mobile - 91-33-9830105090
Regards,
Amit Khan
Ontrack Systems Limited
276B Lake Gardens
Kolkata - 700045
India
Phone - 91-33-24178434,35
Fax - 91-33-24221274
Mobile - 91-33-9830105090
March 8, 2003 at 1:58 pm
By default, an update to a unique column in a replicated table is converted into a delete/insert pair in the subscribing database. If you have FKs on this table in the subscriber the delete will fail, stopping replication. MS provides a workaround - see the following writeup from MS concerning this issue:
INF: New Trace Flag to Enable Singleton Update for Transactional Replication <http://support.microsoft.com/default.aspx?scid=kb;en-us;302341>
Hope this helps,
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply