none
ODBC Invalid cursor state second insert table has trigger RRS feed

  • Question

  • SQL Server 2005 (9.0.5000), latest version SQL Native Client 2005.90.5000.00 (ODBC) 64-bit app and server

    Inserting many rows into a table using a transaction from C++ client

    1 Turn off auto commit

    2 create statement for inserting into database (using bind parameters)

    3 execute insert (repeat) (using  SQLExecDirect or SQLExecute)

    Only the first insert succeeds when there is an insert trigger on the table inserted into. Subsequent inserts fail with this message

    SQL Error. Native Code: 0, SQLState: 24000, Return Code: -1
    [Microsoft][SQL Native Client]Invalid cursor state

    If I disable the trigger on the table then no error and all the inserts succeed. Trigger is used to log the insert into another table. Disabling the trigger is not acceptable because we want all inserts logged.

     

     

     


    Farid Zidan
    Zidsoft http://www.zidsoft.com/
    Monday, July 18, 2011 9:54 AM

Answers

  • Since you hace a trigger after insert, server will return extra response data token, you need to call SQLMoreResults after each insert to consume that response token, e.g. while(SQLMoreResults(hStmt) == SQL_SUCCESS);

     

     


    This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Farid Zidan Thursday, July 21, 2011 2:38 AM
    Thursday, July 21, 2011 12:27 AM

All replies

  • Hello,

    Please, could you have a look at these ( answered ) threads ?

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/f6466a82-caf7-4053-94a4-a6f756b2f5c6/

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/e46bebc0-17e0-4dd5-8376-572d9b4f8a95/

    If you could provide your "faulty" code, it may be useful for specialist of OBDC cursors to give you a quick answer.

    Have a nice day


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.
    Monday, July 18, 2011 12:05 PM
    Moderator
  • This appears to be a bug in SQL Native Client? My insert code does not change because the table has a trigger.

    Specifically, if I commit after each insert, then no error (obviously, this is not an acceptable solution since it degrades performance severely for large number of inserts).

    I can provide test tables and data if you want to reproduce this issue. The client application itself is available on the web and I can help you set it up to reproduce this issue.

    Again, everything works fine for inserting thousands of rows using the same code, the error happens only when the table being inserted into has an insert trigger in which case only the first insert succeeds (statement handle looks like is getting invalidated after the insert due to the existing table insert trigger). 

    Is there is an option in SQL Server regarding triggers and transaction behavior? Seems that the trigger is invalidating the client insert statement handle after the client insert is done if the client does not commit/rollback the transaction.

    Farid


    Farid Zidan
    Zidsoft http://www.zidsoft.com/
    Tuesday, July 19, 2011 12:59 AM
  • Since you hace a trigger after insert, server will return extra response data token, you need to call SQLMoreResults after each insert to consume that response token, e.g. while(SQLMoreResults(hStmt) == SQL_SUCCESS);

     

     


    This posting is provided "AS IS" with no warranties, and confers no rights
    • Marked as answer by Farid Zidan Thursday, July 21, 2011 2:38 AM
    Thursday, July 21, 2011 12:27 AM
  • Try adding SET NOCOUNT ON to the beginning of the trigger code to suppress the DONE_IN_PROC (row count) messages.

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Thursday, July 21, 2011 1:33 AM
    Moderator