Adding Not For Replication to Existing Identity Column

[Jack Harris]

Is is possible to add 'NOT FOR REPLICATION' to an existing identity column?
I'll need to do this without creating a new table and copying the existing
data.

Thanks,

[Paul Ibison]

Please take a look at "sp_identitycolumnforreplication", assuming you are on
SQL 2005.
Cheers,
Paul Ibison (www.replicationanswers.com)

Post by Jack Harris
Is is possible to add 'NOT FOR REPLICATION' to an existing identity column?
I'll need to do this without creating a new table and copying the existing
data.
Thanks,
--
Jack Harris

[Jack Harris]

Paul,

Thanks for the info. Alas this is SQL 2000. I found the code for updating
syscolumns which I might be able to use.

The client was transactional replication with updateable subscribers set up
for disaster recovery. The subscribers would only be updated directly if the
publisher database goes down. When the publisher database is restored, the
changes will flow back to the publisher, or at least that's the idea. I'm
assuming that if I don't get not for replication set then there will be
problems if the subscriber gets updated.

Jack Harris
--
Jack Harris

Post by Paul Ibison
Please take a look at "sp_identitycolumnforreplication", assuming you are on
SQL 2005.
Cheers,
Paul Ibison (www.replicationanswers.com)

Post by Jack Harris
Is is possible to add 'NOT FOR REPLICATION' to an existing identity column?
I'll need to do this without creating a new table and copying the existing
data.
Thanks,
--
Jack Harris

[Paul Ibison]

Jack - the best setup in this case is to have queued updating subscribers,
in which case the identity columns will be implemented rather like merge
replication ie the identity property will exist on the subscriber already.
HTH,
Paul Ibison (www.replicationanswers.com)

[Jack Harris]

Paul,

If I use queued updating subscribers, won't I still need to set the identity
columns to not for replication before creating the publication? I assume that
I would also setup auto range management when creating the publication.

I'm going to be initializing the subscribers using backups of the publisher
database as specified by the client. Any potential pitfalls there?

Thanks
--
Jack Harris

Post by Paul Ibison
Jack - the best setup in this case is to have queued updating subscribers,
in which case the identity columns will be implemented rather like merge
replication ie the identity property will exist on the subscriber already.
HTH,
Paul Ibison (www.replicationanswers.com)

[Paul Ibison]

Jack,
on the publisher you will set the identity ranges on the article properties
before initialization. You'll need to initialize the subscribers using the
snapshot to have the ranges applied. I can't remember if the NFR is put
directly on the publisher tables automatically - as far as I recall it is,
but I'll verify.
HTH,
Paul Ibison (www.replicationanswers.com)