Skip to Content

ASE Feature: "enable select into in tran"

Feb 28 at 07:57 AM


avatar image


i came accross this new feature in ASE: "enable select into in tran". More info: Using select into in Multistatement Transactions

I tested this with a stored procedure (on ASE 16 SP03 PL03), but with no luck...

exec sp_configure "enable select into in tran", 1
create procedure lsp_test_tran
    select * into #t from master..sysmessages 
begin tran
exec lsp_test_tran

This is the output:

>[Error] Script lines: 11-14 ------------------------
 The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
 Msg: 2762, Level: 16, State: 3
 Server: LISSTAT, Procedure: lsp_test_tran, Line: 4 
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Pieter Coene Mar 01 at 01:24 PM

Hi Bart,

thx for the clarification. Regarding the KBA, nowadays, we always use the 'permanent table' solution. This leads to a lot of 'real' tables for temporary data. Thats the main reason i tested this feature. I hope this CR will be in the next release.


10 |10000 characters needed characters left characters exceeded
Bart Van Kuijk
Feb 28 at 02:53 PM

Hi Pieter

A change to my answer from yesterday. By sheer coincidence I happened to test this on an SP02 ASE that had the required config change to make this work.

Looking into this further, there is NO difference in behaviour between SP02and SP03.

The key here is that you need 'ddl in tran' database option set for your example to work. My SP02 ASE happened to have that ON, while SP03 did not (OFF is default for ddl in tran).

As your example does a select into #temp table, 'ddl in tran' needs to be ON in tempdb.

If you were to use select into a regular table, the database where that table will be created needs 'ddl in tran'. Of course it would need 'select into' ON too (which is ON by default on tempdb)

So, change this and your example will work:

use master
sp_dboption tempdb, "ddl in tran" , "true"

The comment on the 277 remains the same:

Running your repro steps, I also see error 277 returned :

Msg 277, Level 16, State 1:
Server 'WIN16', Procedure 'lsp_test_tran', Line 4:
There was a transaction active when exiting the stored procedure 'lsp_test_tran'. The temporary table '#t' was dropped in this transaction either explicitly or implicitly. This transaction has been aborted to prevent database corruption. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = 1, Current count = 0.

There is a feature open to request this 277 message be suppressed. The KBA needs a bit of clarification, I will get on that too.

KBA 2507907 - Msg 2762, Level 16, State 3 with temporary tables

Kind regards


10 |10000 characters needed characters left characters exceeded