| Navigation: SQL Driver Strings(General) >====== BUSYHANDLING ====== |    |
DRIVER('SQLDriver', '/BUSYHANDLING = 1|2|3|4 ' )
[ Busy“ = ] SEND(file, '/ BUSYHANDLING [ = 1|2|3|4 ]' )
file{PROP:BusyHandling} = 1|2|3|4
Valid for all SQL drivers and ODBC except for Oracle.
BUSYHANDLING is used to set the strategy for handling busy connections with MSSQL and ODBC drivers. This setting is system wide, so once you set it (regardless of which table) it is set for all tables on all threads.
The BUSYHANDLING switch must be set PRIOR to opening any tables.
MSSQL/ODBC does not allow more than one statement per connection to be active at any one time. If you attempt to issue two statements on separate threads, MSSQL/ODBC will return an error message of ”[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt“.
There are three different strategies the ODBC and MSSQL drivers use to avoid this error:
1.Raise a separate connection per thread
2.Lock the connection while a statement operation is being processed
3.Retry when the error occurs.
The advantage of using one connection per thread is that once a thread is opened, no slowdown will occur. The disadvantages of using one connection per thread is that you cannot use connection dependent temporary tables. Also, the first time you have a new thread open the connection needs to be raised, which may be slow. The driver pools connections, so this slow down only occurs when you have more threads open than you previously had during the running of the application.
The advantages of locking connections is that you can use connection dependent temporary tables and starting new threads will not be slowed down. However, all statement operations will be slowed down a bit so as the lock can be obtained and released.
The advantages of looping when the busy error occurs are the same as for the locking strategy, plus you do not have the slow down caused by locking and releasing the connection. However, MSSQL does not return a unique error code for the busy error. So the driver is forced to use the error text to detect the busy error. This text will change depending on the language your user's MSSQL is set up to use. So you will need to either hope that your users are all using the English version of MSSQL or tell the driver what the error string is for each user.
To set which strategy the driver should use:
| 1 | indicates that the driver should do nothing about the error. This would be used when an application is only single threaded. |
| 2 | indicates to use the one connection per thread strategy.The BUSYHANDLING=2 setting forces multiple connections. Basically, if you want to use BUSYHANDLING=2 and MSSQL and transaction processing together, then you must keep a file open on the program's main thread. |
| 3 | indicates to use the retry on busy strategy. This is the default driver behavior. |
| 4 | indicates to use the connection locking strategy. |
See Also: BUSYMESSAGE, BUSYRETRIES