Solved

SQL 2000 Script to IDENTITY KEY TO NOT FOR REPLICATION

Posted on 2006-11-20
4
266 Views
Last Modified: 2011-09-20
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'.


 
0
Comment
Question by:Richardsoet
4 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17981336
the script needs to be rather like this:
alter yourtable alter column b_SID INT  NOT FOR REPLICATION

0
 

Author Comment

by:Richardsoet
ID: 17982413
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
 
LVL 20

Expert Comment

by:Sirees
ID: 17982631
0
 
LVL 14

Accepted Solution

by:
profya earned 250 total points
ID: 18014883
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question