Connection is Busy with results for another hstmt ERROR

Question

I am newbie. I have written most of my queries in PHP. I created a connection object and then hitting sql server.

function navigation($sel_subject, $sel_page,$filter=false) {

    $subject_set = get_all_subjects();
    $page_set = get_pages_for_subject($subject["id"],$sec_wp,$filter);
}

function get_all_subjects() {
    global $connection;
    $query = "  SELECT * FROM subjects ORDER BY position ";
    return odbc_exec($connection,$query);
}

Now i am updating few simple queries which just need to fetch the data from the database. I am upgrading them to stored procedures.

function get_all_subjects() {    global $connection;
    $query = "  EXEC get_all_subjects ";  
 return odbc_exec($connection,$query); }

I am getting an error message. Connection is Busy with results for another hstmt ERROR

I am using sql server 2005 and with ODBC connectors.

What is the best way to solve this issue. Or is it a better idea to ask admin if they can do something like http://sourceitsoftware.blogspot.com/2008/06/connection-is-busy-with-results-for.html Ottar Holstad said... 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.

I would like to know, it is it better to activate MARS(multiple active resultset). If so, please help me in providing a step-by-step procedure to enable the same.

Answer 1

You have to enable MARS(Multiple Active Resultset) which is disabled by default. You can avoid this error message by switching to 'SQL Native Client'. For more info check this

Answer 2

Can you try adding odbc_free_result calls when you're finished with the results of an odbc_exec?