???How do I change Key ID Value???

[DefinityCorporation]

In our support system, the table which holds trouble tickets has a primary key column called "ID". This "ID" is the help desk trouble TICKET NUMBER. We do not want to start with "1", we have tried changing it to 20030001 but it remembers the previous value when we insert a new record.

How do we change it to start with 20030001 and then increment by 1???? Sorry if this is incomplete this is my first SQL experience. By default this column is type "int", length "4", precision "10", scale "0". Please help!!!

 

[ClaireHCM]

you could use

dbcc checkident(tablename,reseed,20030001)

This will ensure whatever inserted "after" this statement is going to be started from 20030001

so After this dbcc statement you need to truncate all the existing rows with id started from 1 and incremented by 1,and insert all the data which you want it to be started from 20030001 again.

 

[JamesLean]

ALternatively, if you haven't actually started using the table yet, ie there are no rows inserted, just change the seed of the identity column to 20030001.

--James

 

[DefinityCorporation]

Thanks guys! Just out of curiosity, when it says integer, length 4, that does not mean 0000 through 9999 does it? How many records will fit into this table, 16384? 16777216? billions? ps will starting the id at 20030001 cause any problems?

 

[SQLBill]

The length is the number of BYTES. Not the number of DIGITS. INTEGER can store WHOLE numbers from -2,147,483,648 to 2,147,483,647.

So, 20030001 is no problem.

-SQLBill

 

[DefinityCorporation]

IS this right? In Enterprise Man, I drill down to the table I want to modify, then right-click and chose "Design Table", then in the change "Identity" from "NO" to "Yes" and "Identity Seed" to 20030001, click save. This is what I get:
----------------------------------------------------------
Application Error
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for SQL Server
Description: Cannot insert explicit value for identity column in table 'problems' when IDENTITY_INSERT is set to OFF.
----------------------------------------------------------
Would clearing the table out first help?

 

[DefinityCorporation]

PS this is SQL 2000 ENT on 2000 Server, IIS 5.0, sp3

 

[SQLBill]

I suggest looking up SET IDENTITY_INSERT in the Books OnLine.

Basically, if it is set to off you cannot insert explicit values into the identity field.

-SQLBill