Invalid cursor state

  • 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

     

     

     

  • Where is the CURSOR?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • 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 1 month ago by  thbaig.
  • Ok. Is the app calling the proc in a loop?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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

  • No trigger on this table

    ratbak wrote:

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

  • 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)
    Intro to Tally Tables and Functions

  • 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.

  • 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 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 1 month ago by  thbaig.
  • 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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

  • 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.

  • 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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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