"Connection is busy with results for another hstmt" is a common error when connecting to ODBC databases. It occurs because an OBDC connection can only have one active cursor at a time. By default, odbc only retrieves the first 20 records for a query. If the query contains more than 20 records, the rest are retrieved on demand. That's all very well, but when you open a second query using same connection/session you get the dreaded hstmt error.
I thought I had beaten it years ago, but my latest set of changes have resulted in the error reappearing.
There are a few possible solutions to this error:
- Use TTable components. These don't have the error as they open a new connection each time. Not a very pretty solution :(
- Put a FetchAll after each Open. This forces the retrieval of all records. Not practical in my case as I have around 250 tquery components to check.
- Use multiple connections. Also not practical for me.
- Replace the BDE with something else that doesn't have the problem. In progress but...
- Cheat. Set the odbc rowset size to a larger number. If you set it to 200, then the first 200 records will be returned. If you set it to -1 then ALL records will be returned. This could have dire effects on performance if your queries return a lot of records.
To set the rowset size with the BDE, do the following:
- Click on your TDatabase component
- In the object inspector, expand Params
- Put in a Key of "ROWSET SIZE"
- Put in the desired value
Update: As Otto has pointed out, the error can also be resolved, if you are using sql server 2005 or later, by using the SQL Native Client drivers. See Multiple Active Record Sets for more details. Doesn't help me though :(
11 comments:
When using BDE to connect to a MS SQL Server, via ODBC, this can (sometimes?) be solved by using SQL Native Client instead of SQL Server Driver for ODBC.
Thanks. That will work with sql 2005, but not with sql 2000.
See http://msdn.microsoft.com/en-us/library/ms345109.aspx
hmmmm... it's possible to install only SQL Native Client and connect to a an older SQL Server, and I had the impression that the support-staff at my company solved this problem for a customer by doing that
According to the MARS article, multiple active record sets are implemented in the database for sql server 2005.
For older versions of sql server, it is implemented in the oledb native driver by opening more connections. This may have side effects.
If you have sql server 2005+, the native driver is the way to go. For older versions of sql server, it may be worth trying too.
Thanks Sean. This saved my day.
It saved my day too, thanks a lot!
For me, the fetchall() approach is good, so far no huge tables.
Since this seems to be one of the first hits on Google when you look up this error message, I'll add the solution that worked for me here as well. :)
In my case the problem was caused because 2 datasets where creating cursors over the same database connection.
The fix in my case was to change the properties of the dataset I created myself to use clientside cursors (CursorLocation) and a static CursorType (I don't need to update the data using this dataset).
Thanks for the great advice Sean. I'm currently having this problem with a classic ASP application. How would I go about using option 5? (Increasing rowset size)
@David,
I am not sure how to do it with asp. Perhaps you can add Rowset to the connection string,
Hi, i have the same problem,in my app there are 6 database components to connect databse via dbe to MS Sql server, i tried with rowset size, 3 of my datbase component is working fine, but other 3 are still have the connection busy problem,
I tried with Sql native client also, it solve connection problme, but produces some other uncoverable errors.
Thanks man, you have helped me a lot with your post, I changed the config of Connection, the mode property, from fmOnDemand to fmAll, and it works fine.
Post a Comment