Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    5

    Unhappy Unanswered: Reseed The Identity Column

    Hello there,
    I am having problem in modifying the identity column of a table.

    Currently the identity column is seeded by 1 with an increment of 1 and DATA with these properties of the column is stored.

    Now, I want to reseed the identity column with an increment of 2.

    The query I am using is

    ALTER TABLE XP ALTER COLUMN XP_ID BIGINT IDENTITY ( 500, 2 ) NOT NULL

    Is there any one out there who can help me?

  2. #2
    Join Date
    May 2002
    Posts
    299
    Take a look at DBCC CHECKIDENT() in sql book online.
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I have to ask why you want an increment of 2. There are a lot of things that CAN be done in SQL Server that probably SHOULDN'T be done in SQL Server...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Sep 2005
    Posts
    5

    Thanks

    Quote Originally Posted by oj_n
    Take a look at DBCC CHECKIDENT() in sql book online.
    Thank you very much.

    You have told me a nice thing but I reached there before you told me.

    Thanks for the solution.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Some government must want to put a tax on odd numbers, is all I can think of. The price of going international, I suppose ;-).

  6. #6
    Join Date
    Feb 2003
    Location
    Indian Head, MD
    Posts
    30

    Reset Identity From trigger

    I need to populate a temporary holding table from a trigger. The contents of this table will be added to another table when the Trigger has completed processing. The id column of the temporary table will be added to the max value in the final table to create the record identifier. I used an identity field in the temporary table to accomplish this. My initial idea was to create a temp table in the code for the trigger then drop the temp table when each recursion is complete. However I got "cannot use Create table in Trigger". Then I tried to make temp table in stored proc called by trigger. Same message. Then made permanent "temp" table, and tried to Trucate table after each use. I got "Cannot use Trucate Table in Trigger". Finally I used delete from and DBCC Checkident to reseed identity. This worked in Query analyzer. I worked in VB project using ADO. However, I after deployment I was informed that VB project using RDS to make and update ADO recordsets was failing to run the trigger. After troubleshooting I was alerted that DBCC CheckIdent can only be run by table owner. Business rules prevent this. Therefore I would like to know if anyone knows any other way to reseed an identity, before I abandon this approach and start over.

    Thanks for trying.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •