September 21, 2021 at 11:59 am
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
September 21, 2021 at 12:29 pm
Where is the CURSOR?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 21, 2021 at 1:17 pm
Ok. Is the app calling the proc in a loop?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 21, 2021 at 1:17 pm
Is there an insert trigger on WEB_ORDERS? Cursor could be lurking there.
September 21, 2021 at 1:48 pm
no , app is not calling it in loop, but this could call several times in a sec
September 21, 2021 at 1:54 pm
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
Change is inevitable... Change for the better is not.
September 21, 2021 at 2:08 pm
This table has no FK constraint , as this is base table and having PK only
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.
September 21, 2021 at 2:18 pm
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?
September 21, 2021 at 2:23 pm
This part of application is developed in Omnis
I need to confirm ODBC version, Yes windows server upgraded some time back
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?
September 21, 2021 at 2:30 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
September 21, 2021 at 2:39 pm
This is definitely in the driver or front end that the cursor is being set up. Posting that code may help.
September 21, 2021 at 3:57 pm
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
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.
September 21, 2021 at 4:30 pm
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
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply