What are the challenges you face when working across database platforms? Take the survey

Break, Cancel BlockExecutor

dUrosdUros Posts: 21 Bronze 1
In rare cases the execution of SQL script is stopped. I figured out, that this happens if the object (that is suppossed to be upgraded) is used by some user (locking).
The problem is easily fixed by using KILL connections in SSMSA.

I wonder if there is any way that you can catch this error or if it is possible to simply stop the script from executing (by pressing a button?).

I found a BlockExecutor.CancelOperation method in the manual, but I can't find any examples on how to use the method.



  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1

    I'm not sure how you detect the locking automatically - the SDK doesn't do that. I always thought the SET TRANSACTION ISOLATION LEVEL SERIALIZABLE was specified by SQL Compare to prevent that sort of thing.

    If you want to cancel a synchronization, you can use CancelOperation but I assume this would only work in a multithreaded environment, otherwise you would not get a chance to run the CancelOperation method until the synchronization actually finishes and returns control to the calling method. It should work like this:
    using System;
    using RedGate.Shared.SQL;
    using RedGate.Shared.SQL.ExecutionBlock;
    using RedGate.SQLCompare.Engine;
    using System.Collections.Generic;
    namespace SDK105
            class Program
                private delegate void ExecuteScriptCaller(ExecutionBlock eb, string servername, string databasename);
                static string c_SqlServername = "localhost";
                    static void Main(string[] args)
                            using (Database widgetStaging = new Database(),
                                                            widgetProduction = new Database())
                                    // Retrieve the schema information for the two databases
                                widgetStaging.Register(new ConnectionProperties(c_SqlServername, "WidgetStaging"), Options.Default);
                                widgetProduction.Register(new ConnectionProperties(c_SqlServername, "WidgetProduction"), Options.Default);
                                    // Compare widgetStaging to widgetProduction. 
                                    Differences stagingVsProduction = widgetStaging.CompareWith(widgetProduction, Options.Default);
                                    // Select the differences to include in the synchronization. 
                                    foreach (Difference difference in stagingVsProduction)
                                            difference.Selected = true;
                                    Work work = new Work();
                                    // Calculate the work to do using sensible default options
                                    // The script is to be run on WidgetProduction so the runOnTwo parameter is true
                                    work.BuildFromDifferences(stagingVsProduction, Options.Default, true);
                                using (ExecutionBlock block = work.ExecutionBlock)
                                        // Use a BlockExecutor to run the SQL against the WidgetProduction database
                                        BlockExecutor executor = new BlockExecutor();
                                        // Use the delegate method we defined int he first line
                                        ExecuteScriptCaller caller=new ExecuteScriptCaller(executor.ExecuteBlock);
                                        // Execute the SQL blocks asynchronously in another thread
                                        IAsyncResult result=caller.BeginInvoke(block, c_SqlServername, "WidgetProduction",null, null);
                                        // Put the main thread to sleep for a second
                                        // Tell the execution to cancel on the other thread
                                        // Wait for worker thread to finish
                                        // cleanup
                            Console.WriteLine("Press [Enter]");
  • Options
    dUrosdUros Posts: 21 Bronze 1
    Thanks Brian,

    In youre code, I see what I want to see :). In other way, one thread is better or program wil end and user will think that is all Ok.

    Now I run SP with "kill connections" for upgrading DB, before upgrading. No more problems with locking and user looking hourglass till the end of upgrade.

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Thanks for following up. As I recall, you can also set a database in single user mode, which will kill all connections, then you can set it back to multi-user.
  • Options
    dUrosdUros Posts: 21 Bronze 1

    Thank for advise, but I think is better killing connections with KILL, becouse if come to error, DB will stay in single user mode.

    My proc for killing connections:
    ALTER PROC [dbo].[Kill_Connections] 
    @dbName varchar(100)
    DECLARE @ProcessId varchar(10)
    SELECT spid FROM master.dbo.sysprocesses 
    WHERE dbid = (SELECT dbid FROM master.dbo.sysdatabases WHERE Name = @dbName ) 
    ORDER BY spid 
    OPEN CurrentProcesses
    FETCH NEXT FROM CurrentProcesses INTO @ProcessId
    	Exec ('KILL ' +  @ProcessId)
    	FETCH NEXT FROM CurrentProcesses INTO @ProcessId
    CLOSE CurrentProcesses
    DEALLOCATE CurrentProcesses
Sign In or Register to comment.