SQL 2000 Script to IDENTITY KEY TO NOT FOR REPLICATION

Can you please write a script that will set IDENTITY COLUMN to NOT FOR REPLICATION rather than going into Design table to do it.

I want a script that can do it for all tables with IDENTITY COLUMNS & rimary key

I try below script it give error:

alter table tb_Benefit

alter column b_SID add  NOT FOR REPLICATION

Error:Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'NOT'.


 
RichardsoetAsked:
Who is Participating?
 
profyaCommented:
The ALTER COLUMN part of the ALTER TABLE syntax in SQL Server 2000 does not support chaning any of the IDENTITY-related properties including the NOT FOR REPLICATION. The only available ways as I can see is to:
1) drop the column and redefine it again using ALTER TABLE table_name ADD column_name data_type syntax
2) Export your data, generate SQL Script and use the normal text Replace on the generated SQL Script, run the script and then import you data back.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the script needs to be rather like this:
alter yourtable alter column b_SID INT  NOT FOR REPLICATION

0
 
RichardsoetAuthor Commented:
it does not seem to work, your script is the same as the one i posted in my question , it errors please have a look at the one i posted and compare to yours.am running sql2000
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.