ansaurus

Question

Answer 1

+2 A:

SQL Server as a whole does not support nested transactions. In T-SQL you can issue a BEGIN TRAN inside an earlier BEGIN TRAN but this is just for convienience, it's only the outer transaction that counts. the .NET client for SQL Server (SqlConnection) does not even allow you to do that and throws this exception when you try.

Daniel Renshaw 2010-05-18 20:28:52

My host says they offer 'Microsoft SQL 2008'. Is this T-SQL? If not where does T-SQL come from? I only want the outer transaction to count. Does 'Microsoft SQL 2008' not allow this? (maybe to configure it not to throw an exception?)

acidzombie24 2010-05-19 08:11:56

T-SQL is Microsoft's variant of the SQL standard that runs in all versions of SQL Server. If you want to avoid this exception, simply avoid calling `BeginTransaction` before committing or rolling back an earlier transaction - you can't being a transaction if an earlier transaction is still pending. I recommend you take a look at using TransactionScope (http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope(VS.80).aspx) instead of trying to manage them yourself.

Daniel Renshaw 2010-05-19 08:21:48

hmm, thats not the same thing. This code was used with sqlite and i am unsure if that is supported with it (it could be since it seems to support all of ado.net). But this sounds strange, if T-SQL is in all versions of sql server then shouldnt i have that and shouldnt this be allowed? That one feature will make my life easier. Even though theres only one function that uses it (i guess i could destroy it since its only on init where only one connection is used until done.)

acidzombie24 2010-05-19 08:31:02

T-SQL is the SQL language dialect of SQL Server. SqlConnection issues commands to SQL Server in T-SQL but even though T-SQL allows you to (superficially) nest transactions, SqlConnection does *not* allow it. You could issue a `BEGIN TRAN` command yoursef via a SqlCommand but then you'd be bypassing all of .NETs support for transactions.

Daniel Renshaw 2010-05-19 08:35:59

Hmm. Good to know. Thanks, it looks like the easiest way to solve this is to kill the outer transaction since it is only used once and was there to get better performance in sqlite (its only runs once so it isnt much of a boost).

acidzombie24 2010-05-19 08:50:35

ansaurus

tags:

views:

answers:

How do i enable nested transactions

related questions