I hate to give a reply and sound wishy-washy, but I know I had a similar situation several years ago and can't remember the details of the solution.

I was working with Great Plains accounting, that had a data store on SQL. I was writing some routines in Fox to update Great Plains from some vendor files we received.

I started out using a 1 one solution, as you've described, but I constantly ran into duplicate Id's. I believe that I had to put it in a loop. First do a select to get the nextid, then do an update to increment the nextid where the id was = to the id now stored in the variable. If nothing was updated, then I knew someone else had gotten in because the id had already been incremented.

I hope I'm remembering correctly.

PF

>We have a situation where we store a one column, one row entry called CurrTransNo. As the name implies, it stores the next transaction number to be used. This transaction number will be used across many different transaction tables, so it must be unique across all of the tables.
>
>We're trying to come up with the safest way to avoid any concurrency issues and to avoid any instance of duplicate transaction records. We have a stored procedure that increments the NextTransNo by one, and then returns the new number as a result, so it can be used, like so...
>
>

>
>DECLARE @nNextTransNo int
>
>UPDATE NextTransTable SET @nNextTransNo = CurrTransNo + 1, CurrTransNo = CurrTransNo + 1 FROM NextTransTable
>
>SELECT @nNextTransNo
>
>GO
>

>
>Short of implementing locks in SQL...since the setting of the variable and the updating of the record are done at what appears to be the same time - can anyone think of a better way to handle this?
>
>Thanks,
>Kevin