Microsoft SQL Server Temporary tables

Database stored procedures frequently contain temporary tables that are used as repositories when accumulating rows during processing within the procedure. Since versions of SQL Server prior to SQL Server 2000 do not allow Data Definition Language (DDL) to be executed within the scope of a transaction, PowerBuilder provides the boolean AutoCommit property in the transaction object to allow you to handle these cases.

When AutoCommit is false (the default), normal transaction processing takes place: a BEGIN TRANSACTION is internally issued on a successful connect and this transaction is terminated by a COMMIT TRANSACTION or ROLLBACK TRANSACTION.

When AutoCommit is set to true, no transaction management is performed. Therefore, stored procedures that create temporary tables can be executed. This option should be used with great care because of the recovery implications. If AutoCommit is true, ROLLBACK cannot be issued.

See also

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

Microsoft SQL Server Performance and locking