April 26, 2005 at 5:07 pm
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
April 27, 2005 at 9:11 am
'Not For Replication' is only for the 'identity' property and not for the column!
April 27, 2005 at 9:19 am
Are you expecting updates on the subscriber?
If the anwser is yes, have you partitioned it some how? (manually, automatically)
* Noel
April 27, 2005 at 9:56 am
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??
April 27, 2005 at 10:00 am
As it is the PK, Replication would use it for comparison...
April 27, 2005 at 10:37 am
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.
April 27, 2005 at 10:51 am
It does not override. It only makes sure that the Identity property in not set in the Subscriber.
April 27, 2005 at 10:54 am
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
April 27, 2005 at 1:51 pm
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.
April 27, 2005 at 2:42 pm
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
April 28, 2005 at 4:50 am
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.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply