Why do I receive "[Microsoft][ODBC SQL Server Driver] Connection is busy with results for another hstmt" error with the Database Toolbox?

I'm using a Microsoft ODBC Driver. When I try to execute multiple EXEC commands and create cursor objects, the first cursor object gets created but the subsequent cursor object creation fails:

%conn_odbc : connection to a ODBC datasource

conn_odbc=database('database_name','username',password)

%query :  Any select query

query = 'SELECT * from <table_name> '

%Cursor objects

cursor_odbc_first = exec(conn_odbc,query)

cursor_odbc_second= exec(conn_odbc,query)

As in the above example when I try to execute multiple SELECT queries using the EXEC command in Database Toolbox, the first cursor object is created, but the second cursor object creation fails with the following error message:

 “[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt”

Where as with JDBC driver the multiple cursor objects are created.

Answer 1

[MathWorks Support Team]

This error generally happens because an ODBC connection can only have one active cursor at a time.

When you use the EXEC command to select data from a table, a cursor object is created, if you don't fetch the data from this cursor and you try to execute another EXEC command using the same connection, you will get this error message.

To work around this issue, there are two possibilities:

1. Make sure to read the rest data from the pending result set before we send the next EXEC command:

cursor_odbc_first = exec(conn_odbc,query)

%fetch the data before the next exec call

cursor_data = fetch(cursor_odbc_first);

cursor_odbc_second = exec(conn_odbc,query)

2. Use the Multiple Active Result Sets (MARS) connection setting to enable multiple active result sets in a connection.

The following link on the MSDN support page provides additional information:

 <http://msdn2.microsoft.com/en-us/library/ms345109.aspx>