Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question already has an answer here:

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.

share|improve this question
    
Better to migrate as you have same question on both sites stackoverflow.com/questions/18281658/… –  Kin Aug 16 '13 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 Aug 16 '13 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 Aug 16 '13 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 Aug 16 '13 at 21:38
add comment

marked as duplicate by ypercube, Kin, RolandoMySQLDBA, Mat, Mark Storey-Smith Aug 17 '13 at 10:50

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2 Answers

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.

Example:

    '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.
    Next

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"
    connection.Open()
    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 TRANSACTION 
        BEGIN TRY 
        --Run your Statements 
        COMMIT TRANSACTION 
    END TRY 
    BEGIN CATCH 
        ROLLBACK TRANSACTION 
        DECLARE @Msg NVARCHAR(MAX) 
        SELECT @Msg=ERROR_MESSAGE() 
        RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOG 
    END CATCH 

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.

share|improve this answer
2  
Thanks for the quick response. Where's the rollback? I don't see anything about a transaction here. –  John Tseng Aug 16 '13 at 21:01
    
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? –  ypercube Aug 16 '13 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. –  Aaron Hurst Aug 16 '13 at 21:29
    
@AaronHurst I must be lacking either the resources and/or creative mind then! –  John Tseng Aug 16 '13 at 22:00
add comment

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 [1.2.3.4].[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.

share|improve this answer
add comment

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