Creating a synchronization script without batch markers

Category: How do I?
Date: 03 Feb 2011
Product: SQL Comparison SDK
When saving a synchronization script produced by SQL Comparison SDK, you may want to save the script for later use by the ADO .NET client (SqlCommand). Since the script produced using SQL Toolkit's BlockSaver class in meant for SQL Server Management Studio, it includes batch separators (GO commands) that aren't understood by ADO .NET and will cause errors when run for this reason.

It is possible to save the SQL Toolkit ExecutionBlock in a way where batch markers are not included in the script by looping through the individual batches and saving only batches that are not marked as a batch marker. Here is an example code snippet which assumes that you have already populated an ExecutionBlock called "block":

[C#]
using (System.IO.StreamWriter sw=new System.IO.StreamWriter(@"c:\temp\test.sql"))
{
for (int i = 0; i < block.batchcount;="">
{
Batch b = block.GetBatch(i);
if (b.Marker == false)
{
sw.Write(b.Contents + "\r\n");
}
}
}
[/C#]

Please note that you may want to use the NoSqlPlumbing option as well when you create the ExecutiuonBlock to prevent transactional operations from appearing in the script. ADO .NET has its' own methods for rolling back and committing transactions if necessary.

work.BuildFromDifferences(stagingVsProduction, Options.Default | Options.NoSQLPlumbing, true);

Document ID: KB200806000271 Keywords: SQL,SDK,batch,GO

Was this article helpful?

Search support