Cannot Update Identity Column

I am running SQL Server 2000 with Service pack 3. I have a database with a
transactional publication that is not setup for immediate or queued updating.
The tables I am replicating have a primary key that is also an identity
column. The publication pushes a subscription to a database on the same
instance of SQL Server. The articles are setup to “Keep the existing table
unchanged”. The tables in the subscriptions are exact copies of the
publication tables except the identity column is setup: (not for
replication). I also have the Identity Seed setup so that neither the
publication nor the subscription will use the same Identity. I have put in a
Check Constraint on all tables to make sure of this. With this setup I can
update the publication table and it replicates fine to the subscription table
I also can update and insert records into the subscription table directly,
but when I update the publication table I get this error when it tries to
replicate to the subscription table: "Cannot Update Identity Column 'Table
Name'. All I want to do is replicate my production databases to an exact copy
that I will use for testing. So I never want to have the subscription update
the publication, but I do want to be able to insert and update the
subscription for testing and have the subscription be up to date with the
publication database. Am I missing something to get this setup correctly or
is there a better way to do this?
Thanks for your help,
Brandon
what columns are you updating? By chance you might be updating the pk or the
identity column. Some apps update each column including the pk or identity
column.
There are two strategies to handle this problem. The first is to comment out
the identity column update you see in the second part of the proc. The
second strategy is to replace the update proc that SQL Server uses with the
proc generated by the output of this command.
sp_scriptdynamicupdproc 1
where 1 is the article id obtained from sysarticles.
Hilary Cotter
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
"Brandon" <Brandon@.discussions.microsoft.com> wrote in message
news:698DC490-4A15-429F-931D-23282017E090@.microsoft.com...
>I am running SQL Server 2000 with Service pack 3. I have a database with a
> transactional publication that is not setup for immediate or queued
> updating.
> The tables I am replicating have a primary key that is also an identity
> column. The publication pushes a subscription to a database on the same
> instance of SQL Server. The articles are setup to "Keep the existing table
> unchanged". The tables in the subscriptions are exact copies of the
> publication tables except the identity column is setup: (not for
> replication). I also have the Identity Seed setup so that neither the
> publication nor the subscription will use the same Identity. I have put in
> a
> Check Constraint on all tables to make sure of this. With this setup I can
> update the publication table and it replicates fine to the subscription
> table
> I also can update and insert records into the subscription table directly,
> but when I update the publication table I get this error when it tries to
> replicate to the subscription table: "Cannot Update Identity Column 'Table
> Name'. All I want to do is replicate my production databases to an exact
> copy
> that I will use for testing. So I never want to have the subscription
> update
> the publication, but I do want to be able to insert and update the
> subscription for testing and have the subscription be up to date with the
> publication database. Am I missing something to get this setup correctly
> or
> is there a better way to do this?
> Thanks for your help,
> Brandon
>
|||I was running a simple update statement in Query Analyzer: "Update Test set
TestColumn = 25 where TestID = 1". The stored procedure which is created by
default was trying to update the primary key like you said, so I commented
that out of the sp and that worked.
Thanks!
Brandon
"Hilary Cotter" wrote:

> what columns are you updating? By chance you might be updating the pk or the
> identity column. Some apps update each column including the pk or identity
> column.
> There are two strategies to handle this problem. The first is to comment out
> the identity column update you see in the second part of the proc. The
> second strategy is to replace the update proc that SQL Server uses with the
> proc generated by the output of this command.
> sp_scriptdynamicupdproc 1
> where 1 is the article id obtained from sysarticles.
> --
> Hilary Cotter
> 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
> "Brandon" <Brandon@.discussions.microsoft.com> wrote in message
> news:698DC490-4A15-429F-931D-23282017E090@.microsoft.com...
>
>