Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>This is our template (error logging removed)</p> <p>This is designed to handle</p> <ul> <li>Paul Randal's article <a href="http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(2630)-nested-transactions-are-real.aspx" rel="nofollow noreferrer">"No such thing as a nested transaction in SQL Server"</a></li> <li>Error 266</li> <li>Trigger Rollbacks</li> </ul> <p><strong>Explanations:</strong></p> <ul> <li><p>all TXN begin and commit/rollbacks must be paired so that <code>@@TRANCOUNT</code> is the same on entry and exit</p></li> <li><p>mismatches of <code>@@TRANCOUNT</code> cause error 266 because</p> <ul> <li><p><code>BEGIN TRAN</code> increments <code>@@TRANCOUNT</code></p></li> <li><p><code>COMMIT</code> decrements <code>@@TRANCOUNT</code></p></li> <li><p><code>ROLLBACK</code> returns <code>@@TRANCOUNT</code> to zero</p></li> </ul></li> <li><p>You can not decrement <code>@@TRANCOUNT</code> for the current scope<br> This is what you'd think is the "inner transaction"</p></li> <li><p><code>SET XACT_ABORT ON</code> suppresses error 266 caused by mismatched <code>@@TRANCOUNT</code><br> And also deals with issues like this <a href="https://dba.stackexchange.com/q/10912/630">"SQL Server Transaction Timeout"</a> on dba.se</p></li> <li><p>This allows for client side TXNs (like LINQ) A single stored procedure may be part of distributed or XA transaction, or simply one initiated in client code (say .net TransactionScope)</p></li> </ul> <p><strong>Usage:</strong></p> <ul> <li>Each stored proc must conform to the same template</li> </ul> <p><strong>Summary</strong></p> <ul> <li>So don't create more TXNs than you need</li> </ul> <p><strong>The code</strong></p> <pre><code>CREATE PROCEDURE [Name] AS SET XACT_ABORT, NOCOUNT ON DECLARE @starttrancount int BEGIN TRY SELECT @starttrancount = @@TRANCOUNT IF @starttrancount = 0 BEGIN TRANSACTION [...Perform work, call nested procedures...] IF @starttrancount = 0 COMMIT TRANSACTION END TRY BEGIN CATCH IF XACT_STATE() &lt;&gt; 0 AND @starttrancount = 0 ROLLBACK TRANSACTION; THROW; --before SQL Server 2012 use --RAISERROR [rethrow caught error using @ErrorNumber, @ErrorMessage, etc] END CATCH GO </code></pre> <p><strong>Notes:</strong></p> <ul> <li><p>The rollback check is actually redundant because of <code>SET XACT_ABORT ON</code>. However, it makes me feel better, looks odd without, and allows for situations where you don't want it on</p></li> <li><p><a href="https://stackoverflow.com/users/105929/remus-rusanu">Remus Rusanu</a> has a <a href="http://rusanu.com/2009/06/11/exception-handling-and-nested-transactions/" rel="nofollow noreferrer">similar shell</a> that uses save points. I prefer an atomic DB call and don't use partial updates like their article</p></li> </ul>
    singulars
    1. This table or related slice is empty.
    1. PONested stored procedures containing TRY CATCH ROLLBACK pattern?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USgbn
    1. USgbn
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PONested stored procedures containing TRY CATCH ROLLBACK pattern?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COSET NOCOUNT ON doesn't seem necessary?
      singulars
      1. PO
      1. USCarl R
    2. COYou should always use SET NOCOUNT ON imho.
      singulars
      1. PO
      1. USAaron Bertrand
    3. CO@Carl R: SET NOCOUNT ON is pretty much essential in code and triggers: so much client code breaks without it. Also, there it's possible to measure the time taken to process the "xx rows affected messages". See http://stackoverflow.com/q/1483732/27535 (my question) please
      singulars
      1. PO
      1. USgbn
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload