transaction gotcha

Posted: May 28, 2005 in Uncategorized

In my last post here yesterday i d posted on webservice automatic transaction and how it got failed the ACID test.Today i checked if the same is gtiign failed with com+ or not and luckily com+ passed the test.yeah the same code passed the ACID.

That is if there is an error the first insert to first is getting rolled back.Thus it looks like a lotcha with webservice automatic transaction.

tomorrow i ll post the result of the same uysing sp_addlinkserver and running 2 different server in same transaction.

note:-create the dll and regd in com+ and for the componet defined transaction aas requirenew



SqlConnection con;

SqlCommand cmd;

string strcon="constr1:first connstr to server1";

using (con=new SqlConnection(strcon))




new SqlCommand();



cmd.Connection =con;



//call webservice 2

//spawnservice.Execchild svc=new spawnservice.Execchild();


string strcon1="second constr to server2";

SqlConnection con1;

SqlCommand cmd1;

using (con1=new SqlConnection(strcon1))




new SqlCommand();



cmd1.Connection =con1;



return "success";


catch(Exception ex)


return ex.Message.ToString();



here goes the calling code:-



testreference.reftest test=

new reftest();

string s=test.enlisttran();



catch(Exception ex)




use sqlserver and u still get the same rolled back if error happened while executing the second command:-

EXEC sp_addlinkedserver  






begin tran


insert emp values(21,’shreeman21′,getdate(),1111,50)—local svr

if @@error<>0

rollback tran

insert addlnk_remsvr.test.dbo.tempemp values(1,’shreeman’)–remote svr

if @@error<>0

rollback tran


commit tran


Luckily or you can say unlucky i got the issue  (due to the sp2 pack and my remote server setting changed) .

if you look at the behavior it is not recommenede to use a distributed transaction using autotransaction features of webservices but i wonder why the same was  not raised before may be due to the fact that nobody might ve used may be i m wrong..if somebody can point i would be more happy.


Note:In connectionstring i tried enlist=true as well but with out luck.


As a final note few annoying msdtc errors i am fighting with and have gone thru few support articles:-



New transaction cannot enlist in the specified transaction coordinator.


The operation could not be performed because the OLE DB provider ‘SQLNCLI’ for linked server ‘addlnk_remsvr’ was unable to begin a distributed transaction.

The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction.


The transaction manager has disabled its support for remote/network transactions;EN-US;817064


Note:-The purpose of this note is to put a notice on the exceptional behavior of msdtc with webservice autotransaction.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s