NULL Reference error when performing Data Compare

NKost

Hello,

I am using the SQL Comparison and Synchronization Toolkit to synchronize the schema and data from one populated DB, to an empty DB. The Schema stuff is working fine (the new blank DB has the schema applied, and looks like a blank version of the populated DB).

When I try to do the Data Compare though, I get the following error:


Unhandled Exception: System.NullReferenceException: Object reference not set to an instance of an object.
at RedGate.SQLDataCompare.Engine.SqlProvider.GetMigrationSQL(ComparisonSession session, SelectionDelegate select, Boolean runOnTwo)
at RedGate.SQLDataCompare.Engine.SqlProvider.GetMigrationSQL(ComparisonSession session, Boolean runOnTwo)
at DBSnapshot.CopyDB.dataCompareDBs(String sourceDBServer, String sourceDBName, String sourceDBPassword, String blankDBName, String blankDBPassword, DataSet compareTables)
at DBSnapshot.CopyDB.Main(String[] args)

I think this might be caused by my selective table comparing. Rather than do a blanket data compare between the two DBs, I only Add a TableComparisonSetting if the table name matches the table name in my dataSet (see below). I AM using NULL for the uniqueFieldDefinitions when adding a TableComparisonSetting, and I was wondering if that could be the problem?

RedGate.SQLDataCompare.Engine.SqlProvider provider=new SqlProvider();
RedGate.SQLDataCompare.Engine.Database compareDB1=provider.GetDatabase(new SqlConnectionProperties(sourceDBServer, sourceDBName,"sa",sourceDBPassword));
RedGate.SQLDataCompare.Engine.Database compareDB2=provider.GetDatabase(new SqlConnectionProperties("localhost",blankDBName,"sa",blankDBPassword));
//set up the table comparison setting
RedGate.SQLDataCompare.Engine.TableComparisonSettings settings=new RedGate.SQLDataCompare.Engine.TableComparisonSettings();
//Filter through DB Tables, and identify CODE tables to compare with.
try
{
int i = 0;
foreach (RedGate.SQLDataCompare.Engine.Table table in compareDB1.Tables)
{
while (i < compareTables.Tables[0].Rows.Count)
{
if (compareTables.Tables[0].Rows[0].ToString() == table.Name)
{
settings.Add(new TableComparisonSetting(table.FullyQualifiedName, table.Fields, null));
Console.WriteLine("TABLE: " + table.Name);
}
i = i + 1;
}
i = 0;
}
}


Here is the line of code that is actually failing, but it's just two lines down so I'm not sure if it's something with my CompareDatabases method.. or the NULL problem above..

ComparisonSession session=new ComparisonSession();
session.CompareDatabases(compareDB1, compareDB2, settings);


I would appreciate any feedback you all may have..
Thanks!
-Ned Kost
Software Developer
Telution

Brian Donahue

Hi,

Thanks for your post. You may be ending up trying to compare tables that are ineligable to be compared because they do not have matching primary keys. Your best bet would be to identify the matching tables using the CreateFromIntersection method and filtering those down by name.

If the collection of tables returned by CreateFromIntersection returns NULL, that means that there are no two tables in the databases that match schema and primary key, which means they can't be compared.