Set identity to not for replication in T-SQL
Home > IT Answers > SQL Server > Set identity to not for replication in T-SQL
Db23
230 pts.
0
Q:
Set identity to not for replication in T-SQL
SQL Server 2000, ALTER, 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
ASKED: Oct 20 2009  5:58 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Mrdenny
46795 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
To change the column...

ALTER TABLE YourTable
      ALTER COLUMN YourColumn DROP NOT FOR REPLICATION


To change the trigger just do a ALTER TRIGGER and add the NOT FOR REPLICATION to the trigger definition.
Last Answered: Oct 22 2009  7:38 PM GMT by Mrdenny   46795 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

mrdenny   46795 pts.  |   Oct 22 2009  1:23AM GMT

What do you mean by change the identity column?

 

Db23   230 pts.  |   Oct 22 2009  2:51AM GMT

Columns which are set as identity field.
create table(
int id idenitity(1,1)
)

 

Db23   230 pts.  |   Oct 23 2009  1:22AM GMT

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

 

mrdenny   46795 pts.  |   Oct 23 2009  6:49PM GMT

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.

 
0