>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
Alan,
Before when I replied I didn't know your original problem and simply replied for that particular message. I still don't know it in its entirety. Just this piece looks weird.
Why are you inventing newid when SQL server has one NewID() or IDENTITY or Timestamp.
Cetin