Results 1 to 8 of 8

Thread: ***Resolved***resetting autonumber keys in MS SQL Server 7

  1. Dec 3rd, 2002, 05:10 AM #1
    beasty1711
    beasty1711 is offline

    Thread Starter
    Hyperactive Member beasty1711's Avatar
    Join Date
    Mar 2001
    Posts
    418

    ***Resolved***resetting autonumber keys in MS SQL Server 7

    Is it possible to reset the autonumber keys in the database.

    I have been using the table as a test... but now that its complete.. all my ref numbers start at like 1500 :|

    is there a way of resetting the counters... the tables are empty
    Last edited by beasty1711; Dec 4th, 2002 at 04:20 AM.
    "...They even have the internet on computers..." :- Homer Simpson

    "Second Place is First Looser" :- No Fear
    Reply With Quote Reply With Quote
  2. Dec 3rd, 2002, 07:07 AM #2
    PilgrimPete
    PilgrimPete is offline
    Frenzied Member PilgrimPete's Avatar
    Join Date
    Feb 2002
    Posts
    1,313
    Yes.

    The identity column should have a seed and an increment. You should be able to reset the seed to whatever you require. ( I can't test it here since I don't have SQL Server in front of me.)

    Alternatively, a TRUNCATE may reset the seed...
    Reply With Quote Reply With Quote
  3. Dec 3rd, 2002, 07:07 AM #3
    swatty
    swatty is offline
    Frenzied Member swatty's Avatar
    Join Date
    Aug 2002
    Location
    somewhere on earth
    Posts
    1,478
    Yep ucheck the mark identity

    fill in a new record with id 0
    reset the identity seed starting at 1
    delete the added record with id 0
    Code:
    If Question = Incomplete Then
   AnswerNextOne
Else
   ReplyIfKnown
End If
    cu Swatty
    Reply With Quote Reply With Quote
  4. Dec 3rd, 2002, 07:23 AM #4
    beasty1711
    beasty1711 is offline

    Thread Starter
    Hyperactive Member beasty1711's Avatar
    Join Date
    Mar 2001
    Posts
    418
    cool ta
    "...They even have the internet on computers..." :- Homer Simpson

    "Second Place is First Looser" :- No Fear
    Reply With Quote Reply With Quote
  6. Dec 3rd, 2002, 08:51 AM #5
    abhijit
    abhijit is offline
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,228

    Talking

    Here is one more way to reset the identity of the key column

    VB Code:
    1. dbcc checkident ('temptab',RESEED,15)

    2.     --temptab is the name of the table

    Cheers!
    Abhijit
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog
    Reply With Quote Reply With Quote
  7. Dec 3rd, 2002, 08:54 AM #6
    beasty1711
    beasty1711 is offline

    Thread Starter
    Hyperactive Member beasty1711's Avatar
    Join Date
    Mar 2001
    Posts
    418
    cool.. would i fire that in query analyzer etc?

    is the dbcc a sql reserved word?
    "...They even have the internet on computers..." :- Homer Simpson

    "Second Place is First Looser" :- No Fear
    Reply With Quote Reply With Quote
  8. Dec 3rd, 2002, 11:13 PM #7
    abhijit
    abhijit is offline
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,228

    Lightbulb

    Originally posted by beasty1711
    cool.. would i fire that in query analyzer etc?

    is the dbcc a sql reserved word?

    Dbcc is a command-line based utility. You can do a lot of things with it. For more on dbcc, refer to books online.

    Cheers!
    Abhijit
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog
    Reply With Quote Reply With Quote
  9. Dec 4th, 2002, 04:20 AM #8
    beasty1711
    beasty1711 is offline

    Thread Starter
    Hyperactive Member beasty1711's Avatar
    Join Date
    Mar 2001
    Posts
    418
    okie

    thanx
    "...They even have the internet on computers..." :- Homer Simpson

    "Second Place is First Looser" :- No Fear
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width