use tempdb
go
set nocount on
go

if object_id('T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B','U') is not null
	begin drop table T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B end
go
create table T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B
(id int not null primary key clustered)
go
-- Load test data
insert into T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B
select id = 2049034185

go
if object_id('tempdb..#t1','U') is not null
	begin drop table #t1 end
if object_id('tempdb..#t2','U') is not null
	begin drop table #t2 end
if object_id('tempdb..#t3','U') is not null
	begin drop table #t3 end
if object_id('tempdb..#t4','U') is not null
	begin drop table #t4 end
go
create table #t1 (id int not null primary key clustered, trancount int not null)
create table #t2 (id int not null primary key clustered, trancount int not null)
create table #t3 (id int not null primary key clustered, trancount int not null)
create table #t4 (
id int not null primary key clustered,
trancount_from_declared_table int not null,
trancount int not null
)
go

declare @d1 table (id int not null primary key clustered, trancount int not null)
declare @d2 table (id int not null primary key clustered, trancount int not null)

insert into #t1
select *, @@trancount from T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B

print 'select * from #t1 - Simple insert into temp table'
select * from #t1



insert into @d1
select *, @@trancount from T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B

print 'select * from @d1 - Simple insert into declared table'
select * from @d1


insert into #t2
execute ('
select *, @@trancount from T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B
')


print 'select * from #t2 - Insert into temp table from execute statement'
select * from #t2


insert into @d2
execute ('
select *, @@trancount from T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B
')

print 'select * from @d2 - Insert into declared table from execute statement'
select * from @d2


execute ('
insert into #t3
select *, @@trancount from T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B
')


print 'select * from #t3 - Insert into temp table inside execute statement'
select * from #t3


execute ('

declare @d4 table (id int not null primary key clustered, trancount int not null)


insert into @d4
select *, @@trancount from T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B


print ''select * from @d4 - Insert into declared table inside execute statement''

select * from @d4

insert into #t4
select *, @@trancount from @d4


print ''select * from #t4 - Insert into temp table from declared table inside execute statement''
select * from #t4

')


go
if object_id('T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B','U') is not null
	begin drop table T_TEMP_1DA72A14FFBC4E0BACB6B6950BC6652B end


/*

-- Sample Results

select * from #t1 - Simple insert into temp table
id          trancount   
----------- ----------- 
 2049034185           2 

select * from @d1 - Simple insert into declared table
id          trancount   
----------- ----------- 
 2049034185           0 

select * from #t2 - Insert into temp table from execute statement
id          trancount   
----------- ----------- 
 2049034185           1 

select * from @d2 - Insert into declared table from execute statement
id          trancount   
----------- ----------- 
 2049034185           1 

select * from #t3 - Insert into temp table inside execute statement
id          trancount   
----------- ----------- 
 2049034185           2 

select * from @d4 - Insert into declared table inside execute statement
id          trancount   
----------- ----------- 
 2049034185           0 

select * from #t4 - Insert into temp table from declared table inside execute statement
id          trancount_from_declared_table trancount   
----------- ----------------------------- ----------- 
 2049034185                             0           2 



*/

quote:

---

Originally posted by mfemenel

declare tables don't participate in transactions. See example.

DECLARE @MsgInfo as table(Msg varchar(1000))
Create Table #MsgInfo(Msg varchar(1000))

Begin Transaction
BEGIN TRY
Insert into @MsgInfo(Msg) values('About to update firstnames of A in table var')
Insert into #MsgInfo(Msg) values('About to update firstnames of A in temp table')
UPDATE CONTACT
SET FirstName='B.'
WHERE FirstName='A.'
RAISERROR('ERROR RAISED',16,1)

END TRY
BEGIN CATCH
ROLLBACK Transaction
SELECT * FROM @MsgInfo
SELECT * FROM #MsgInfo
END CATCH
Drop table #MsgInfo

Mike
"oh, that monkey is going to pay"

---

quote:

---

Originally posted by Peso

I think it's the EXECUTE itself that triggers a builtin transaction. The EXECUTE doesn't know beforehand what is going to be executed.

---

N 56°04'39.26"
E 12°55'05.63"

---

quote:

---

Originally posted by Kristen

"It appears that inserting into a declared table does not cause a transaction"

T'is documented in BoL (this from SQL 2000)

"because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks."

"unless you are inserting from an execute"

Interesting, didn't know you could do that (certainly couldn't in SQL 2000)

I wonder if I could, now in SQL2008, solve a problem that I couldn't find an easy way to do in SQL2000.

My Sprocs log that they have started and update that [log row] when they end.

A Rollback in an Sproc higher up the tree causes all child sproc logging to be rolled back - I just get the "error" from the higher Sproc in the log. Often would be handy to see what the child sprocs' would have logged.

I could copy the log data into a @TableVariable before the Rollback, and reinsert it (changing a column to indicate that the log entries were part of a rollback).

But [at the time, back in SQL 2000] I would have to have that code in all my Sprocs. I wanted to call an SProc that would preserve the log data via @TableVariable, do the rollback, and then re-insert the log data from the @TableVariable.

Here's the question I asked at the time. Didn't get much traction then - perhaps I built a rather complex example?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61277#212435

---

quote:

---

Originally posted by mfemenel

Why Rob, what wonderful presentation did you hear that from? ! ;~

---