I have 2 linq 2 SQL statements I'd like to be in a transaction (the SQL server is remote, outside firewalls etc) all other communication works but when I wrap these 2 statements in a TransactionScope() I begin having to configure MSDTC which we did, but then there are firewall issues (I think) is there a simpler way?
the basics of what I want to do boil down to this: (both are stored procs under the hood)
using (var transactionScope = new TransactionScope())
{
Repository.DataContext.SubmitChanges();
Repository.DataContext.spDoFinalStuff(tempID, ref finalId);
transactionScope.Complete();
}
What is the simplest way to achieve this?
EDIT:
first I got this: The transaction manager has disabled its support for remote/network transactions. (Exception from HRESULT: 0x8004D024)
On our servers I followed the instructions here to correct this. However the instructions don't seem to apply to windows 7 (my dev box) see my comment on above answer.
after correcting the issue (on the non win7 boxes) I get this: The transaction has already been implicitly or explicitly committed or aborted (Exception from HRESULT: 0x8004D00E) which some googling suggested may be firewall issue.
EDIT
I just discovered the remote DB is SQL 2000
if 2 updates are sent to 2 different database, .net transactoinScope class need MSDTC's help to coordinate the transactions. SQL server 2005 or later, if two update are within the same database, no MSDTC are involved.
MSDTC are configured in com+ component service, choose your computer name, and select properties, basically you should select No authentication.
the following link might help