TransactionScope and dynamic database connection selection question

forrest posted on Friday, November 06, 2009

In my application there is 2 databases located in beijing and shanghai.
some data are stored in different place according the sale department's location.

In a InvoiceApply Business object,I will store the InvoiceApply object to the database by the order's sale department location of the invoice.

        [Transactional(TransactionalTypes.TransactionScope)]
        protected override void DataPortal_Update()
        {
            string conn = GetObjectDataSourceConnection();
            using (SqlConnection cn = new SqlConnection(conn))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = @"UpdateOrderInvoiceRequirement";
                    DoInsertUpdate(cm);
                }
            }
        }

        private string GetObjectDataSourceConnection()
        {
            string conn = DataBase.MainTradeInfoConnection;
            BusinessOrder order = BusinessOrder.GetBusinessOrder(_orderNo);
            if (order.TradeDept.DataInSHDataBase)
            {
                conn = DataBase.SubTradeInfoConnection;
            }

            return conn;
        }
       
There is database operation operation(open database and get business object, only select operation no transaction control) in GetObjectDataSourceConnection() method.

The question is when the DataPortal_Update() method has the [Transactional(TransactionalTypes.TransactionScope)] attribute,the method throw a exception.
If I cut the attribute or do not execute the dynamic database connection selection,the method will be ok.

Any suggestion will be appreciated!
Thanks!

richardb replied on Friday, November 06, 2009

Sounds like you have a distributed transaction there, two different databases on two different servers.

Assuming it is SQL Server, it might be that the MSDTC coordinator might have a security access issue.  There might be some configuration required in SQL and/or Windows to get that working.