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
Aaaaa
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...
> 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 following
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
..... do
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 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.
Be sure to right-click on the table in the tree and choose REFRESH.
Roy Harvey
Beacon Falls, CT
|||Hi
I definately have refreshed each time. Still no joy. It seems to update
the table but not the design view.
A
Thanks
Aaaaa
"Roy Harvey" wrote:

> On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
> <Aaaaa@.discussions.microsoft.com> wrote:
>
> Be sure to right-click on the table in the tree and choose REFRESH.
> Roy Harvey
> Beacon Falls, CT
>
|||> Be sure to right-click on the table in the tree and choose REFRESH.
No, this really is a bug in the table designer. I can reproduce it in 2000,
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/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
(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
|||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 2000,
> 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/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=289675
> (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_value
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: