Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL 2000 Script to IDENTITY KEY TO NOT FOR REPLICATION

Posted on 2006-11-20
4
Medium Priority
?
306 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 143

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 750 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

636 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