473,883 Members | 1,622 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MSDTC and Delete transactions

Hello
I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.

However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@@ERROR value. There are just DELETE statement and a plain RETURN.

thanks
Sunit

Aug 28 '06 #1
1 2206
sjoshi (sj****@ingr.co m) writes:
I had a question reagrding MSDTC usage. I know that MSDTC will enlist
each connection transactions as it's own (take ownership) and
commit/abort accordingly.

However I'm not sure what would happen where I have a stored proc with
input parameters and 2 Delete commands on 2 different tables. In this
case, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2nd
aborts ? In the script for the Stored Proc, there is no checking for
@@ERROR value. There are just DELETE statement and a plain RETURN.
In a normal stored procedure if you have:

BEGIN TRANSACTION
DELETE tbl WHERE ....
DELETE tbl2 WHERE ....
COMMIT TRANSACTION

you could very well end up with only one of the DELETE statements
being carried out, becuase many errors in SQL Server aborts the
current statment only.

A distributed transaction may be different. At least in some
situations there is a requirement that SET XACT_ABORT is ON. With
this settings all errors but compile errors abort the batch and
rollback the transaction.

But exactly what happens, I will have to admit that I don't know. The
best advice I can give is to test a scenario where one of the DELETE
statement fails. No, wait, the best is to either add checks on @@error
or make sure XACT_ABORT is ON.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 28 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
1824
by: JIMMIE WHITAKER | last post by:
I'm using msde. On the little icon in task tray, you have under current service, a choice of mssqlserver, and another choice is msdtc. What is msdtc? And what is sqlserver agent?
0
361
by: | last post by:
Hopefully I have selected the appropriate news group for this question. If not I appologive in advance. I am currently experiencing the following error: Import of MSDTC transaction failed: Result Code = 0x8004d00e. It is occurring on a multi-tier app running locally on a Win XP Professional machine that has .Net + Sql Server 2000. There is no firewall and it is all local (no network involved). It is using the AutoComplete feature from ...
0
1084
by: Srini Eadha | last post by:
Hi, we are experiencing strange problem in ADO.NET assembly. we use DTC transactions for MSMQ and SQL server. just recently we are consistently(one or two times in a day) getting following exception and losing messages every time with this exception. "Import of MSDTC transaction failed: Result Code = 0x8004d00e.". we are running our application(C#) on windows 2000 and SQL server.
6
13977
by: cylt | last post by:
Hello, I'm using a TransactionScope with a ADO.NET SqlClient and i get the error : System.Runtime.InteropServices.COMException (0x8004D024): The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024). Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool. I've checked the configuration on both the client...
1
3412
by: Jimbo | last post by:
I keep getting this error on my application server...what does it mean? The description for Event ID ( 0 ) in Source ( ODBC ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Failed to enlist in DTC: SQL state 37000, native error 8501, error message MSDTC on server 'PROSQL' is unavailable.. ...
3
2937
by: =?Utf-8?B?YWJheWVy?= | last post by:
In short: I have an application using the CommittableTransaction class that works fine when run locally, but does not escalate to MSDTC when deployed to a Windows 2003 R2 server. More Specifically: I have a web service that saves data to a database. This service uses a CommittableTransaction to decide when to Commit or Abort the underlying
6
3052
by: =?Utf-8?B?ZXhl?= | last post by:
Hi, I'm using TransactionScope to do a serie Insert, Update, Delete operations with the ExecuteNonQuery and some Selects with ExecuteScalar everything works fine with multiple connections in the same scope. Problem is that when i do two Selects with ExecuteReader (to fill datasets) it shows the folowing error: Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the...
6
2015
by: =?Utf-8?B?UGF1bCBQcmV3ZXR0?= | last post by:
Hi - I have 4 webservers in my webfarm. All Win2k3 web edition. Before yesterday, none of them were service packed. I have now applied SP2 to two of them, and I'm getting a very weird MSDTC error on them now. The error occurs when I attempt a series of SQL statements wrapped in a TransactionScope(). It's executing against a different server, so this is where it's elevated to a distributed transaction.
2
4168
by: G.S. | last post by:
Is there a way to use transactions from within C# code without using MSDTC (client-and-server environment where the SQL server is behind a firewall)? Thank you
0
9942
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11148
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10419
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9577
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7974
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7133
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5801
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5994
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3236
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.