Simple Transactions

Question

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

Answer 1

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

http://support.microsoft.com/kb/306843

http://blogs.msdn.com/b/distributedservices/archive/2008/11/12/troubleshooting-msdtc-issues-with-the-dtcping-tool.aspx

Answer 2

You could always create a new stored procedure that executes these two SPs within its own transaction? Quick n' dirty...

Answer 3

TransactionScope is the way to go if you don't want to mess around with SQL. I am not to familiar with doing transactions over a firewall and having any issues though. Can you post what exceptions / errors you are encountering?

If it is really causing you problems you could make a stored proc to wrap those and do the transaction inside the wrapped store proc.

Answer 4

When you only need to handle one single database during the transaction, you can simply create and open a new SqlConnection. This prevents you from needing to use a TransactionScope. Here is an example:

using (var con = new SqlConnection("constr"))
{
    con.Open();
    using (var tran = con.BeginTransaction())
    {
        using (var context = new YourDataContext(con))
        {
            // Do stuff
            context.SubmitChanges();
            int generatedId = /* get this id */
            // Do stuff with id

            context.SubmitChanges();
        }
    }
}

Because you use a Repository, you will have to create the context on the background, but the idea is the same. And don't forget to dispose the database transaction and connection.