Strange behaviour of update proc in replication

Question

Post reply

Strange behaviour of update proc in replication

ishaan99

SSCertifiable

Points: 6261
More actions
April 26, 2005 at 5:07 pm

#88252

I have a table set up as article in replication. This has an ID column which is set as " NOT FOR REPLICATION". But somehow the update procedure that was created using "sp_scriptmappedupdproc" shows this ID column due to replication errors. This ID column is set as identity and also has a clusterd PK on it.Is there any reason why this is happening or can anything be done to avoid this? any help will be greatly appreciated.
TIA

Viewing 11 posts - 1 through 10 (of 10 total)

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

prn9974 SSCrazy Points: 2060 More actions · Answer 1

'Not For Replication' is only for the 'identity' property and not for the column!

noeld SSC Guru Points: 96590 More actions · Answer 2

Are you expecting updates on the subscriber?

If the anwser is yes, have you partitioned it some how? (manually, automatically)

* Noel

ishaan99 SSCertifiable Points: 6261 More actions · Answer 3

Yes i do understand not for replication is only for identity columns and i do have ID set as identity column . I dont want it to be updated at subscriber too. why is then it gets shown in "sp_MSUpd" procedure?? ANy thoughts??

prn9974 SSCrazy Points: 2060 More actions · Answer 4

As it is the PK, Replication would use it for comparison...

ishaan99 SSCertifiable Points: 6261 More actions · Answer 5

Does that mean it overrides the property of Not for Replication when turned on that column which has a PK. then whats the use of not for replication.

prn9974 SSCrazy Points: 2060 More actions · Answer 6

It does not override. It only makes sure that the Identity property in not set in the Subscriber.

noeld SSC Guru Points: 96590 More actions · Answer 7

It is not overriding the identity property, it is replicating the PRIMARY KEY!

Not for replication will provoke that the data replicated (inserted) at the subscriber won't affect the value of the next identity that will be generated there if inserted by anything other than the replication agent!

[edit:] I am now refering to merge. For Transactional the Identity property won't be replicated at all

* Noel

ishaan99 SSCertifiable Points: 6261 More actions · Answer 8

Sorry i am a bit confused now. Ours is a transactional replication. and both at publisher and subscriber we have " not for replication" turned on. Is it correct or should it be only at publisher side. also how can the identity column be avoided in update procedure on subscriber database. does anything need to be done for this? Thanks a lot for all the answers.

noeld SSC Guru Points: 96590 More actions · Answer 9

if you have them in both places and marked as "NOT FOR REPLICATION" in both places you need to partition or manage the value ranges at each location. In other words you have to ressed them DIFFERENTLY like

- odds on publisher and even on the Subscriber

- Positive on one Negative on the other, etc ... you get the Idea,Right?

and apply check constraint on them also

This setup is to facilitate inserts on the subscriber! (and avoid PK Colission)

But as you mentioned that you don't need that, then the Normal set up of transactional replication does NOT create the Identity property on the subscriber (not needed becuse it will just use the value as it comes from the distribution agent!)

hth

* Noel

prn9974 SSCrazy Points: 2060 More actions · Answer 10

Noel's last para sums up. leave it as it is! no harm will come. As long as there are not value conflicts or failures, the replication is on its normal course.