Execute synchronization SCRIPT via .net sqlClient.SqlCOmmand

Warrenla

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#

Eddie D

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