I use the following routine on SQL Server

create procedure get_id (@keytype varchar(20) ) 
as declare @keyvalue int 
begin tran 
	select keyvalue from keys (UPDLOCK) where key_type = @keytype 
	update keys set keyvalue = keyvalue + 1 
		where key_type = @keytype 
commit tran 
return

It is called with sqlexec([exec get_id 'INVOICE'],'cursorName')


>>Hi,
>>
>>I am developing a new app and am looking to the future and using local views so I can easily upsize to SQL later.
>>
>>I have a dbc containing VFP tables, and another dbc containing local views of the tables. I have one ID table containing all the unique ID's required within the system.
>>
>>I know how to lock and increment etc. in normal VFP, but keeping in mind the upsize, what is the correct way to get the next unique id from the ID file.
>>
>>Could I perhaps make a view of this file but turn buffering off and manually RLOCK or should I just include the table in the data environment of the form and manually lock (and will this scenario upsize correctly).
>>
>>I hope someone can help out.
>>
>>Regards
>>Doug Johnston
>
>Hello Doug,
>
> Using RLOCK() for VFP table to get the Id is the right way. Don't worry about SQL Server or any other RDBMS because they have their own internal locking mechanism. You just have to execute UPDATE ... statement on your Id table.
>
>Bye
>Jayesh