Transactional Replication of UPDATE fails

Question

Post reply

Transactional Replication of UPDATE fails

DavidGriffiths

SSC Rookie

Points: 33
More actions
August 1, 2002 at 9:56 am

#357587

We have two SQL Server 2000 databases. One is a Transactional Publisher and one is a Pull Subscriber. The database being replicated uses referential integrity. We have some applications talking to the Publisher via ODBC.

Replication is running quite happily until one of the applications issues an UPDATE statement on a particular table. The update itself works - no foreign keys are violated.

However, at this point the replication stops with an error "DELETE statement conflicts with column reference constraint blah".

I remember reading somewhere that SQL Server implements an update as a delete followed by an insert, internally. But most updates are replicated successfully.

What is going on ?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sarmistead SSCommitted Points: 1671 More actions · Answer 1

Okay, it appears that the update works in the publisher - is it failing in the subscriber? Does the table in the subscribing database have any constraints on it, like a foreign key perhaps? If so you may need to implement trace flag 8207 - look it up at MS site at the following link:

<http://support.microsoft.com/default.aspx?scid=kb;en-us;q302341>. If this link is not useable search on "8207".

HTH,

Steve Armistead,

Database Administration

Panther Systems Northwest, Inc.

Vancouver, WA

Steve

DavidGriffiths SSC Rookie Points: 33 More actions · Answer 2

Thanks Steve - that does seem to be the problem. We have referential integrity enabled on the Subscriber. The offending UPDATE is one which changes a column which has a unique index on it - this is then propagated as a DELETE/INSERT pair, and the DELETE breaks r.i. on the Subscriber.

I guess my best bet is just to disable r.i. on the Subscriber.

Nick Beagley Ten Centuries Points: 1349 More actions · Answer 3

I believe you can get around this by telling your article that you want to do an MCALL on the update rather than a CALL. This is done in the articles' @upd_cmd. Sorry, but I don't have time to test if it's true, but I just had a look through a distribution database and it seems to have records like this, rather than the DEL/INS pair: {CALL sp_MSupd_TABLE (NULL,NULL,'1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,96,0x0400)}

Here's the article:

 

exec sp_addarticle @publication = N'MyDatabse'

		, @article = N'TABLE'

		, @source_owner = N'dbo'

		, @source_object = N'TABLE'

		, @destination_table = N'TABLE'

		, @type = N'logbased'

		, @creation_script = null

		, @description = null

		, @pre_creation_cmd = N'drop'

		, @schema_option = 0x0000000000000073

		, @status = 16

		, @vertical_partition = N'false'

		, @ins_cmd = N'CALL sp_MSins_TABLE'

		, @del_cmd = N'CALL sp_MSdel_TABLE'

		, @upd_cmd = N'MCALL sp_MSupd_TABLE'

		, @filter = null

		, @sync_object = null

GO

The called sp_msupd_TABLE proc does an update.

sarmistead SSCommitted Points: 1671 More actions · Answer 4

David,

I think you can resolve this without removing RI on the subscriber - enable trace 8207 on the server, which will make the delete/insert into a true update instead, which should then not cause a problem for the subscriber.

In EM, right click the server, select properties, startup parameters, and add this code as a new parameter:

/T 8207

When the server is restarted all of the delete/update pairs will then become true updates.

HTH,

Steve Armistead,

Database Administration

Panther Systems Northwest, Inc.

Vancouver, WA

Steve

DavidGriffiths SSC Rookie Points: 33 More actions · Answer 5

Nick: I am not sure about this - the documented difference between a CALL and an MCALL is that the first modifies all the columns, whereas the second takes a bitmask of changed columns as an extra parameter and only modifies those columns which have been changed in an update.

Steve: yes, you're right - the MS KB article you referred me to explains this but unfortunately the Trace 8207 flag only affects what MS call "singleton UPDATEs" - i.e. updates which only affect one row. Updates which affect multiple rows (and which change uniquely constrained columns) are still propagated as DELETE/INSERT pairs. Looking at the code in our applications, I can't be sure that only single-row updates are generated.