Invalid cursor state

Question

Invalid cursor state

thbaig

Hall of Fame

Points: 3003
More actions
September 21, 2021 at 11:59 am

#3931416

HI,
Version : Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 (X64)
I have a real basic stored proc that inset data to a table. application is calling it via ODBC. Sometime application receive following error sometime not.
SQL Error:
The statement could not be executed directly - [Microsoft][ODBC SQL Server Driver]Invalid cursor state
I had added SET nocount on in proc but error remain same. dev added open and close conn/cursor before and after that call but it not work. will it work if I add hints like READPAST etc ? or what should I do to get rid of this error
==== SP code
ALTER PROCEDURE [dbo].[spwoInsert]
(
@RELEASED varchar(1),
@USERNAME varchar(28),
@DEPARTMENT varchar(6),
@EMAIL_ADDRESS varchar(105),
@CONTACT varchar(28),
@CUSTOMER_PO varchar(28),
@CUSTOMER_NO varchar(9),
@WEB_REFERENCE_NO int,
@NOTE varchar(1152),
@AE_EMAIL varchar(150),
@WEB_HOLD varchar(1),
@OrderChannel varchar(20),
@SFCaseNo varchar(15),
@SFCaseRecordID varchar(18)
)
AS
BEGIN
declare @ttime datetime;
set @ttime = cast( ('1900-01-01'+' '+CONVERT(VARCHAR(8),GETDATE(),108)) as datetime)
INSERT INTO WEB_ORDERS (ORDER_DATE
,ORDER_TIME
,RELEASED
,USERNAME
,DEPARTMENT
,EMAIL_ADDRESS
,CONTACT
,CUSTOMER_PO
,CUSTOMER_NO
,WEB_REFERENCE_NO
,NOTE
,AE_EMAIL
,WEB_HOLD
,OrderChannel
,SFCaseNo
,SFCaseRecordID)
VALUES (
cast(getdate() as date),
@ttime,
@RELEASED ,
@USERNAME ,
@DEPARTMENT ,
@EMAIL_ADDRESS ,
@CONTACT ,
@CUSTOMER_PO ,
@CUSTOMER_NO ,
@WEB_REFERENCE_NO ,
@NOTE ,
@AE_EMAIL ,
@WEB_HOLD ,
@OrderChannel ,
@SFCaseNo ,
@SFCaseRecordID
)
END

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Where is the CURSOR?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

thbaig Hall of Fame Points: 3003 More actions · Answer 2

I have not defined any cursor. But may be it is throwing error due to implicit cursors by rdbms , that is the reason for suggestion about SET NOCOunt ON 🙁

This reply was modified 3 years, 2 months ago by thbaig.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Ok. Is the app calling the proc in a loop?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ratbak Ten Centuries Points: 1358 More actions · Answer 4

Is there an insert trigger on WEB_ORDERS? Cursor could be lurking there.

thbaig Hall of Fame Points: 3003 More actions · Answer 5

no , app is not calling it in loop, but this could call several times in a sec

thbaig Hall of Fame Points: 3003 More actions · Answer 6

No trigger on this table

ratbak wrote:

Is there an insert trigger on WEB_ORDERS? Cursor could be lurking there.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

It's just an INSERT for C.R.U.D. I suspect that the inset is sometimes being blocked or failing DRI (FK's) or some other constraint and it's simply not being reported. The "cursor" is behind the scenes where the driver creates a cursor behind the scenes and any constraint or other failure may return the failure simply as the cursor not committing and so being in an invalid state.

I haven't worked on any front end code for almost 2 decades and so that's pretty much the extent (pun intended) of the help that I can offer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thbaig Hall of Fame Points: 3003 More actions · Answer 8

This table has no FK constraint , as this is base table and having PK only

Jeff Moden wrote:

It's just an INSERT for C.R.U.D. I suspect that the inset is sometimes being blocked or failing DRI (FK's) or some other constraint and it's simply not being reported. The "cursor" is behind the scenes where the driver creates a cursor behind the scenes and any constraint or other failure may return the failure simply as the cursor not committing and so being in an invalid state.
I haven't worked on any front end code for almost 2 decades and so that's pretty much the extent (pun intended) of the help that I can offer.

ratbak Ten Centuries Points: 1358 More actions · Answer 9

What type of application? MS Access by chance? I do find references to such a bug in Access -- e.g., https://social.msdn.microsoft.com/Forums/sqlserver/en-US/33a48549-aaa0-4301-a73c-3714ea41efb5/odbc-all-of-a-sudden-not-working-invalid-cursor-state?forum=sqldataaccess

If not, what version of ODBC?

Was there a recent Windows Update on the application server?

Any changes in the network?

thbaig Hall of Fame Points: 3003 More actions · Answer 10

This part of application is developed in Omnis

I need to confirm ODBC version, Yes windows server upgraded some time back

ratbak wrote:

What type of application? MS Access by chance? I do find references to such a bug in Access -- e.g., https://social.msdn.microsoft.com/Forums/sqlserver/en-US/33a48549-aaa0-4301-a73c-3714ea41efb5/odbc-all-of-a-sudden-not-working-invalid-cursor-state?forum=sqldataaccess
If not, what version of ODBC? Was there a recent Windows Update on the application server? Any changes in the network?

This reply was modified 3 years, 2 months ago by thbaig.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

Can you post the part of the code which sets up the connection and calls the proc? I know nothing about Omnis, but presumably it's not too alien.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 12

This is definitely in the driver or front end that the cursor is being set up. Posting that code may help.

thbaig Hall of Fame Points: 3003 More actions · Answer 13

Dev said he used default settings. I got following on login Audit (profiler), not sure if this can help

-- network protocol: TCP/IP

set quoted_identifier on

set arithabort off

set numeric_roundabort off

set ansi_warnings on

set ansi_padding on

set ansi_nulls on

set concat_null_yields_null on

set cursor_close_on_commit off

set implicit_transactions off

set language us_english

set dateformat mdy

set datefirst 7

set transaction isolation level read committed

Phil Parkin wrote:

Can you post the part of the code which sets up the connection and calls the proc? I know nothing about Omnis, but presumably it's not too alien.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 14

Nothing in the code you posted shows how the proc is called, how the connection is opened or how the connection is closed. It may be helpful to see that.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin