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

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi Kevin...

Thank you and Bob for your responses; they've been very helpful.

There are a couple of points that I'm still a little confused about. First,
both you and Bob recommended against using ADO.net SqlTransaction and BEGIN
TRANSACTION together, but below you say that if you do it will behave as
nested... That doesn't sound so bad; is there something else wrong with it?

To clarify where I'm coming from a bit, we're adding a stored procedure to
do some Service Broker queuing but it has to work with a bunch of other
legacy stored procedures. The developers of the legacy stored procedures are
all very frightened of modifying them at all, so they want to use
SqlTransaction. *But* they're still using SQLServer 2000, so the service
broker sproc has to live on another machine and use DTC.

Just trying to figure out how to answer all masters here.

Thanks
-Mark


"Kevin Yu [MSFT]" wrote:

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: Transaction Error on None-Transaction Query
    ... These two threads are using the same connection ... Why do you commit the transaction immediately after it's created? ... Microsoft Online Community Support ... where an initial response from the community or a Microsoft Support ...
    (microsoft.public.dotnet.languages.csharp)
  • 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: 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)