Hi Ric,

I am mainly an Oracle Guy and it would be easy to use Sequence in Oracle.
Don't know much about MS SQL anyway, check for something like SEquence in MSSQL.
Or Create a sequence like function returning a unique value:

I am enclosing a function in VFP / Try the same concept in MSSQL.

Function AutoGen
Para forwhom
If Used("Generator")
	Use In generator
Endif
Use generator In 0 Alias generator
Select generator
Set Order To Module
Seek Allt(forwhom)
if found()
     Repl generator.counter With generator.counter+1
     RetValue=generator.counter
     if used('Generator')
        Use In generator
     endif
     RetValue=Padl(Allt(Str(temp)),10,'0')
     Return RetValue
else
     *Error Statement
endif

Issuing an update will be like

ConnectHandle=SqlConnect("MSSQLDSNNAME","UN","PWD")
SQLSETPROP(ConnectHandle, 'Transactions', 2)
SQLSETPROP(ConnectHandle,"DispWarnings",.T.)

*Assuming they are numeric in nature

Sqlexec(ConnectHandle,"Update mySQLTable set lockkeycolumn = "+altrim(str(keycolumn))+" where custno = "+alltrim(str(mycustno)))

Sqlrollback(ConnectHandle)/SqlCommit(ConnectHandle)
Sqldisconnect(ConnectHandle)

>Hi Everyone,
>
>What is the best or most appropriate way to control record or file locking when using SQL Pass Through on a remote view, to insert transactions into a MS SQL database from my application. There is a table in SQL that I need to push recrods into, but am not sure how I should control the locking requirements to ensure I have the right transaction numbers in a number
>field. I might be adding 100 records, each needs the next transaction number, but how do I know someone else isn't doing the same from the main application if I can't lock the file termporarily?
>
>
>I am thinking something like normal scripting - based on teh keycolum, but not sure if the content or syntax is right:
>
>

begin transaction
>update mySQLTable
>set lockkeycolumn = keycolumn
>where custno = mycustno
>.....
>*-- do rollback or commit

>
>TIA
>
>Ric