ExecuteBlock Error

gsimmons

I am trying to test the toolkit using the example from the help file. All I have changed is the server name and database names. When it tries to run the ExecuteBlock I get "An unhandled exception of type 'System.Data.sqlclient.sqlexecption' occurred in redgate.sql.shared.dll. Additional Information: System Error.

Everything else seems to run fine. Here is the code that I am using:

static void Main(string[] args)
{
Database db1=new Database();
Database db2=new Database();

db1.Register(new ConnectionProperties("sa1dbt9", "Empower"), Options.Default);
db2.Register(new ConnectionProperties("sa1dbt9", "EmpowerDBG"), Options.Default);

Differences differences=db1.CompareWith(db2, Options.IgnoreFillFactor | Options.IgnoreWhiteSpace | Options.IncludeDependencies | Options.IgnoreFileGroups) ;

foreach (Difference difference in differences)
{
//make sure the difference is selected so it is included in the synchronization
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(differences, Options.Default, true);

//we can now access the messages and warnings

/* Console.WriteLine("Messages:");

foreach (Message message in work.Messages)
{
Console.WriteLine(message.Text);
}

Console.WriteLine("Warnings:");

foreach (Message message in work.Warnings)
{
Console.WriteLine(message.Text);
}

//print out the SQL used to synchronize
*/
ExecutionBlock block=work.ExecutionBlock;

Console.WriteLine("SQL to synchronize:");

Console.WriteLine(block.ToString());

//and run the SQL

RedGate.SQL.Shared.Utils utils=new RedGate.SQL.Shared.Utils();
utils.ExecuteBlock(block, "sa1dbt9", "EmpowerDBG");

db1.Dispose();
db2.Dispose();
block.Dispose();


}

Brian Donahue

Hello There,

It would probably be more helpful to trap the SQL error and see what it is. To do this, wrap the whole code in a try/catch block and catch the correct exception type.

ty {
...the code...
}
catch (System.Data.SqlClient.SqlException e) {
Console.WriteLine("SQL Error: {0}", e.Message)
}
The SQL error will be much more revealing and will most likely pinpoint the real cause of the problem.

gsimmons

Thank you for the "try" statement. I am new to .Net and C#. I didn't know what to catch!

Now that I am getting an informative message...it is telling me:

SQL Error: Invalid column name 'Text1'.
Invalid column name 'Text2'.
Invalid column name 'Text3'.
Invalid column name 'Text4'.
Invalid column name 'Text5'.
Invalid column name 'TypeId'.

I checked and there is a table with these column names in the Source database. The "text" columns are VARCHAR(200) and the typeid is an INT. Are these names reserved? Does this make any sense?

Thanks,
George

Brian Donahue

Hello George,

Data Compare shouldn't have let you get this far if there is a schema mismatch. So I'd assume that perhaps your target database uses a case-sensitive database collation order. In that case, a column named 'Text1' would not be the same as 'text1' for instance.

gsimmons

Hi Brian,

Thanks for the input. This is actually a schema compare. And neither database is case sensitive.

Thanks,
George

gsimmons

Ok, I ran the same thing using the GUI and got a little more info. The problem is that a stored procedure is referencing columns that do not exist in a table. When the app tries to create this stored procedure it throws the same error you would receive when doing syntax check.

Is there a way to ignore this type of error? Even if the stored proc is invalid I don't want the app to error out. I would rather it just create the proc and move along.

Thanks,
George

Brian Donahue

Hello George,

The only thought I have is to use the NoSQLPlumbing option. That should prevent the script from rolling back if there is an error. Of course when you execute this SQL, you will also want to put it in a try/catch block.

gsimmons

Brian,

I stumbled across the "no sql plumbing" this morning and have been playing with it. It causes changes to be rolled back but it also stops the script from running on the first error. Optimally, possibly logging the error and then continuing would be what I am looking for.

The problem is that there are going to be procedures that reference other databases that do not exist (in this situation). Plus the occaissional procedure that does not want to compile for some reason. In this particular circumstance I want to ignore those errors so that the schema from db1 matches db2.

Thanks for all of your help.

George

Brian Donahue

Hi George,
This is what I am talking about. This will run each individual batch of SQL in the executionblock individually and should allow you to continue in the case of an error.

SqlConnection conn=new SqlConnection("server=dataserver; initial catalog=database; user id=sa; password=xxxx; Connection Lifetime=120");
			conn.Open();
			SqlCommand cmd=new SqlCommand();
			cmd.Connection=conn;
			for (int i=0; i>obExeBlock.BatchCount; i++) 
			{
				Batch b=obExeBlock.GetBatch(i);
				if (b.Marker==false)
				{
					cmd.CommandText=b.Contents;
					try 
					{
						cmd.ExecuteNonQuery();
					}
					catch (SqlException e) 
					{
						Console.WriteLine("SQL error: {0}", e.Message);
					}
				}
			}