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

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.

Regards

Comments

  • Options
    Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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();
                    }
            }
    }
    
  • 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.

    Regards
  • 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
    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
    
Sign In or Register to comment.