Transactions

The START TRANSACTION statement is not supported outside of a stored procedure because the ODBC standard specifies that every statement is by default inside a transaction. The ODBC standard does not have an API to start a transaction. See START TRANSACTION in SQL Engine Reference.

ODBC provides for the application to decide if each SQL statement is in its own transaction or if the application will specify when each transaction is completed. ODBC automatically opens a transaction prior to any statement that is not in a transaction. Thus, with the first statement of a given connection, or with the first statement after a COMMIT or ROLLBACK, ODBC automatically starts a new transaction.

Within the ODBC standard, SQLSetConnectOption is used to specify whether each statement is in its own transaction or the application groups statements within a transaction.

Each statement is in its own transaction if SQLSetConnectOption is called specifying the option SQL_AUTOCOMMIT and the value SQL_AUTOCOMMIT_ON (this is the default). This usage means that a transaction is started at the beginning of executing a statement and the transaction is either automatically committed, if no error occurs, or rolled back, if error occurred, upon completion of statement execution.

The application can group statements in a transaction if SQLSetConnectOption is called specifying the option SQL_AUTOCOMMIT and the value SQL_AUTOCOMMIT_OFF value. This usage means that a transaction is started at the beginning of the first statement executed. The application then decides when and how to end the transaction by calling SQLTransact or executing a 'COMMIT WORK' or 'ROLLBACK WORK' statement. When the application ends one transaction, another transaction is automatically started on execution of the next statement.