Red Gate forums :: View topic - Break, Cancel BlockExecutor
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Comparison SDK 10
SQL Comparison SDK 10 forum

Break, Cancel BlockExecutor

Search in SQL Comparison SDK 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
dUros



Joined: 26 Feb 2014
Posts: 6
Location: Slovenija

PostPosted: Wed Mar 05, 2014 10:09 am    Post subject: Break, Cancel BlockExecutor Reply with quote

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
Back to top
View user's profile Send private message AIM Address
Brian Donahue



Joined: 23 Aug 2004
Posts: 6641

PostPosted: Thu Mar 06, 2014 12:54 pm    Post subject: Reply with quote

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:
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();
                }
        }
}
Back to top
View user's profile Send private message
dUros



Joined: 26 Feb 2014
Posts: 6
Location: Slovenija

PostPosted: Thu Mar 06, 2014 4:27 pm    Post subject: Reply with quote

Thanks Brian,

In youre code, I see what I want to see Smile. 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
Back to top
View user's profile Send private message AIM Address
Brian Donahue



Joined: 23 Aug 2004
Posts: 6641

PostPosted: Tue Mar 11, 2014 9:30 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
dUros



Joined: 26 Feb 2014
Posts: 6
Location: Slovenija

PostPosted: Tue Mar 11, 2014 10:00 am    Post subject: Reply with quote

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:

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
Back to top
View user's profile Send private message AIM Address
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