ASE Feature: "enable select into in tran"

[former_member1223811]

Hi,

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
go
create procedure lsp_test_tran
as
begin
    select * into #t from master..sysmessages 
end
go
begin tran
exec lsp_test_tran
rollback
go

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 

[former_member1223811 · Accepted solution]

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.

Greetz
Pieter

[bart_van_kuijk]

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
go
sp_dboption tempdb, "ddl in tran" , "true"
go

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

Bart