New post
Question
· Sep 23, 2020

ODBC Invalid cursor state Error

#Business Operation #SQL #Ensemble

We use ExecureProcedure() to execute a stored procedure which returns a result set. But we see lot of "Invalid cursor state" errors when the result set is empty.  The connection to SQL server database is made through ODBC. 

ERROR #6022: Gateway failed: Fetch. + ERROR <Ens>ErrGeneral: SQLState: (24000) NativeError: [0] Message: [Microsoft][ODBC Driver 11 for SQL Server]Invalid cursor state 

Here is the code snippet from the business operation class which uses  EnsLib.SQL.OutboundAdapter  

do ..Adapter.DSNSet(DataSourceName)

set sp = "{call sqlstoredprocedure(?,?,?)}"
set tSC = ..Adapter.ExecuteProcedure(.pResults, .out, sp, "iii", p1,p2,p3)

Looking at the ODBC documentation, error 24000 means "A cursor is already opened on the statement handle." 

There is no error when we execute the proc directly on SQL Server. Did anyone come across this issue?

Please provide your suggestion to get this fixed.  

Discussion (2)1
Log in or sign up to continue
Alexander Koblov · Nov 6, 2020

Hi Ramesh.

"Invalid cursor state" is an error returned by ODBC driver. Ensemble just shows this error to you.

It's expected that you don't get this error when you run the proc directly on SQL Server -- because there is no ODBC driver involved in this case.

What you can try is to run the same query from some other ODBC tool (e.g. WinSQL) and see if you are getting the same error message.

If you see the same problem -- the likely the issue is within ODBC-driver+SQL Server. If you don't see the same problem then indeed, something might require changing on Ensemble side.

Also -- try googling this error message "Invalid cursor state". As this error message comes from Microsoft ODBC driver there are perhaps discussions on Microsoft sites. For example, I found this one
https://social.msdn.microsoft.com/Forums/en-US/f6466a82-caf7-4053-94a4-a...

0 0
Omprakash Sharma · Nov 18, 2020

Hi Ramesh,

There is no direct way to overcome this error. The best possible way is to catch the tSC error and set it to Ok

do ..Adapter.DSNSet(DataSourceName)

set sp = "{call sqlstoredprocedure(?,?,?)}"
set tSC = ..Adapter.ExecuteProcedure(.pResults, .out, sp, "iii", p1,p2,p3) 

set errorText = $System.Status.GetErrorText(tSC)

if (errorText [ "Invalid cursor state")
{
set tSC = $$$OK
} 

0 1