RE: SqlTransaction, t-sql transactions, and distributed transactions

Hi Mark,

1) Yes, SQL Server supports nested transactions. The COMMIT statement will
only commits to the current level of transaction, however, ROLLBACK will
rollback all the transactions.

1a) In this case, you have to check the @@TRANCOUNT function to see whether
the transaction has been rolled back before. For more information on
@@TRANCOUNT, please check:

http://msdn2.microsoft.com/en-us/library/ms187967.aspx

2) It's not a good practice to mix SqlTransaction and BEGIN TRANSACTION
together. You can use either of them. But if you mix them, it will work as
nested.

3) The transaction will be promoted to the DTC Service, and the DTC service
will handle the distributed transaction.

4) If your comsumers will open the transaction in ADO.NET, you don't need
to use transaction in your stored procedures.

If anything is unclear, please feel free to let me know.

Kevin Yu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

(This posting is provided "AS IS", with no warranties, and confers no
rights.)

.

Relevant Pages

  • Re: NT: son of VMS? (was Re: Portents of VMS death)
    ... you can also perform updates using a transaction log, ... multiple bucket updates into a single disk access (which makes the presence ... I think I'd get rid of RFA mechanisms in indexed files: ... Some kind of relatively simple support for 1:many parent:child relationships ...
    (comp.os.vms)
  • Re: may Janets innocent voltage connects, Hakim facilitates let alone able, insufficient south-e
    ... Her transaction was ... green, appalling, and cheers regarding the organization. ... Agha, have a ... They are contacting in spite of improved, in support of vulnerable, ...
    (sci.crypt)
  • Re: Using Transaction Scope
    ... WinForms application and within this transaction scope you pass multiple ... SQL statements to the CommandText property of the SqlCommand object to ... Microsoft Online Community Support ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: SqlTransaction, t-sql transactions, and distributed transactio
    ... TRANSACTION together, but below you say that if you do it will behave as ... Microsoft Online Community Support ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.data.ado)
  • Re: Dec RDB V6.1-14 under VMS 6.2
    ... Surely with those outdated versions and no support ... If your error-handling code is rolling back the transaction so as ... people simply ignore this error :-( Often, they're using a specific database ... > interested in helping me (I know we don't have a support contract but I ...
    (comp.os.vms)