Problem with transactions

  • Thread starter Thread starter WhiteEagl
  • Start date Start date
W

WhiteEagl

Hi,

I have an application that uses a web service to access a
SQLServer database. Theses 3 elements are on seperate
machines.

Part of my webservice looks like this:

[WebMethod(TransactionOption =
TransactionOption.RequiresNew, Description="Specific
Method to Query Table1, Table2 and Table3 from the
database")]
public void SaveTables(DataSetApplication dsApp,
ConnectionMode mode)
{
using (SqlConnection sqlConnection = new SqlConnection
(GetConnectionString(mode)))
{
sqlConnection.Open();
InitializeConnection(sqlConnection);

sqlDataAdapterTable1.Update(dsApp);
sqlDataAdapterTable2.Update(dsApp);
sqlDataAdapterTable3.Update(dsApp);

sqlDataAdapterTable1.Dispose();
sqlDataAdapterTable2.Dispose();
sqlDataAdapterTable3.Dispose();
sqlConnection.Close();
}
}


Then I get the following error:

Exception: System.Web.Services.Protocols.SoapException
Message: Server was unable to process request. -->
MSDTC on server 'SQL03' is unavailable.


My question is, how can I modify the code so that the
transaction is in the code and not used directly on the
DB. (The DBA wont change anything on his side...)
 
Hi,

You might explicitely start a transaction by:
SqlTransaction trans = sqlConnection.BeginTransaction;
try
{
...
trans.Commit;
}
catch (Exception ex)
{
trans.Rollback;
...
}
 
If you need distributed transaction support you need to have MSDTC running,
you can't handle this in code yourself.
Why has the DBA decided to stop the DTC, does he really know what it's used
for?

Willy.
 
He knows what it is but I dont know why he wont activate
it...

Can Miha Markic's answer be a way to solve this problem?
You say that it can't be done in code...

Thanks

-----Original Message-----
If you need distributed transaction support you need to have MSDTC running,
you can't handle this in code yourself.
Why has the DBA decided to stop the DTC, does he really know what it's used
for?

Willy.

Hi,

I have an application that uses a web service to access a
SQLServer database. Theses 3 elements are on seperate
machines.

Part of my webservice looks like this:

[WebMethod(TransactionOption =
TransactionOption.RequiresNew, Description="Specific
Method to Query Table1, Table2 and Table3 from the
database")]
public void SaveTables(DataSetApplication dsApp,
ConnectionMode mode)
{
using (SqlConnection sqlConnection = new SqlConnection
(GetConnectionString(mode)))
{
sqlConnection.Open();
InitializeConnection (sqlConnection);

sqlDataAdapterTable1.Update(dsApp);
sqlDataAdapterTable2.Update(dsApp);
sqlDataAdapterTable3.Update(dsApp);

sqlDataAdapterTable1.Dispose();
sqlDataAdapterTable2.Dispose();
sqlDataAdapterTable3.Dispose();
sqlConnection.Close();
}
}


Then I get the following error:

Exception: System.Web.Services.Protocols.SoapException
Message: Server was unable to process request. -->
MSDTC on server 'SQL03' is unavailable.


My question is, how can I modify the code so that the
transaction is in the code and not used directly on the
DB. (The DBA wont change anything on his side...)
Click to expand...


.
Click to expand...
 
Hi,


WhiteEagl said:
He knows what it is but I dont know why he wont activate
it...

Can Miha Markic's answer be a way to solve this problem?
You say that it can't be done in code...
Click to expand...

Willy is talking about *distributed* transactions and he is right - you need
DTC and can't be done through code (ok, well, probably it could in a way or
another :) ).
While I was talking about normal (one database) transactions.
 
Back
Top