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))

{

con.Open();

cmd=

new SqlCommand();

cmd.CommandType=CommandType.StoredProcedure;

cmd.CommandText="proc_tran";

cmd.Connection =con;

cmd.ExecuteNonQuery();

}

//call webservice 2

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

//svc.execute();

string strcon1="second constr to server2";

SqlConnection con1;

SqlCommand cmd1;

using (con1=new SqlConnection(strcon1))

{

con1.Open();

cmd1=

new SqlCommand();

cmd1.CommandType=CommandType.StoredProcedure;

cmd1.CommandText="child_proc";

cmd1.Connection =con1;

cmd1.ExecuteNonQuery();

}

return "success";

}

catch(Exception ex)

{

return ex.Message.ToString();

}

}

here goes the calling code:-

try

{

testreference.reftest test=

new reftest();

string s=test.enlisttran();

MessageBox.Show(s);

}

catch(Exception ex)

{

MessageBox.Show(ex.Message.ToString());

}

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

EXEC sp_addlinkedserver  

   @server=’addlnk_remsvr’,

   @srvproduct=”,

   @provider=’SQLNCLI’,

   @datasrc=’remsvrsystemname’

 

begin tran

 –SET XACT_ABORT ON

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

–SET  XACT_ABORT OFF

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

 

http://support.microsoft.com/default.aspx?scid=kb;EN-US;817064

http://support.microsoft.com/?kbid=873160

 

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

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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