Problem with transactions

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

[Miha Markic]

Hi,

You might explicitely start a transaction by:
SqlTransaction trans = sqlConnection.BeginTransaction;
try
{
...
trans.Commit;
}
catch (Exception ex)
{
trans.Rollback;
...
}
--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

"WhiteEagl" <wh*******@hotmail.com.REMOVE> wrote in message
news:02****************************@phx.gbl...

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. --&gt;
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...)

[Willy Denoyette [MVP]]

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.

"WhiteEagl" <wh*******@hotmail.com.REMOVE> wrote in message
news:02****************************@phx.gbl...

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. --&gt;
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...)

[WhiteEagl]

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 usedfor?

Willy.

"WhiteEagl" <wh*******@hotmail.com.REMOVE> wrote in messagenews:02****************************@phx.gbl...

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

.

[Miha Markic]

Hi,
"WhiteEagl" <an*******@discussions.microsoft.com> wrote in message
news:09****************************@phx.gbl...

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

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.