CODEWITHSUNDEEP
c#.netsqlsql-serversql-server-2008
urlreader
urlreader

Reputation: 6615

does it drop temp table (created in transaction) if rollback transaction in sql server?

In the transaction, I created some temp tables and inserted some data. if everything goes ok, the temp tables will be dropped and then the transaction will be committed. if there is exception, the connection will be close.

My question is: Do I have to rollback the transaction before close the connection? or, as long as the connection is closed, it is ok?

If I rollback, I assume the temp tables created in transaction will be dropped (rollback) too, correct?

thanks

Upvotes: 3

Views: 4893

Answers (2)

deeg
deeg

Reputation: 528

A temp table will be dropped when it's out of scope.

However losing a connection while having a transaction open creates an orphaned transaction, and any locks it holds will remain.

Using SET XACT_ABORT should rollback any transaction as soon as a run-time error is encountered.

http://msdn.microsoft.com/en-us/library/ms188792.aspx

Upvotes: 4

Brian P
Brian P

Reputation: 1587

The temp table will drop as soon as it is out of scope. Closing the connection puts it out of scope regardless of whether or not it rolls back.

Upvotes: 2

Related Questions