Execution Blocks - Adding to them.

jbaggaley

I want to create a remote execution block into which gets placed differences in table structures, static table data and dynamically filtered records. These data items are put together in different parts of my code and then I want to execture them all at once so they form part of the same transaction.
At first I thought I could declare a module level execution block and then use the AddBatch along the lines of:

// Script the differences
Work work=new Work();
work.BuildFromDifferences(differences, Options.Default, true);

// Add to the batch
_ebSyncRemoteBlock.AddBatch (work.ExecutionBlock.ToString());

I was then hoping that after finishing all my processing I could run something like the following:

Utils utils=new Utils();
// Run the updates remotely with everything part of a single transaction
utils.ExecuteBlock(_ebSyncRemoteBlock, _ssRemoteServer.ServerName ,  _ssRemoteServer.DBName);

This however gives me lots of errors as follows:

 Message: "Line 7: Incorrect syntax near 'GO'.\r\nLine 9: Incorrect syntax near 'GO'.\r\nLine 11: Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword 'SET'.\r\nLine 15: Incorrect syntax near 'GO'.\r\nLine 17: Incorrect syntax near 'GO'.\r\nLine 21: Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword 'IF'.\r\nLine 34: Incorrect syntax near 'GO'.\r\nLine 38: Incorrect syntax near 'GO'.\r\nLine 42: Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword 'IF'.\r\nLine 57: Incorrect syntax near 'GO'.\r\nLine 61: Incorrect syntax near 'GO'.\r\nLine 65: Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword 'IF'.\r\nLine 81: Incorrect syntax near 'GO'.\r\nLine 85: Incorrect syntax near 'GO'.\r\nLine 89: Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword 'IF'.\r\nLine 104: Incorrect syntax near 'GO'.\r\nLine 108: Incorrect syntax near 'GO'.\r\nLine 112: Incorrect syntax near 'GO'.\r\nIncorrect syntax near the keyword 'IF'.\r\nLine 125: Incorrect syntax near
 'GO'.\r\nLine 129: Incorrect syntax near 'GO'.\r\nLine 133: Incorrect syntax near 'GO'.\r\nLine 141: Incorrect syntax near 'GO'.\r\nLine 143: Incorrect syntax near 'GO'."
    Source: ".Net SqlClient Data Provider"
    StackTrace: "   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()\r\n   at RedGate.SQL.Shared.Utils.ExecuteBlock(ExecutionBlock block, String server, String database, Boolean integratedSecurity, String userName, String password)\r\n   at RedGate.SQL.Shared.Utils.ExecuteBlock(ExecutionBlock block, String server, String database)\r\n   at WindowsApplication1.Class1.SyncRemoteStructureAndData() in e:\\jonb\\prototype\\datasync\\windowsapplication1\\class1.cs:line 103"
    TargetSite: {System.Reflection.RuntimeMethodInfo}

How should I be doing this? I am having trouble finding examples that are similar but am sure I am not the only person doing this. Alternatively, is there a better way?

Thanks

Jon

Brian Donahue

Jon,

I just had this conversation again today with development and the answer still remains the same. Mainly you shouldn't mess around with the executionblock object.

The error you're getting relates to the batch separators. If you convert an executionblock to a string you will get all of these GO statements (batch separators) and you can't run these in .NET's SqlClient as they're specific to Microsoft's SQL running tools like isql.exe and osql.exe.

You may be able to run through the individual batches in the execution block and run the batch only if it is not a separator. There is a boolean property on each batch that indicated if it is simply a separator.

jbaggaley

Brian,
Am not quite sure of the point of an execution block now. I want to run a series of different steps in the context of one transaction (is there something higher than a block). Ideally a block should have an AddBlock function which as the name says on the tin, just appends blocks together to big blocks. I am sure that internally, you are not storing all the GOs etc. as you say they only come out when doing a ToString (although this has all theoretically changed with the new version 5...)

Jon

Brian Donahue

I think you can have a little more control if you get the individual batches from the executionblock and run them one-at-a-time:

ExecutionBlock b;
SqlConnection conn=new SqlConnection(...);
SqlCommand cmd=new SqlCommand();
cmd.Connection=conn;
...
for (int i=0;i<b.BatchCount;i++) {
Batch bat=b.GetBatch(i);
cmd.CommandText=bat.Contents;
if (!bat.Marker) cmd.ExecuteNonQuery();
}

jbaggaley

This approach was close to what I needed. Because I needed to batch up several scripts both for structure and data, I found that I could save the script using the getstring() after creating it with no transactional plumbing (on toolkit5). I then pick up the script at a later point, split it into an array using the GO markers and then run through executing each of the batches against the database directly. This has the advantage that I can zip the changes script for my users on a modem link. I also don't need to distribute your components to the hundreds of laptops we have in the field. Our remote users are working from a local web site so executing oSQL didn't seem a reasonable option. <fingerscrossed>Seems to work!</fingerscrossed>

Thanks

Jon