Not For Replication

Question

Post reply

Not For Replication

Gilbert Grant

SSChasing Mays

Points: 619
More actions
January 19, 2006 at 1:57 am

#92853

Howzit,
Anyone know how to change an identity column so that "Not for Replication" is switched on? In SQL 2000 i di this:
UPDATE syscolumns
SET colstat = colstat | 8
WHERE colstat & 1 > 0
AND c.colstat & 8 = 0
but SQL 2005 does not allow this at all.
Regards
Gilbert

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

allen davidson SSCertifiable Points: 7042 More actions · Answer 1

Whoa. syscolumns is a system table and I strongly recommend you do not update it. It might just break your database.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

It should be

I'm not sure you can do it with "ALTER TABLE x ALTHR COLOUMN y NOT FOR REPLICATION

but you can do it through enterprise manager - go to the table design and lok at the properties of the identity column

MVDBA

Gilbert Grant SSChasing Mays Points: 619 More actions · Answer 3

So far, no problems with the edits to syscolumns, but by the fact that SQL2005 does not allow it means that I must find another way - neither am i happy with editing this table - in SQL 2000 one had to run "sp_configure 'allow updates' 1 RECONFIGURE WITH OVERRIDE" to be able to do this.

enterprise manager handles it very elegantly (not) by creating a temporary table, dropping and re-creating the original with the flag switched on - i want to avoid having to do this - very large tables + speed considerations.

ALTER TABLE does not work as far as i have tried (unless i am doing something wrong). It does not like NOT FOR REPLICATION PART + in the help files it does not mention that you can use this in an alter statement.

I am thinking one must maybe do something when setting up the replication schema - something to do with sp_changearticle.

Ashok Sharma Grasshopper Points: 11 More actions · Answer 4

Since SqlServer 2005 does not allow to update system tables than I Believe your best bet would be to script it.

(1) Generate a script for all the tables using Auto Generate feature.

(2) Manually type the 'Not for Replication' command after Identity value like

FieldName DataType IDENTITY(1,1) Not for Replication NOT NULL

(3) Rename the current table(s)

(4) Create New tables with help of script and copy the data from the Renamed table(s) to new tables.

(5) Delete the Renamed table(s).

(6) Make sure you have scripted all triggers(In case you are using them).

Gilbert Grant SSChasing Mays Points: 619 More actions · Answer 5

Got a SQL2005 solution from Hilary Cotter MS TechNet forum - sp_identitycolumnforreplication. Seems i will have to stick with changing syssolumns in SQL2000.