Break, Cancel BlockExecutor

dUros

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.

Regards

Brian Donahue

Hello,

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
                                    System.Threading.Thread.Sleep(1000);
                                    // Tell the execution to cancel on the other thread
                                    executor.CancelOperation();
                                    // Wait for worker thread to finish
                                    result.AsyncWaitHandle.WaitOne();
                                    // cleanup
                                    result.AsyncWaitHandle.Close();
                                }
                        }
 
                        Console.WriteLine("Press [Enter]");
                        Console.ReadLine();
                }
        }
}

dUros

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.

Regards

Brian Donahue

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.

dUros

Hello

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)
AS

DECLARE @ProcessId varchar(10)
DECLARE CurrentProcesses SCROLL CURSOR FOR

SELECT spid FROM master.dbo.sysprocesses 
WHERE dbid = (SELECT dbid FROM master.dbo.sysdatabases WHERE Name = @dbName ) 
ORDER BY spid 

FOR READ ONLY
OPEN CurrentProcesses

FETCH NEXT FROM CurrentProcesses INTO @ProcessId
WHILE @@FETCH_STATUS <> -1
BEGIN
	Exec ('KILL ' +  @ProcessId)
	FETCH NEXT FROM CurrentProcesses INTO @ProcessId
END

CLOSE CurrentProcesses
DEALLOCATE CurrentProcesses