Start a new process in a transaction

Question

Hi,

I have to create a user in a third party application that stores its information in SQL Server, and I also store information about this user in our application database, also in SQL Server.

I was hoping to wrap both updates in a TransactionScope, so they can succeed or rollback together. Unfortunately the only method I have for creating users is via a command-line utility, that I am currently running through a Process.Start call. I was hoping the new process might still enlist in my active transaction, but this does not seem to be the case.

As it happens, both datbases are on the same server machine, but are under different instances. My application is written in C# for .NET 3.5

Am I doing something wrong, or is there some explicit way to get this process to enlist in the transaction, or will I just have to manually rollback the changes?

Answer 1

To answer your question: They can't automatically go into the same transaction scope. Try to create the user first, that way, if it fails you don't even create the records in your db.

If your Command Line process that you use to create the user returns an error code, (the int in 'public static int Main()' check that and make that part of the result of your transaction. 0 means success, everything else usually means failure.

If it doesn't create an error code. but some text on STDERR, override the STDERR stream, read it's output and check that way.

Answer 2

Enlist command line app in distributed transaction - don't think this can be done.