We have deployment scripts that we need to run on databases that are spread across multiple servers. One script only runs on one database, but the scripts depend on each other. We are looking for a way to run all of the scripts as one big transaction so that all scripts either commit or rollback as a whole.

How do I do this?

I would prefer a way to do this from ADO.NET, but SSMS is cool, too.

My current solution (that does not work) is that I begin transaction in every database, run all my scripts, and then commit/rollback once everything is good. However, I can't run all my scripts since cross-database dependencies are blocking indefinitely.

  • Better to migrate as you have same question on both sites stackoverflow.com/questions/18281658/…
    – Kin Shah
    Commented Aug 16, 2013 at 20:41
  • @Kin I wanted to see if either community would know the answer. Do you think it belongs in stackoverflow instead of dba?
    – John Tseng
    Commented Aug 16, 2013 at 20:42
  • Since you want to do it using ADO.NET it will be good to migrate it to SO. I might be wrong, so let the Mods decide ..
    – Kin Shah
    Commented Aug 16, 2013 at 20:45
  • @ypercube I've updated my question with what I've tried so far. I don't think I can implement 2 phase commit myself because of the locking issues.
    – John Tseng
    Commented Aug 16, 2013 at 21:38

2 Answers 2


I've run into a problem like this. I used ADO.NET 4 with VB.

Here's what I did:

  1. Created a .txt file with list of servers.
  2. Dump the .txt file into a List Box.
  3. Wrote the .NET application to scan through the list box, line by line, dumping each line into a text box on the form.
  4. Replace the connection string on the SQL connection with the database currently in the text box.
  5. Wrote the SQL scripts inside the command clause.
  6. Execute the SQL command on the current database.
  7. Next line.


    'Loop through the server names.
    For i = 0 To lbxServers.SelectedItems.Count - 1
        txtServer.Text = ""
        txtServer.Text &= lbxServers.SelectedItems.Item(i).ToString() & " "
        'Call your sub procedure which contains your sql connection and sql command.

That will get your loop going, then you need to write some sub procedures for your queries. Like so:

    'Declare SQL connection variables.
    Dim connection As New Data.SqlClient.SqlConnection
    Dim dataAdapter As New Data.SqlClient.SqlDataAdapter
    Dim command As New Data.SqlClient.SqlCommand

    'Connect to the server next in the list box.
    connection.ConnectionString = "Server= " & txtServer.Text & "; integrated security=true"

    'SQL Statement to run on the server.
    command.CommandText = "TSQL"
    command.Connection = "connection"
    dataAdapter.SelectCommand = "command"

I put all of this inside of a try/catch loop so if there is an error it will rollback.

The TRY/CATCH loop was inside of my CommandText.

        BEGIN TRY 
        --Run your Statements 
    END TRY 
        RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG 

I used my application to grab and dump that local variable for each session into a datagridview. That way if error happened, I saw it in the application.

  • 2
    Thanks for the quick response. Where's the rollback? I don't see anything about a transaction here.
    – John Tseng
    Commented Aug 16, 2013 at 21:01
  • stackoverflow.com/questions/639238/… Commented Aug 16, 2013 at 21:17
  • 1
    Please move the code sample from the comments to your answer. And I'm not sure that you have solved all issues. What happens if the transaction to server A commits and then the transaction to server B rollbacks? How can we "uncommit" the first one? Commented Aug 16, 2013 at 21:19
  • You're right, my situation didn't require that all my scripts depend on one another but you can get creative with the given code. I'm sure you can get it done using bits and pieces with the given information. I've never come across a situation that you couldn't write code for, given appropriate resources and a creative mind. Commented Aug 16, 2013 at 21:29
  • @AaronHurst I must be lacking either the resources and/or creative mind then!
    – John Tseng
    Commented Aug 16, 2013 at 22:00

Linked servers can do this with MSDTC. After setting up MSDTC with instructions from http://www.sqlwebpedia.com/content/msdtc-troubleshooting and creating linked servers to every server, I am now able to do through exec [].[catalog].dbo.sp_executesql.

System.Transactions.Transaction can also use MSDTC with this. This way I could communicate directly with the server instead of exec sp_executesql.

Not the answer you're looking for? Browse other questions tagged or ask your own question.