Ideally I would prefer not to use my own key generator because I will need to make adjustments to my connected app that uses "Select @@Identity" to get the key back. Would my custom generator be able to set the @@identity value so my app would not need to change?

Assuming I cannot force MS SQL to not update the next seed value when I do an identity insert or just always use the next lowest value available (either of which would solve the problem), any ideas where would be a good place to look for a good integer key generator for MS SQL 2000?




>The Identity would be wrong in this case. You should use your own key generator.
>
>>I am working on a application that now requires off line clients to connect and sync up the data. I have a table that has an integer type identity column primary key.
>>
>>I want the traditional/connected application to use values less than 9999999 and the clients node to use a unique range of ids that are assigned to each client always above 9999999.
>>
>>The problem I have is when the client connects and inserts records with a primary key greater than 9999999 the next time the connected does an insert the identity value issued is above the 9999999 range and can conflict with a client generate id.
>>
>>I could use DBCC CHECKIDENT to reset the seed value after an off line client syncs their data. My concern with this approach is that it may not run quick enough to ensure no conflicts occur.
>>
>>

>>newseed = select MAX(idnum) from consumer where idnum < 9999999
>>DBCC CHECKIDENT (consumer, RESEED, newseed)
>>

>>Is there some way to force SQL Server to use the lowest available value for identity inserts or to stop the seed value from changing when I do an IDENTITY_INSERT? I would use GUIDs if this was a new app but as it stands now I am stuck with the integer primary key.