Invalid cursor state, SQL state 24000 in SQLExecDirect

Question

I need to call two stored procedures in sequence via ODBC in PHP:

#run stored procedure 1
$query = "Shipped_Not_Shipped_Rep ".$_GET['rep_id'];
$result = odbc_exec($dbh, $query);
odbc_result_all($result);

#run stored procedure 2
$query = "Shipped_Not_Shipped_Account ".$_GET['account_id'];
$result = odbc_exec($dbh, $query);
odbc_result_all($result);

I'm getting this error in PHP after the second stored procedure call:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [unixODBC][FreeTDS][SQL Server]Invalid cursor state, SQL state 24000 in SQLExecDirect

If I re-arrange the order I call the stored procedures, it is always the second that errors. Is there a way to, idk, reset the cursor position between calls? A little out of my element here.

Answer 1

Something to try for people getting invalid cursor state with SQL server:

SET NOCOUNT ON;

At the top of your stored procedure or SQL script. Found here: https://social.msdn.microsoft.com/Forums/en-US/f872382a-b226-4186-83c7-0d0fcadcd3eb/invalid-cursor-state?forum=sqldataaccess I had this problem just running some very average SQL in SQL Server 2017

Answer 2

Open two handles to the database. ODBC probably maintains the cursor in the handle.

Answer 3

I ran into the same problem, but odbc_free_result($result) between the 2 queries did the job for me.

Documentation:

bool odbc_free_result ( resource $result_id )

Free resources associated with a result.

odbc_free_result() only needs to be called if you are worried about using too much memory while your script is running. All result memory will automatically be freed when the script is finished.

Note: If auto-commit is disabled (see odbc_autocommit()) and you call odbc_free_result() before committing, all pending transactions are rolled back.

Answer 4

I found the exact problem as well. Apparently this is common with the free ODBC drivers. This has been my morning headache from trying to migrate a project from MySQL to ODBC SQL Server. I finally found what got rid of this for me.

This error shows up because an active cursor still exists from the previous result set. I was able to get rid of this error without using the disconnect/reconnect method by ensuring I read through the entire first record set (even if only using a partial piece of it) before issuing a new one. Note: I'm using PHP.

Gives me an error:

$sql="SELECT COUNT(whatever) as whatever FROM whatever";<br />
$countResult = odbc_exec($db, $sql);<br />
$countMenuHeader = odbc_fetch_array($countResult);<br />
extract ($countMenuHeader);<br />
$countRecords = $NumMenuHeader;<br />

$sql="SELECT whatever as whatever FROM whatever";<br />
$result = odbc_exec($db, $sql);<br />
$MenuHeader = odbc_fetch_array($result);<br />

Cleared the error:

 $sql="SELECT COUNT(whatever) as whatever FROM whatever";<br />
 $countResult = odbc_exec($db, $sql);<br />

 while($countMenuHeader = odbc_fetch_array($countResult))<br />
 {<br />&nbsp;&nbsp;
 extract ($countMenuHeader);<br />&nbsp;&nbsp;
 $countRecords = $NumMenuHeader;<br />}

 $sql="SELECT whatever as whatever FROM whatever";<br />
 $result = odbc_exec($db, $sql);<br />
 $MenuHeader = odbc_fetch_array($result);<br />

In short, make sure you completely read or fetch the data set before moving to the next statement.