+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Changing Identity Seed

  1. 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

  2. Re: Changing Identity Seed

    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" 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




  3. Re: Changing Identity Seed

    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" 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

    >
    >
    >


  4. Re: Changing Identity Seed

    > 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



  5. Re: Changing Identity Seed

    > 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:

    > > 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
    >
    >
    >


  6. Re: Changing Identity Seed

    On Mon, 30 Jul 2007 06:36:01 -0700, Aaaaa
    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

  7. Re: Changing Identity Seed

    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
    > 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
    >


  8. Re: Changing Identity Seed

    > 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/SQLServ...dbackID=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




  9. Re: Changing Identity Seed

    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:

    > > 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/SQLServ...dbackID=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
    >
    >
    >
    >


  10. Re: Changing Identity Seed

    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" wrote in message
    news:4ECE6DDB-05C9-4DEE-8E39-ED16EE5D8820@microsoft.com...
    > 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:
    >
    >> > 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/SQLServ...dbackID=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
    >>
    >>
    >>
    >>




+ Reply to Thread
Page 1 of 2 1 2 LastLast