Error 20055 - ODBC

Edited text of question.

Adjusted points to 250

Adjusted points to 300

This ODBC error occurs when you're trying to execute a statement while the connection is still waiting/processing the results of a previous command. This would occur for example if some previously opened recordset hasn't been fully processed or wasn't explicitly closed, if a transaction is still waiting to be committed, or if some kind of deadlocking has occurred as a result of a previous operation. Please post your code, including whatever is before your INSERT, so that we can try to sort it out.

The problem arose when I had to change an Insert/Select statement into a while loop:

(example of Insert Select)

If NOT SqlPrepareAndExecute( p_hSql1,
                'INSERT INTO historic_transaction (
            effective_date,
            processing_date,
            product_id,
            action_id,
            batch_id,
            amount,
            reversal_id,
            receipt_required,
            cheque_payment,
            cheque_detail,
            withdrawal_charge,
            user_id,
            units
                                                 )
                  SELECT
            effective_date,
            processing_date,
            product_id,
            action_id,
            batch_id,
            amount,
            transaction_id,
            receipt_required,
            cheque_payment,
            cheque_detail,
            withdrawal_charge,
            user_id,
            units
            
        FROM daily_transaction (TABLOCKX HOLDLOCK)
                              )
              .... etc etc if fail

This code worked fine, then I changed it to Select/Loop/Insert  (Note the different Client side cursors - hSql and p_hSql1) :

If NOT SqlPrepareAndExecute( hSql,
                ' SELECT
            daily_transaction_id,
            effective_date,
            processing_date,
            product_id,
            action_id,
            batch_id,
            amount,
            transaction_id,
            comment_general,
            comment_start_date,
            comment_end_date,
            receipt_required,
            cheque_payment,
            cheque_detail,
            withdrawal_charge,
            user_id,
            units
            
                  FROM daily_transaction
            into
            :nDailyTransactionID,
            :dtEffectiveDate,
            :dtProcessingDate,
            :nProductID,
            :nActionID,
            :nBatchID,
            :nAmount,
            :nTransactionID,
            :sCommentGeneral,
            :dtCommentStartDate,
            :dtCommentEndDate,
            :nReceiptRequired,
            :nChequePayment,
            :sChequeDetail,
            :nWithdrawalCharge,
            :sUserID,
            :nUnits
                             ' )
      
            ..... etc etc if fail

     While SqlFetchNext( hSql, nRet )
      Set nIndex = nIndex + 1
      If NOT SqlPrepareAndExecute( p_hSql1,
                      'INSERT INTO                                             historic_transaction (
                  effective_date,
                  processing_date,
                  product_id,
                  action_id,
                  batch_id,
                  amount,
                  reversal_id,
                  receipt_required,
                  cheque_payment,
                  cheque_detail,
                  withdrawal_charge,
                  user_id,
                  units
                  ) values (
                  :dtEffectiveDate,
                  :dtProcessingDate,
                  :nProductID,
                  :nActionID,
                  :nBatchID,
                  :nAmount,
                  :nTransactionID,
                  :nReceiptRequired,
                  :nChequePayment,
                  :sChequeDetail,
                  :nWithdrawalCharge,
                  :sUserID,
                  :nUnits
                           ) '
                                   )
                             ...etc etc if fail

      Call SqlGetModifiedRows( p_hSql1, nRowNumber )
      If nRowNumber = 0
                        .. etc etc i


I have even created a seperate, independent handle for the Insert, which produced the same error message.

It appears to me that ODBC is getting confused with these client handles / cursors ???

Any help.

Thanks

Maybe also there's a problem with the Select into?

The primary key field, TRANSACTION_ID is an IDENTITY field while in older test database, which works, it's not ??? This seems the only differnce between these two databases?

I don't think there's anything wrong with your syntax or the fact that the primary key is an identity field. What's happening is that ODBC cannot process the INSERT while iterating over the cursor.

1. What was wrong with your first approach (INSERT INTO xxx SELECT ), which is functionnally equivalent and noticeably faster ?

2. If you cannot revert to the previous mechanism, can you do your processing in a stored procedure ?

Actually, My ODBC drivers is v 2.65.0213 .. I've been informed this is ancient. I'll try upgrading and see if that helps ... will respond soon.

Actually ODBC 3.0 doesn't upgrade the sqlServer element, so that didn't help. I've had to create a stored procedure to get around the problem (which I was trying to avoid to keep control in the frontend).

Something must be wrong with the interface between Centura front end and ODBC SqlServer drivers.

Thanks anyway wp, I send you 100 points or your help.

Grant.

looks like I cannot reduce points so what the hell, have 300.