Node-mssql: "ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION" on date conversion error?

[derjust]

I've got the following code executing SQL - but sometimes it complains on missing begin transaction - for example if a invalid string is written to a date column:

INSERT INTO myTable (dateColumn) VALUES ('Invalid date')

Queries just hitting a constraint are properly handled and the rollback does not cause any problems.

    var transaction = new sql.Transaction(connection);
    logger.silly('Transaction Begin');
    transaction.begin(function(err) {
        if (err) {
            return callback(err);
        }
        logger.silly('Executing TX callback');
        request.query(sqlCommand, function(err, recordset) {
            if (err) {
                logger.silly('Transaction rollback');
                return transaction.rollback(function (innererr) {
                    if (innererr) {
                        //Here it is failing - innererr is set
                        logger.warn('Rollback failed: ', innererr);
                    }
                    return callback(err);
                });
            }

            logger.silly('Transaction commit');
            transaction.commit(function(err) {
                if (err) {
                    //little we can do here
                    return callback(err);
                }

                logger.silly('Leaving');
                return callback(undefined, recordset);
            });
        });
    });

The following error is raised:

{ [RequestError: Conversion failed when converting date and/or time from character string.]
  name: 'RequestError',
  message: 'Conversion failed when converting date and/or time from character string.',
  code: 'EREQUEST',
  precedingErrors: [] }

with this stacktrace

Rollback failed:  TransactionError: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
    at Request.userCallback (\node_modules\mssql\lib\tedious.js:426:21)
    at Request.callback (\node_modules\tedious\lib\request.js:30:27)
    at Connection.STATE.SENT_CLIENT_REQUEST.events.message (\node_modules\tedious\lib\connection.js:281:29)
    at Connection.dispatchEvent (\node_modules\tedious\lib\connection.js:732:59)
    at MessageIO.<anonymous> (\node_modules\tedious\lib\connection.js:665:22)
    at MessageIO.EventEmitter.emit (events.js:92:17)
    at MessageIO.eventData (\node_modules\tedious\lib\message-io.js:58:21)
    at Socket.<anonymous> (\node_modules\tedious\lib\message-io.js:3:59)
    at Socket.EventEmitter.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:745:14)
    at Socket.EventEmitter.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:407:10)
    at emitReadable (_stream_readable.js:403:5)
    at readableAddChunk (_stream_readable.js:165:9)
    at Socket.Readable.push (_stream_readable.js:127:10)
    at TCP.onread (net.js:528:21)

Any idea why this is happening? For the time being, it looks like it is not doing any harm but having those kind of errors in the logs isn't desirable.

[patriksimek]

I believe this is happening because of XACT_ABORT set to on. On the server, transaction is rolled back in case of error, but the driver doesn't know about that. Some changes needs to be done in both mssql and tedious to make this work correctly.

[patriksimek]

It's implemented and will be released in 2.0 in following days.

This example shows how you should correctly handle transaction errors when abortTransactionOnError (XACT_ABORT) is enabled.

var transaction = new sql.Transaction(/* [connection] */);
transaction.begin(function(err) {
    // ... error checks

    var rolledBack = false;

    transaction.on('rollback', function(aborted) {
        // emited with aborted === true

        rolledBack = true;
    });

    var request = new sql.Request(transaction);
    request.query('insert into mytable (bitcolumn) values (2)', function(err, recordset) {
        // insert should fail because of invalid value

        if (err) {
            if (!rolledBack) {
                transaction.rollback(function(err) {
                    // ... error checks
                });
            }
        } else {
            transaction.commit(function(err) {
                // ... error checks
            });
        }
    });
});