Tuesday, November 24, 2009 3:19 PM tonyrogerson

Taking advantage of Table Variables NOT being transactional - application logging files within a transaction and keeping what's happened

Table Variables are not Transactional, that is to say BEGIN TRAN....ROLLBACK has no effect on them; we can use that to our advantage.

Have you ever been in the situation where you use a log file within your application to log progress, unfortunetly because your process is in a transaction you lose what has happened, well - not anymore - just use a table variable.... 

create table my_log_file (

    msg varchar(1024)

)

declare @t table ( msg varchar(1024) )

begin tran

--  some processing

--  ...

--  ...

--  logging as we go....

insert my_log_file ( msg ) values( '1' )
insert my_log_file ( msg ) values( '2' )
insert my_log_file ( msg ) values( '3' )
insert my_log_file ( msg ) values( '4' )
insert @t ( msg ) select msg from my_log_file

rollback

select * from my_log_file
select * from @t

Filed under: ,

Comments

# Twitter Trackbacks for Taking advantage of Table Variables NOT being transactional - application logging files within a transaction [sqlblogcasts.com] on Topsy.com

Pingback from  Twitter Trackbacks for                 Taking advantage of Table Variables NOT being transactional - application logging files within a transaction         [sqlblogcasts.com]        on Topsy.com

# Reflective Perspective - Chris Alcock » The Morning Brew #485

Pingback from  Reflective Perspective - Chris Alcock  » The Morning Brew #485

# re: Taking advantage of Table Variables NOT being transactional - application logging files within a transaction and keeping what's happened

Friday, November 27, 2009 11:25 AM by danh

Nice nugget, hadn't thought of using them for this.

# Handy SQL Server knowledge nuggets « C# Hacker – The Rambling Coder

Pingback from  Handy SQL Server knowledge nuggets «  C# Hacker – The Rambling Coder