Newbie Needs Help ;)

Hello,
I'm new to replication. I have got a subscriber, publisher
ans distributer working ok, so now does the snapshot
replication, then the transactional ones.
It was working fine for 5 minutes then I had the following
error
Cannot update identity column 'ID'.
{CALL sp_MSupd_tblPerson
(NULL,0,NULL,NULL,NULL,NULL,NULL,' ',1,0,7146,0x82
03)}
ID is a primary key with an Identity setting get to 'Yes
but not for replication'.
Any pointers please
Peter
Peter,
are you using transactional with queued updating subscribers? If it is
standard transactional or transactional with updating subscribers, then on
the subscriber there should be no identity property, as the publisher will
manage the values.
Regards,
Paul Ibison
|||run this script on your subscriber.
sp_configure 'allow updates', 1
GO
reconfigure with override
GO
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0
and colstat & 0x0008 = 0
GO
sp_configure 'allow updates', 0
GO
reconfigure with override
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eCvcKcGNEHA.2704@.TK2MSFTNGP10.phx.gbl...
> Peter,
> are you using transactional with queued updating subscribers? If it is
> standard transactional or transactional with updating subscribers, then on
> the subscriber there should be no identity property, as the publisher will
> manage the values.
> Regards,
> Paul Ibison
>
|||If you want to do this ( ie: keeping identify columns on the subscribers
)_INS
you can do this:sp_MSupd_tblPerson
edit
this proc:
sp_MSupd_tblPerson
and remove the case where the pk_key column is updated ( ths will never
happen anyway )
and this proc:
sp_MSins_tblPerson
add
SET IDENTITY_INSERT "sp_MSins_tblPerson" on
here, replace the insert statement with something like
insert into "tblPerson" ("col1",... ) VALUES (@.param1, ... )
SET IDENTITY_INSERT "sp_MSins_tblPerson" off
Peter wrote:
> Hello,
> I'm new to replication. I have got a subscriber, publisher
> ans distributer working ok, so now does the snapshot
> replication, then the transactional ones.
> It was working fine for 5 minutes then I had the following
> error
> Cannot update identity column 'ID'.
> {CALL sp_MSupd_tblPerson
> (NULL,0,NULL,NULL,NULL,NULL,NULL,' ',1,0,7146,0x82
> 03)}
> ID is a primary key with an Identity setting get to 'Yes
> but not for replication'.
> Any pointers please
> Peter
|||while I agree that using set identity_insert in your insert procs will solve
this problem, you would be better to partition using different seeds and
probably increments on your publisher and subscriber, and put the Not For
Replication option on your identity columns on the Subscriber.
The primary key's are sometimes updated - normally this is a sign of poor
database design, however it is the default behavior of the datagrid control.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Olivier" <olivierwarez@.netscape.net> wrote in message
news:0Xcnc.43829$zm5.21011@.nntpserver.swip.net...
> If you want to do this ( ie: keeping identify columns on the subscribers
> )_INS
> you can do this:sp_MSupd_tblPerson
> edit
> this proc:
> sp_MSupd_tblPerson
> and remove the case where the pk_key column is updated ( ths will never
> happen anyway )
> and this proc:
> sp_MSins_tblPerson
> add
> SET IDENTITY_INSERT "sp_MSins_tblPerson" on
> here, replace the insert statement with something like
> insert into "tblPerson" ("col1",... ) VALUES (@.param1, ... )
> SET IDENTITY_INSERT "sp_MSins_tblPerson" off
>
>
> Peter wrote:
>
|||
> while I agree that using set identity_insert in your insert procs will solve
> this problem, you would be better to partition using different seeds and
> probably increments on your publisher and subscriber, and put the Not For
> Replication option on your identity columns on the Subscriber.
In this case there is one publisher and one subscriber, and the 'not for
replication' is set

> The primary key's are sometimes updated - normally this is a sign of poor
> database design, however it is the default behavior of the datagrid control.
It is not possible to update the identity column. Usually the identity
column is the primary key one
By the, what does
update syscolumns set colstat = colstat | 0x0008 where colstat & 0x0001 <> 0
and colstat & 0x0008 = 0
do exactly?