Execute synchronization SCRIPT via .net sqlClient.SqlCOmmand

WarrenlaWarrenla Posts: 5
edited July 21, 2009 12:27PM in SQL Compare Previous Versions
We have a application we are push sql scripts to then it executes them to update the database on the clients computer (across the internet).
Anyway, I want to execute the synchronization script REDGATE generates, but as you all should know. It fails.. Any suggestions on how to pull this off?

we are using the .net framework 3.5 latest sp and C#

Comments

  • Eddie DEddie D Posts: 1,720 Rose Gold 5
    Thank you for your post into the forum.

    When executing a synchronization script created by the SQL Compare (or SQL Data Compare or using the Comparison SDK), it may be desired that some ad-hoc queries be intermixed with the SQL produced by the Red Gate APIs.

    Because the BlockExecutor class can only run SQL code by converting ExecutionBlocks to SQL code and submitting them to the SQL Server, custom SQL cannot be introduced into the query stream. It is possible, however, to break an ExecutionBlock into individual query batches and running them using the .NET Framework's ADO .NET methods.

    In the following C# example, first, a connection is made to the server using the connection properties of the second database. Then a transaction is created. The custom SQL query is run first, then each batch of SQL from the ExecutionBlock in order. Finally, the transaction is committed. If any errors occur during the execution of the SQL script, then the error is written to the console and the transaction will be rolled back.
    ExecutionBlock block = provider.GetMigrationSQL(session, new SelectionDelegate(this.SyncRecord), true);
    
    ...
    
    System.Data.SqlClient.SqlTransaction trans = null;
    try
    {
    // Make a connection string from the second database connection properties
    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source="+db2.ConnectionProperties.ServerName+";Initial Catalog="+db2.ConnectionProperties.DatabaseName+";Integrated Security=SSPI");
    System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
    conn.Open();
    trans = conn.BeginTransaction("MyTransaction");
    //Run batches
    for (int i = 0; i < block.BatchCount; i++)
    {
    Batch b = block.GetBatch(i);
    if (!b.Marker)
    {
    cmd.CommandText = b.Contents;
    cmd.ExecuteNonQuery();
    }
    }
    trans.Commit();
    }
    catch (System.Data.SqlClient.SqlException se)
    {
    Console.WriteLine("Execute SQL failed: " + se.Message);
    trans.Rollback();
    }
    
    I hope the above helps in resolving your issue.

    Many Thanks
    Eddie
    Eddie Davis
    Product Support Engineer
    Redgate Software Ltd
    Email: [email protected]
Sign In or Register to comment.