I’m using the Xojo ODBCDatabase to connect to SQL Server 2008 R2. I have put a lock timeout on a SQL statement and wrapped my statement in an error trap. When the lock timeout occurs, the error trap is invoked and the error is raised. Here is the SQL:
SET LOCK_TIMEOUT 3000
BEGIN TRY
SET NOCOUNT ON
SELECT *
FROM zzTest
END TRY
BEGIN CATCH
DECLARE @Errmsg nvarchar (4000)
SELECT @Errmsg=CONVERT(nvarchar(5),ERROR_NUMBER()) + ‘:’ + ERROR_MESSAGE ()
RAISERROR (@Errmsg, 16, 1)
END CATCH
I lock the table I’m querying by starting a transaction in a separate window and doing an INSERT statement but not committing the transaction.
If I then run my query window in SQL management studio, I get the following message as expected:
Msg 50000, Level 16, State 1, Line 11
1222:Lock request time out period exceeded.
However, if I run this query in Xojo, it waits for the Lock Timeout to occur, but then no error is returned. On the ODBCDatabase object, Error = false, ErrorCode = 0, ErrorMessage = . Also, data is returned in the recordset
The connection is set to Read Committed so the query does wait for the timeout to occur.
I’m struggling! Can anyone help?
Thanks in advance