Identity seed/increment of MS SQLSERVER 2000 and MSDE

How can I change the increment value of an identity column?
I absolutely need to set a new value for the "increment"
DBCC CHECKIDENT only allow me to change the increment.
ALTER COLUMN does not allow altering identity
Is there a system column i can update or ?
I read few post suggesting using the enterprise manager to do so... but
we got lots of tables with many levels and fk. it's almost impossible in
our situation.
In fact we got many offline databases that where supposed to be
identity(-1,-1) but I just get a surprise!
Tons of data are alrealy inserted (positively) by a "kind of"
replication. but locally created records should be negative.
Thanks a lot
*** Sent via Developersdex http://www.developersdex.com ***Hi
You cannot update an IDENTITY property. Create another non_identity column
and move all data (of identity values) to the column.
You will be able to update a new created column and later on delete an
IDENTITY column
"Vincent" <anonymous@.devdex.com> wrote in message
news:Oq%23ev9A9HHA.600@.TK2MSFTNGP05.phx.gbl...
> How can I change the increment value of an identity column?
> I absolutely need to set a new value for the "increment"
> DBCC CHECKIDENT only allow me to change the increment.
> ALTER COLUMN does not allow altering identity
> Is there a system column i can update or ?
> I read few post suggesting using the enterprise manager to do so... but
> we got lots of tables with many levels and fk. it's almost impossible in
> our situation.
> In fact we got many offline databases that where supposed to be
> identity(-1,-1) but I just get a surprise!
> Tons of data are alrealy inserted (positively) by a "kind of"
> replication. but locally created records should be negative.
> Thanks a lot
>
> *** Sent via Developersdex http://www.developersdex.com ***

Identity Column Null? WHAT?

Hey all
Wanted to check the identity of my column so I ran this:
dbcc checkident (tablename)
It came back saying it was null!!!
"Checking identity information: current identity value 'NULL', current
column value 'NULL'."
What gives? When I enter a value in the column...it starts with 0. I want it
to start with 1!!! Any suggestions? I already tried reseeding it and it still
says null grrrr.
Thanks
Theresa
> What gives? When I enter a value in the column...it starts with 0. I want
> it
> to start with 1!!! Any suggestions?
Drop the table and create it correctly?
CREATE TABLE dbo.MyTable
(
IdentityColumn INT NOT NULL IDENTITY(1,1)
--, ... other columns
);
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23TiZk$RXHHA.4480@.TK2MSFTNGP04.phx.gbl...
> Drop the table and create it correctly?
> CREATE TABLE dbo.MyTable
> (
> IdentityColumn INT NOT NULL IDENTITY(1,1)
> --, ... other columns
> );
>
And people wonder why Celko hates IDENTITY so much. :-)

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Identity Column Null? WHAT?

Hey all
Wanted to check the identity of my column so I ran this:
dbcc checkident (tablename)
It came back saying it was null!!!
"Checking identity information: current identity value 'NULL', current
column value 'NULL'."
What gives? When I enter a value in the column...it starts with 0. I want it
to start with 1!!! Any suggestions? I already tried reseeding it and it stil
l
says null grrrr.
Thanks
Theresa> What gives? When I enter a value in the column...it starts with 0. I want
> it
> to start with 1!!! Any suggestions?
Drop the table and create it correctly?
CREATE TABLE dbo.MyTable
(
IdentityColumn INT NOT NULL IDENTITY(1,1)
--, ... other columns
);
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:%23TiZk$RXHHA.4480@.TK2MSFTNGP04.phx.gbl...
> Drop the table and create it correctly?
> CREATE TABLE dbo.MyTable
> (
> IdentityColumn INT NOT NULL IDENTITY(1,1)
> --, ... other columns
> );
>
And people wonder why Celko hates IDENTITY so much. :-)

> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Identity Column Null? WHAT?

Hey all
Wanted to check the identity of my column so I ran this:
dbcc checkident (tablename)
It came back saying it was null!!!
"Checking identity information: current identity value 'NULL', current
column value 'NULL'."
What gives? When I enter a value in the column...it starts with 0. I want it
to start with 1!!! Any suggestions? I already tried reseeding it and it still
says null grrrr.
Thanks
Theresa> What gives? When I enter a value in the column...it starts with 0. I want
> it
> to start with 1!!! Any suggestions?
Drop the table and create it correctly?
CREATE TABLE dbo.MyTable
(
IdentityColumn INT NOT NULL IDENTITY(1,1)
--, ... other columns
);
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23TiZk$RXHHA.4480@.TK2MSFTNGP04.phx.gbl...
>> What gives? When I enter a value in the column...it starts with 0. I want
>> it
>> to start with 1!!! Any suggestions?
> Drop the table and create it correctly?
> CREATE TABLE dbo.MyTable
> (
> IdentityColumn INT NOT NULL IDENTITY(1,1)
> --, ... other columns
> );
>
And people wonder why Celko hates IDENTITY so much. :-)
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
>
--
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com