Changing Identity Seed

Hi All,
I am using SQL Server 2000 and I am trying to change the identity seed in a
table. I want to change this via a script. I have used the following command
:
DBCC CHECKIDENT (dbo, RESEED, value)
where
dbo = the table in the database whose seed I want to change
value = the value I would like to change the value in Identity seed too.
I get the following result:
"Checking identity information: current identity value '100017140', current
column value '100017148'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Then when I go to the table --> right click --> desigh --> the identity seed
has not changed to what the message above has said it has changed too.
I have tried refreshing the table but still no luck.
Can anyone help. I am looking to have the value to be changed in the design
view for Identity seed.
Thanks.
A
Thanks
AaaaaAaaa
It works just fine (why do you call the table 'dbo'?)
create table test (c int not null identity(1,1))
go
insert into test default values
insert into test default values
insert into test default values
go
select * from test --we have 3 rows
/*
c
--
1
2
3
*/
go
dbcc checkident (test, RESEED, 1)
insert into test default values
insert into test default values
insert into test default values
select * from test
drop table test
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
> Hi All,
> I am using SQL Server 2000 and I am trying to change the identity seed in
> a
> table. I want to change this via a script. I have used the following
> command:
> DBCC CHECKIDENT (dbo, RESEED, value)
> where
> dbo = the table in the database whose seed I want to change
> value = the value I would like to change the value in Identity seed too.
> I get the following result:
> "Checking identity information: current identity value '100017140',
> current
> column value '100017148'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator."
> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
> Can anyone help. I am looking to have the value to be changed in the
> design
> view for Identity seed.
> Thanks.
> A
> Thanks
> Aaaaa|||Hi Uri
Thanks for responding. I only wrote dbo instead of test.
I created a test table like you suggested. Then when you right click on the
test table --> go to design --> at the bottom is a table where the followin
g
is written:
Columns
Description
...
..
Identity Yes
Identity Seed 1 --> this is what i
would like to
Idenitity Increment 1 change, but
the code does not
.... d
o
so.
....
I am new to SQL Server 2000 and this forum, I am not sure if I am typing or
checking something wrong.
Please bear with the little knowledge I have.
Thank you so much.
A
Thanks
Aaaaa
"Uri Dimant" wrote:

> Aaaa
> It works just fine (why do you call the table 'dbo'?)
> create table test (c int not null identity(1,1))
> go
> insert into test default values
> insert into test default values
> insert into test default values
> go
> select * from test --we have 3 rows
> /*
> c
> --
> 1
> 2
> 3
> */
> go
> dbcc checkident (test, RESEED, 1)
> insert into test default values
> insert into test default values
> insert into test default values
> select * from test
> drop table test
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:CB7CB183-88B8-462E-B696-CC0380A459F2@.microsoft.com...
>
>|||> Then when I go to the table --> right click --> desigh --> the identity
> seed
> has not changed to what the message above has said it has changed too.
> I have tried refreshing the table but still no luck.
Did you try inserting a row into the table? Did you try closing and
re-opening Management Studio?
Aaron Bertrand
SQL Server MVP|||> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
I inserted a new table, shut down the application and opened it up. When it
opened the table had been updated (test --> right click --> open table -->
return all rows)
But then when I check design --> column --> identity seed the value has not
changed.
This is where the problem lies.
A
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:

> Did you try inserting a row into the table? Did you try closing and
> re-opening Management Studio?
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
<Aaaaa@.discussions.microsoft.com> wrote:

>I inserted a new table, shut down the application and opened it up. When i
t
>opened the table had been updated (test --> right click --> open table -->
>return all rows)
>But then when I check design --> column --> identity seed the value has not
>changed.
>This is where the problem lies.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT|||Thanks Aaron
I have tried to run a trace via SQL Profiler though I am having some issues
with the parameters returning more indepth information on where (1,1) is
stored. Could you advise on what I could include with this for more
information?
Morning by the way (evening for me)
Thanks
Ads
Aaaaa
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, this really is a bug in the table designer. I can reproduce it in 200
0,
> 2005 and even in Katmai. There doesn't seem to be a way to make the table
> designer reflect numerous changes to the identity seed value...
> http://connect.microsoft.com/SQLSer...=289
675
> (The question, of course, is where on earth does SQL Server store the 1,1
> from initial creation? They must be coming from somewhere. Yes, I'm too
> lazy this morning to fire up profiler.)
> --
> Aaron Bertrand
> SQL Server MVP
>
>|||In SQL 2008, I picked this up from TSQL:StmtCompleted:
select col.name, col.column_id,
st.name as DT_name,
schema_name(st.schema_id) as DT_schema,
col.max_length, col.precision, col.scale, bt.name as BT_name,
col.collation_name, col.is_nullable, col.is_ansi_padded,
col.is_rowguidcol, col.is_identity,
case when(idc.column_id is null)
then null else CONVERT(nvarchar(40), idc.seed_value) end,
case when(idc.column_id is null) then null
else CONVERT(nvarchar(40), idc.increment_value) end,
CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
as IsIdNotForRepl,
col.is_replicated,
col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
is_FullTextCol,
col_name(col.object_id, ftc.type_column_id) FT_type_column,
ftc.language_id as FT_language_id,
case when(cmc.column_id is null) then null else cmc.definition end as
formular,
case when(cmc.column_id is null) then null else cmc.is_persisted end as
is_persisted,
defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
'IsDeterministic')
as IsDeterministic, xmlcoll.name as xmlSchema_name,
schema_name(xmlcoll.schema_id)
as xmlSchema_schema, col.is_xml_document from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id left
outer join
sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R' left outer join sys.objects dobj on
dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id left outer join
sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id
= col.column_id left outer join sys.computed_columns cmc on cmc.object_id =
col.object_id and cmc.column_id = col.column_id left outer join
sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
ftc.column_id = col.column_id left outer join sys.xml_schema_collections
xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
col.object_id = object_id(N'dbo.foo') order by col.column_id
Which I whittled down to:
SELECT name,seed_value,increment_value,last_val
ue
FROM sys.identity_columns
WHERE [object_id] = OBJECT_ID('dbo.foo');
The result was:
bar, 1, 1, 5
Notice that seed_value in sys.identity_columns has not changed, though
current_value has.
Aaron Bertrand
SQL Server MVP
"Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...[vbcol=seagreen]
> Thanks Aaron
> I have tried to run a trace via SQL Profiler though I am having some
> issues
> with the parameters returning more indepth information on where (1,1) is
> stored. Could you advise on what I could include with this for more
> information?
> Morning by the way (evening for me)
> Thanks
> Ads
> Aaaaa
> --
> Thanks
> Aaaaa
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||That was very well shrunk BUT i still failed miserably and this code did not
work. Too many errors,

> SELECT name,seed_value,increment_value,last_val
ue
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
The system did not like 'sys.identity_columns' for a start.
Sorry about bugging you on this. This bug is really bugging me.
Would the first line be formatted like this?
SELECT test,24,1,21
Ads
--
Thanks
Aaaaa
"Aaron Bertrand [SQL Server MVP]" wrote:

> In SQL 2008, I picked this up from TSQL:StmtCompleted:
> select col.name, col.column_id,
> st.name as DT_name,
> schema_name(st.schema_id) as DT_schema,
> col.max_length, col.precision, col.scale, bt.name as BT_name,
> col.collation_name, col.is_nullable, col.is_ansi_padded,
> col.is_rowguidcol, col.is_identity,
> case when(idc.column_id is null)
> then null else CONVERT(nvarchar(40), idc.seed_value) end,
> case when(idc.column_id is null) then null
> else CONVERT(nvarchar(40), idc.increment_value) end,
> CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end)
> as is_computed,
> convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl'))
> as IsIdNotForRepl,
> col.is_replicated,
> col.is_non_sql_subscribed, col.is_merge_published,
> col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name,
> schema_name(robj.schema_id) as Rul_schema, col.default_object_id,
> OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst,
> dobj.name as def_name, schema_name(dobj.schema_id) as def_schema,
> CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as
> is_FullTextCol,
> col_name(col.object_id, ftc.type_column_id) FT_type_column,
> ftc.language_id as FT_language_id,
> case when(cmc.column_id is null) then null else cmc.definition end as
> formular,
> case when(cmc.column_id is null) then null else cmc.is_persisted end as
> is_persisted,
> defCst.definition, COLUMNPROPERTY(col.object_id, col.name,
> 'IsDeterministic')
> as IsDeterministic, xmlcoll.name as xmlSchema_name,
> schema_name(xmlcoll.schema_id)
> as xmlSchema_schema, col.is_xml_document from sys.columns col
> left outer join sys.types st on st.user_type_id = col.user_type_id left
> outer join
> sys.types bt on bt.user_type_id = col.system_type_id
> left outer join sys.objects robj on robj.object_id = col.rule_object_id
> and robj.type = 'R' left outer join sys.objects dobj on
> dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join
> sys.default_constraints defCst on defCst.parent_object_id = col.object_id
> and defCst.parent_column_id = col.column_id left outer join
> sys.identity_columns idc on idc.object_id = col.object_id and idc.column_i
d
> = col.column_id left outer join sys.computed_columns cmc on cmc.object_id
=
> col.object_id and cmc.column_id = col.column_id left outer join
> sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and
> ftc.column_id = col.column_id left outer join sys.xml_schema_collections
> xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where
> col.object_id = object_id(N'dbo.foo') order by col.column_id
> Which I whittled down to:
> SELECT name,seed_value,increment_value,last_val
ue
> FROM sys.identity_columns
> WHERE [object_id] = OBJECT_ID('dbo.foo');
> The result was:
> bar, 1, 1, 5
> Notice that seed_value in sys.identity_columns has not changed, though
> current_value has.
> --
> Aaron Bertrand
> SQL Server MVP
>
>
> "Aaaaa" <Aaaaa@.discussions.microsoft.com> wrote in message
> news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@.microsoft.com...
>
>|||> That was very well shrunk BUT i still failed miserably and this code did
> not
> work. Too many errors,
I know, as I stated, this was for SQL 2005/2008, not for 2000. You will
have to turn Profiler on, including TSQL:StmtCompleted, and then open the
table in design view. There will be a bunch of statements there, and one of
them will include something about identity / seed.
Aaron Bertrand
SQL Server MVP