>I have a table in our SQL Server database which holds the next available key for each table. When retrieving the next key I call the function below. It worked great while I was the only one logged in. Now we have testers in the system and I am receiving Table Update errors. It's my understanding that remote views can only be optimistically locked. Is that true? How could I do a SEEK...RLOCK...REPLACE...UNLOCK?
>
>tia
>
>
>
>function GetNextKey
>
>lparameter cTable
>
>local nRetVal
>
>nRetVal = 0
>
>select NEXTKEY ;
> from tls_KeyCollection ;
> where upper(TABLENAME) == upper(cTable) ;
> into cursor C_KEY
>
>if _tally = 1
> update tls_KeyCollection ;
> set NEXTKEY = C_KEY.NEXTKEY + 1 ;
> where upper(TABLENAME) == upper(cTable)
>
> nRetVal = C_KEY.NEXTKEY
>endif
>
>use in C_KEY
>
>return nRetVal
Row locking in SQL 6.5 not available, however it will be available in SQL 7.0
I suggest that you add (NOLOCK) to your select statement.
select NEXTKEY ;
from tls_KeyCollection (NOLOCK);
where upper(TABLENAME) == upper(cTable) ;
into cursor C_KEY