Set identity to not for replication in T-SQL

290 pts.
Tags:
Microsoft SQL Server 2000
T-SQL
Hi, Does anyone know the TSQL to change the identity column on a table in SQL Server 2000. And also the TSQL to change trigger to not for replication. Thanks

Answer Wiki

Thanks. We'll let you know when a new response is added.

To change the column…

<pre>ALTER TABLE YourTable
ALTER COLUMN YourColumn DROP NOT FOR REPLICATION</pre>

To change the trigger just do a ALTER TRIGGER and add the NOT FOR REPLICATION to the trigger definition.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    What do you mean by change the identity column?
    66,280 pointsBadges:
    report
  • Db23
    Columns which are set as identity field. create table( int id idenitity(1,1) )
    290 pointsBadges:
    report
  • Db23
    Thank you for the reply The changing column identity field syntax works on SQL Server 2005 but when I run the same command in SQL Server 2000, it is returning Incorrect syntax near 'NOT'. I have read that you can change the identity column in 2000 by alter the system tables, but I don't want to do this, as one of our DBA indicated that it not a good idea to change the system table and that is why they remove it in 2005. Is there any other way beside alter the system tables and how safe is it to alter the system tables? Another option is to script out the database, make the changes to the script, recreate the database and import all the data to the created database. Do you know why the NOT FOR REPLICATION isn't added to SQL Server 2000 by default in the first place? Does adding these option to database which will not be configure for replication affect their performance? Thanks
    290 pointsBadges:
    report
  • Denny Cherry
    Changing system tables is never recommended as if can have disastrous repercussions if not done correctly. In SQL 2000 you'll probably want to recreate the table under a different name with the different options set, then move the data into the new table, then rename the tables, rebuild the indexes and add any foreign constraints. There are hundreds of features which aren't included in SQL 2000 which have been included in SQL 2005 and SQL 2008. As to why that one feature wasn't included in SQL 2000 I've got no idea. SQL 2000 was only the second version of SQL Server which included replication, so there weren't a lot of extra features added around maintaining it.
    66,280 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following