Microsoft SQL Server Using AutoCommit

Using AutoCommit

The setting of the AutoCommit property of the transaction object determines whether PowerBuilder issues SQL statements inside or outside the scope of a transaction. When AutoCommit is set to false or 0 (the default), SQL statements are issued inside the scope of a transaction. When you set AutoCommit to true or 1, SQL statements are issued outside the scope of a transaction.

Versions of SQL Server prior to SQL Server 2000 require you to execute Data Definition Language (DDL) statements outside the scope of a transaction. If you execute a database stored procedure that contains DDL statements within the scope of a transaction, an error message is returned and the DDL statements are rejected. When you use the transaction object to execute a database stored procedure that creates a temporary table, you do not want to associate the connection with a transaction.

To execute SQL Server stored procedures containing DDL statements in SQL Server 7 and earlier, you must set AutoCommit to true so PowerBuilder issues the statements outside the scope of a transaction. However, if AutoCommit is set to true, you cannot issue a ROLLBACK. Therefore, you should set AutoCommit back to false (the default) immediately after completing the DDL operation.

When you change the value of AutoCommit from false to true, PowerBuilder issues a COMMIT statement by default.

See also

Microsoft SQL Server Performance and locking

Microsoft SQL Server Temporary tables

Microsoft SQL Server Using CONNECT, COMMIT, DISCONNECT, and ROLLBACK