Transactional replication problem with update

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

Transactional replication problem with update

Author

Topic

Dj463
Starting Member

11 Posts

Posted - 2011-03-22 : 09:42:49

Hello all,

I set up transactional replication without using snapshot from backup, i set "Do not replicate deletes" for the article properties. Everything works fine, except that whenever a value is updated at publisher the older value is not deleted at subscriber, instead i see both old and new rows at subscriber.

I thought the problem might be with the foreign key constarints since i configured the subscriber from backup. I dropped the foreign key constraints but still the same problem is persisting.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-22 : 10:28:37

That may be due to replication using delete/insert for updates rather than just an update statement. I believe there's an option to replicate the actual update instead of insert/delete, so you'll need to locate that and select it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 11:02:58

That's correct. In the Article Properties tab (if you're using the GUI) in the statement deliver section, go to UPDATE delivery format.

Default is SCALL. Can change it to UPDATE Statement.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 11:05:48

Although...if you look at the definition of the SP that replication generates...it uses an UPDATE statement as it is. At least in any that I've looked at.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-22 : 12:40:33

I believe it converts it to delete/insert before it hits the subscriber. I believe it happens at the distributor from what I remember when a MS engineer talked about this with me. So by the time it hits the subscriber and runs the update stored procedure, it's already an insert/delete so it'll call the insert/delete sprocs.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Dj463
Starting Member

11 Posts

Posted - 2011-03-22 : 13:24:49

As i am using SQL Server 2005, i think the update statement is delivered as update only but not as deferred update(delete/insert pair).

I ran the profiler at the subscriber database, in the profiler it shows the statement as

exec [sp_MSupd_dbo.Table] default,default,3,default,'2011-02-25 12:01:57:997',default,default,218004,2,0x14

So it is not using a deferred update(delete/insert) i think.

Dj463
Starting Member

11 Posts

Posted - 2011-03-22 : 13:28:16

@russell: what is the difference b/w SCALL and just UPDATE statement?
But by changing the article properties,i hope it would ask to reinitialize the subscription.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-22 : 13:42:00

SCALL executes the system generated stored proc to perform the update

Dj463
Starting Member

11 Posts

Posted - 2011-03-23 : 12:14:39

Is there any way we could make the update statement delivered as update only but not differed update(delete/insert), without re-initializing the subscription.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-03-23 : 13:16:26

Don't think so

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-03-30 : 11:07:53

Heres a KB for more info - http://support.microsoft.com/kb/302341
you dont need to reinitialize the subscriptions.. you just need to restart the SQL Service.. for the TF to effect..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources