title: TransactionScope and Transactions
body: <p>In my C# code I am using TransactionScope because I was told not to rely that my sql programmers will always use transactions and we are responsible and yada yada.</p>
  
  <p>Having said that </p>
  
  <p>It looks like TransactionScope object Rolls back before the SqlTransaction?  Is that possible and if so what is the correct methodology for wrapping a TransactionScope in a transaction.</p>
  
  <p>Here is the sql test</p>
  
  <pre><code>CREATE PROC ThrowError
  AS
  
  BEGIN TRANSACTION --SqlTransaction
  SELECT 1/0
  
  IF @@ERROR&lt;&gt; 0
  BEGIN
    ROLLBACK TRANSACTION --SqlTransaction
    RETURN -1 
  END
  ELSE
  BEGIN
    COMMIT TRANSACTION --SqlTransaction
    RETURN 0
  END
  
  go
  
  DECLARE @RESULT INT
  
  EXEC @RESULT = ThrowError
  
  SELECT @RESULT
  </code></pre>
  
  <p>And if I run this I get just the divide by 0 and return -1 </p>
  
  <p>Call from the C# code I get an extra error message</p>
  
  <p>Divide by zero error encountered.<br>
  <b>Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION tatement is missing. Previous count = 1, current count = 0.</b></p>
  
  <p>If I give the sql transaction a name then </p>
  
  <p><b>Cannot roll back SqlTransaction. 
  No transaction or savepoint of that name was found. 
   Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK 
  TRANSACTION statement is missing. Previous count = 1, current count = 2.</b></p>
  
  <p>some times it seems the count goes up, until the app completely exits</p>
  
  <p>The c# is just</p>
  
  <pre><code>        using (TransactionScope scope = new TransactionScope())
          {
               ... Execute Sql 
  
               scope.Commit()
           }
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>The sql code has to work for 2000 and 2005</p>