RedGate.SQL.Shared.Utils.ExecuteBlock errors out

kuponutcom

I am getting the following asp.net runtime error:

System.NullReferenceException: Object reference not set to an instance of an object.
   at RedGate.SQL.Shared.Utils.ᐁ(Object , SqlInfoMessageEventArgs )
   at System.Data.SqlClient.SqlConnection.OnInfoMessage(SqlInfoMessageEventArgs imevent)
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at RedGate.SQL.Shared.Utils.ExecuteBlock(ExecutionBlock block, String server, String database, Boolean integratedSecurity, String userName, String password)
   at RedGate.SQL.Shared.Utils.ExecuteBlock(ExecutionBlock block, String server, String database)
   at Service.CompareAndComplyDatabase(String ServerName, String DatabaseName, String UserName, String Password)

and i am using the following code

    [WebMethod(Description = "")]
    public void CompareAndComplyDatabase(string ServerName, string DatabaseName, string UserName, string Password) 
    {
        Database db1 = new Database();
        Database db2 = new Database();

        //copy from
        db1.Register(new ConnectionProperties(
               WebConfigurationManager.AppSettings["SQL_ServerName"].ToString(),
               WebConfigurationManager.AppSettings["SQL_DatabaseName"].ToString(), 
               WebConfigurationManager.AppSettings["SQL_UserName"].ToString(), 
               WebConfigurationManager.AppSettings["SQL_Password"].ToString()
            ),
            Options.Default);

        //copy to
        db2.Register(new ConnectionProperties(
            ServerName,
            DatabaseName,
            UserName,
            Password
            ),
            Options.Default);


        Differences differences = db1.CompareWith(db2, Options.Default);

        foreach (Difference d in differences)
        {
            d.Selected = true;
        }

        Work w = new Work();
        w.BuildFromDifferences(differences, Options.Default, true);

        ExecutionBlock b = w.ExecutionBlock;

        Utils u = new Utils();
        u.ExecuteBlock(b, 
            ServerName,
            DatabaseName
            ,
            true,
            UserName,
            Password);

        b.Dispose();
        db1.Dispose();
        db2.Dispose();
    }    

as far as i can tell, the c# code is set up identically to the toolkit's sample project "SynchronizeDatabases" function

i'm positive the databases connection information provided to the function is accurate and correct, as it would otherwise be erroring out on not being able to connect to the databases.

since the asp.net stack trace includes zero source code information about the Toolkit's library code, I have no idea how to debug my problem other than asking a redgate support staff look at it and give me an idea as to what the issue might be?

Brian Donahue

Hi,

I guess the first thing that you can try is to put a try/catch block around the executeblock method and catch a System.Data.SqlClient.SqlException. If the error is being thrown by SQL server, it will be more plainly-explained in the SqlException.

By the way, I think your code is more relevant to version 4 of Toolkit that version 5. Work.ExecuteBlock should have been depricated in v5.

kuponutcom

well the first thing i've done is download sql bundle 5, installed it, and opened up the c# example in it to see what "new way" it is done if the old way is somewhat depricated... but only found that the example in that toolkit file is identical to my existing code. the 'new file' i looked at to compare to my existing code is located at :
- C:\Program Files\Red Gate\SQL Bundle 5\Toolkit Sample Files\Automating SQL Compare\C#\Class1.cs : SynchronizeDatabases()

could you please post a link to a page or file that demonstrates the "best way" to handle synchronizing databases that does not use the depricated stuff?


i will try the try/catch exception method you suggest to see if that has any results that are helpful

Brian Donahue

Hi,

Please make sure you're looking in the program files\red gate\sql bundle 5 directory. SQL Bundle version 5 installes in a different directory by default, so you may still be looking at the old toolkit. The method for executing the block of SQL code in version 5's API is

RedGate.SQL.Shared.BlockExecutor executor=new RedGate.SQL.Shared.BlockExecutor();
			executor.ExecuteBlock(block, ".", "WidgetProduction");

I hope this helps!