>Many thanks to all who have helped me with this problem. I think I've nearly solved it.
>
>To minimise SQLEXEC calls I now pass a batch of SQL commands to the back end (ie. has the same effect as a stored procedure) as follows...
>
>
>FUNCTION NewId
>LPARAMETER tcIdField
>
>LOCAL lcFieldName, lnConnHandle, lnNewId, lnOrigArea, lnRetVal
>lcFieldName = UPPER(tcIdField)
>lnOrigArea = SELECT(0)
>lnConnHandle = DefaultConnection()
>
>IF lnConnHandle<0
> RETURN -1
>ENDIF
>
>TEXT TO lcSQLCmd TEXTMERGE NOSHOW
> DECLARE @newId INT
>
> WHILE (1=1)
> BEGIN
> UPDATE UniqueId
> SET @newId=nLastNumber+1, nLastNumber=nLastNumber+1
> WHERE UPPER(cFieldName)='<<lcFieldName>>'
>
> IF @@ROWCOUNT>0
> BEGIN
> BREAK
> END
> END
>
> SELECT @newId AS nNewNumber
>ENDTEXT
>
>=SQLEXEC(lnConnHandle,lcSQLCmd,'NewIdTemp')
>
>=SQLDISCONNECT(lnConnHandle)
>SELECT (lnOrigArea)
>
>RETURN NewIdTemp.nNewNumber
>
>
>This is now very fast, but when I test the function in a tight loop to add 10000 new ids, SQL gives-up after about 4000 additions, with the error message...
>
>Connection failed:
>SQLState: '01000'
>SQL server Error: 52
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]Connection Open
>(Connect())
>Connection failed:
>SQLState: '08001'
>SQL server Error: 17
>[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist
>or access denied
>
>After pressing OK the process asks me for SQL login details (how can it 'forget' them?) then continues to add another 4000 or so records before crashing again!
>
>The SQLStates in question are 01000 General warning, 08001 Unable to connect to data source. Does anyone know of a good resource listing the SQL Server errors?
>
>Can anyone see anything wrong with my code, or have any other ideas what might be causing this problem?
>
>TIA,
>
>Alan
you want to complicate the simple things.
if you don't check the return value of SQLEXEC() the program cannot work to along
FUNCTION NewId
LPARAMETER tcIdField
LOCAL lcFieldName, lnConnHandle, lnNewId, lnOrigArea, lnRetVal
lcFieldName = UPPER(tcIdField)
lnOrigArea = SELECT(0)
lnConnHandle = DefaultConnection()
IF lnConnHandle<0
RETURN -1
ENDIF
TEXT TO lcSQLCmd TEXTMERGE NOSHOW
DECLARE @newId INT
SET LOCK_TIMEOUT 5000
UPDATE UniqueId
SET @newId= nLastNumber = nLastNumber+1
WHERE UPPER(cFieldName) ='<<lcFieldName>>'
SELECT @newId AS nNewNumber
ENDTEXT
PRIVATE nNewNumber
IF SQLEXEC(lnConnHandle,lcSQLCmd,'NewIdTemp')=1
nNewNumber = nNewNumber
USE
ELSE
nNewNumber = -1
ENDIF
=SQLDISCONNECT(lnConnHandle)
SELECT (lnOrigArea)
RETURN m.nNewNumber