Solved Problem: ODBC Error: "Connection is busy with results for another hstmt"

This error has bugged me for a while using Microsoft SQL with Delphi through an ODBC connection. OBDC can only have one active cursor at a time. By default, odbc only retrieves the first 20 records for a query. This error occurs if a query contains more than 20 records which are usually retrieved on demand. This is fine until a second query is executed using same connection/session, the error then pops-out.

I searched and bumped with MARS (Multiple Active Record Sets) articles and solutions but I found an easier one.

Solution:
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 is not a good practice and performance will suffer if a query result set is large.

To set the rowset size with the BDE, do the following:

1. Click on your TDatabase component
2. In the object inspector, expand Params
3. Put in a Key of "ROWSET SIZE"
4. Put in the desired value

Changing the rowset size should work with other odbc connection components as well.

Thanks to Sean's post.