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