sp_MSupd_ Cannot update identity column

[Christian Hamel]

Hello,

We're experiencing problems with the replication since a few days. I have a
transactional replication process that have approximately 31 subscribers
(Laptop) (User=MSDE, Server=SQL Server 2000 Enterprise). This process is
running fine since 3-4 years. The problems started this week once I change
the value in one table for one specific record. It is funny because it is
not the first time I'm changing stuff in this table. Now, I have 18
Subscribers out of 31 that receive the error message listing in the topic.

Cannot update identity column 'General_ID'.
(Source: CA-SR-2961 (Data source); Error number: 8102)

I've seen other thread on that subject on the net but don't know exactly
what are my options. Yes I have an identity column (General_ID) that is set
to NOT FOR REPLICATION = NO. As per my understanding it should be set to
Yes. however, I don't understand why it worked for so many years and now it
start bugging me up.

What are my options? Do I absolutely have to drop each of the subscription,
change the column identity to Not For Replication = Yes and create back the
subscription? I hope not, my users are everwhere in the country and that
would be kinda difficult for me to accomplish.

Any help/recommendations would be greatly appreciated. Thanks!

[Christian Hamel]

Forgot to mentionned that I tried reinitializing a few times all the
subcribers & regenerating the Snapshot but still the same problems occurs...

Post by Christian Hamel
Hello,
We're experiencing problems with the replication since a few days. I have
a transactional replication process that have approximately 31 subscribers
(Laptop) (User=MSDE, Server=SQL Server 2000 Enterprise). This process is
running fine since 3-4 years. The problems started this week once I
change the value in one table for one specific record. It is funny
because it is not the first time I'm changing stuff in this table. Now, I
have 18 Subscribers out of 31 that receive the error message listing in
the topic.
Cannot update identity column 'General_ID'.
(Source: CA-SR-2961 (Data source); Error number: 8102)
I've seen other thread on that subject on the net but don't know exactly
what are my options. Yes I have an identity column (General_ID) that is
set to NOT FOR REPLICATION = NO. As per my understanding it should be set
to Yes. however, I don't understand why it worked for so many years and
now it start bugging me up.
What are my options? Do I absolutely have to drop each of the
subscription, change the column identity to Not For Replication = Yes and
create back the subscription? I hope not, my users are everwhere in the
country and that would be kinda difficult for me to accomplish.
Any help/recommendations would be greatly appreciated. Thanks!

[Paul Ibison]

Christian,
please see if this applies:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

[Christian Hamel]

K, I'll check over the weekend if it fixes the problem. Thanks.

Post by Paul Ibison
Christian,
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
http://www.nwsu.com/0974973602p.html)

[Hilary Cotter]

You might want to edit the update proc and comment out the portions where it
update the identity columns as well.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Christian Hamel
K, I'll check over the weekend if it fixes the problem. Thanks.

Post by Paul Ibison
Christian,
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
http://www.nwsu.com/0974973602p.html)

[Christian Hamel]

Are you talking about this Stored Proc: sp_MSsync_upd_tblGeneral_1 ?

The only update sentence in that procedure is not trying to update my
identity column (General_ID). I'm kinda lost here :(

update [dbo].[tblGeneral] set
[General_Category] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else
[General_Category] end,
[General_Description] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else
[General_Description] end,
[General_Used] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else
[General_Used] end,
[Region] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [Region]
end,
[General_Parameter1] = case substring(@bitmap,1,1) & 32 when 32 then @c6
else [General_Parameter1] end,
[General_Parameter2] = case substring(@bitmap,1,1) & 64 when 64 then @c7
else [General_Parameter2] end,
[msrepl_tran_version] = case substring(@bitmap,1,1) & 128 when 128 then @c8
else [msrepl_tran_version] end,
[General_Parameter3] = case substring(@bitmap,2,1) & 1 when 1 then @c9 else
[General_Parameter3] end

Post by Hilary Cotter
You might want to edit the update proc and comment out the portions where
it update the identity columns as well.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Post by Christian Hamel
K, I'll check over the weekend if it fixes the problem. Thanks.

Post by Paul Ibison
Christian,
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q302/3/41.ASP&NoWebContent=1
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
http://www.nwsu.com/0974973602p.html)