Unwanted Promotion with SQL Express 2005  
Author Message
Mark G. Goles





PostPosted: .NET Framework Data Access and Storage, Unwanted Promotion with SQL Express 2005 Top

I've read through a number of forum postings now, and don't think what I'm seeing should be happening. We are installing SQL Server Express 2005 on a tablet PC, copying the database file (if necessary, meaning that the file doesn't exist) to a Local Settings location, and then trying to open the file using code similar to the following:

using (TransactionScope tr = new TransactionScope()) {
using (SqlConnection cn = new SqlConnection(BusinessCommon.DBConnection)) {
cn.Open();
// Work with SQLCommands here
cn.Close();
}

}

I am using the following connection string:

Data Source=.\SQLEXPRESS;AttachDbFilename="<db file location path here>";Integrated Security=True;User Instance=True;Connection Timeout=30

The problem I am having is that my attempt to open a connection fails immediately (on the ONE AND ONLY cn.Open()) with a DTC error. The problem goes away if I change DTC from manual startup to automatic and start it. I take this to mean that my transaction is being promoted from a lightweight transaction to a DTC transacation. Why is this happening DTC is turned off by default with a SQL Server 2005 Express installation, and I don't really like the idea of having to tell each user to turn this on, and how to turn it on, when the transaction shouldn't be promoting in the first place.

My actual code is somewhat more complicated, as I'm using Lhotka's CSLA Business Object subsystem, but this certainly seems to be the gist of it. In any case, I am quite confident that no other connection-open calls occur between the TransactionScope and the one and only SqlConnection Open(). My understanding from other forum posts is that opening more than one connection certainly promotes, but I do not believe I am doing this. Is it possible that there is some problem with my connection string I'll try "(local)" instead of "." but have little reason to believe this will help.




.NET Development12  
 
 
BillyB





PostPosted: .NET Framework Data Access and Storage, Unwanted Promotion with SQL Express 2005 Top

Mark,

I'm not an expert on this topic by any means, but I stumbled across during my own quest to find out why my transactions are getting promoted. So take this with a grain of salt, but ...

there is a connectionstring property for SQLServer 2005 called Enlist. You can read up on it in MSDN literature and elsewhere, but I'm thinking that perhaps if you add Enlist=false to your connection string, then you wont have to start MSDTC. Worth a try, anyway.

-BillyB