Break, Cancel BlockExecutor

Automate and integrate using the SQL comparison API

Moderators: Chris Auckland, David Atkinson, Michelle Taylor, chengvoon.tong

Break, Cancel BlockExecutor

Postby dUros » Wed Mar 05, 2014 10:09 am

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
dUros
 
Posts: 6
Joined: Wed Feb 26, 2014 11:09 am
Location: Slovenija

Postby Brian Donahue » Thu Mar 06, 2014 12:54 pm

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:
Code: Select all
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();
                }
        }
}
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby dUros » Thu Mar 06, 2014 4:27 pm

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
dUros
 
Posts: 6
Joined: Wed Feb 26, 2014 11:09 am
Location: Slovenija

Postby Brian Donahue » Tue Mar 11, 2014 9:30 am

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.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am

Postby dUros » Tue Mar 11, 2014 10:00 am

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:
Code: Select all
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
dUros
 
Posts: 6
Joined: Wed Feb 26, 2014 11:09 am
Location: Slovenija


Return to SQL Comparison SDK 10

Who is online

Users browsing this forum: No registered users and 0 guests