Red Gate forums :: View topic - Saving Contents of BlockExecutor for Later Execution
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Toolkit Previous Versions
SQL Toolkit Previous Versions forum

Saving Contents of BlockExecutor for Later Execution

Search in SQL Toolkit Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
bazza47



Joined: 24 Oct 2007
Posts: 3

PostPosted: Wed Oct 24, 2007 10:50 pm    Post subject: Saving Contents of BlockExecutor for Later Execution Reply with quote

After comparing two database schemas using the SQL Toolkit 6 API, I can save the changes to a file as follows:

Code:
string filePath = GetCachedScriptFilePath(targetDatabaseName);
BlockSaver saveBlock = new BlockSaver(filePath, EncodingType.UTF8, work.ExecutionBlock);
saveBlock.SaveToFile();


Is it possible to use the API to load the contents of the file, at some later time, and then apply these changes?

I tried code as follows, to apply the script contents, but the BlockExecutor doesnt like the 'GO's in the script. If I take the GOs out the the BlockExecutor fails because variables are defined more than once etc.

Code:
string scriptWithoutGO = String.Empty;
using (StreamReader sr = new StreamReader(cachedScriptFilePath))
{
    string script = sr.ReadToEnd();
    sr.Close();
    //scriptWithoutGO = script.Replace("GO", " ");   // nuke any GO's
    scriptWithoutGO = script;                       // or try with GO's
}

using (RedGate.SQLCompare.Engine.Database dbTarget = new RedGate.SQLCompare.Engine.Database())
{
    RedGate.SQL.Shared.BlockExecutor b = new BlockExecutor();
    using (ExecutionBlock blk = new ExecutionBlock())
    {
        blk.AddBatch(scriptWithoutGO);
        b.ExecuteBlock(blk, dataSource, targetDatabaseName, true, String.Empty, String.Empty);
    }
}


We have a large number of databases we need to sync and storing the changes to a script file for subsequent execution could lead to a reduction in processing time.

Thanks
Colin
Back to top
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 525
Location: Red Gate Software

PostPosted: Mon Oct 29, 2007 12:51 pm    Post subject: Reply with quote

The script which is saved using BlockSaver is designed to be run with Management Studio or similar tools.

There are two approaches you could take to saving an execution block:

1) Save the script and then run it 'by hand', i.e. using something like the following:

Code:
        public static void executeSql(string fileName, SqlConnection connection, bool reportErrors)
        {
            string sqlScript;

            using (StreamReader sr = new StreamReader(fileName))
            {
                sqlScript = sr.ReadToEnd();
            }

            Regex r = new Regex("\\sGO\\s");
            string[] sqlScriptParts = r.Split(sqlScript);

            try
            {
                connection.Open();

                foreach (string sql in sqlScriptParts)
                {
                    SqlCommand command = new SqlCommand(sql, connection);
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        if (reportErrors) throw;
                        Console.WriteLine("Error executing command: " + command.CommandText);
                        Console.WriteLine("Error was: " + e);
                        continue;
                    }
                    finally
                    {
                        command.Dispose();
                    }
                }
            }
            finally
            {
                connection.Dispose();
            }
        }


2) Serialize the actual ExecutionBlock object out to disk somehow, and read it back in into an ExecutionBlock object directly. I'm not entirely sure how or if this would work - I can look into it further if the above method won't do.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group