Crazy Identity column

[Ruairi]

I have a table with 3 fields, the identity column and 2 that im placing values in. I bcp'd some data from the dos predecessor to the application i'm working on using the -E (explicit identity) switch. there were 21 records for this table in the old system, which were imported with identity values 1 - 21, so far so good. When i started testing the component that will work with this table i inserted 3 test colums, the identity field for the first of these inserts is 1145514556, so now the table looks like this

1, 1, AGM&M Inc.
.
.
.
21, 1, NONE
1145514556, 1, test1
1145514557, 1, test2
1145514558, 1, test3

what could be causing this? I have about 10 base tables in the system that are set up just like this one, recieved their initial data from a bcp import with explicit identities, and subsequently were inserted into by the same data access component that handles the inserts for this table (the insert procedures are general and take things like table name as parameters). In each of these cases the identity column keeps incrementing by one and acts as expected. Has anyone seen something like this?
TIA


Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications

 

[foxdev]

It sounds like you want to reseed the identity column. The only way I can recall to do this is to truncate the table, if that is practical for you.

Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Truncating will do the trick if it's a seed issue.

I dont know if this is an issue, but you should also make sure that you haven't exceeded the numeric range of the positive value for the number (integers are 2's compliment numbers, if you go beyond 2 billion for this data type, it will wrap to negative numbers). -1 is actually FFFFFFFF, -2 is FFFFFFFE, and so on. If you have a smallint data type, then this will wrap to negative numbers starting at 32768 (FFFF).. and so on...

Tom

 

[jballum]

To reseed the indentity column in a table you would use this:

DBCC CHECKIDENT (tablename, RESEED, 30)

Reseeds identity column (only 1 allowed per table so don't need to indicate which field it is) in table named 'tablename' to 30.

JB

 

[foxdev]

Thanks, JB. In my earlier response I was rummaging through the corners of what I call my brain for that, because I knew I had done it before, but couldn't recall how I did it. Since some of my tables had FK references I couldn't use Truncate Table, and had to use this instead.

Robert Bradley

http://www.foxdev.com/

 

[Ruairi]

Thanks for your responses. If i reseed the column as you suggest, will the rows already in the table keep their current values in their identity column? For new inserts the value of this column doesn't matter, but for the imported rows the value needs to be in sync with the record numbers in the dos program i am rewriting, where the imported data comes from.

thanks again

Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications

 

[foxdev]

Reseeding doesn't affect any current rows; it only changes the internal bucket for the given table (sort of like an Oracle Sequence, except that it is table-specific).

for the imported rows the value needs to be in sync with the record numbers in the dos program i am rewriting

Hmmm...little bells going off. Maybe you should be explicitly inserting these values, then.



Robert Bradley

http://www.foxdev.com/

 

[TomSark]

Yeah, I forgot about that one too...

Tom

 

[Ruairi]

I am explicitly inserting them (see the original post). As i said in that post, the record numbers(identity column) for those original rows are fine i just wanted to make sure reseeding the table wouldnt affect those rows.

Ruairi
ruairi@logsoftware.com
Experienced with:

VB6, SQL Server, QBASIC, C(unix), MS Office VBA solutions

ALSO: Machine Control/Automation using GE and Omron PLC's and HMI(human machine interface) for industrial applications