Invalid cursor state, SQL state 24000 in SQLExecDirect
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
Open two handles to the database. ODBC probably maintains the cursor in the handle.
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 />
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 />
In short, make sure you completely read or fetch the data set before moving to the next statement.