Data Compare API - Object Reference Not Set Error

danielstony

Hi,

We are using the API to data compare 2 databases. The code is real simple.

We have tried this on multiple machines, change the references from Data COmpare 7.0 to 6.0, tried different databases. We have tried it on VS2005 and VS 2008 and I have got SQL COmparison 7 installed (via the Toolbelt)

Database db1 = new Database();
Database db2 = new Database();

server = "dsk00248\\adviser_evol";
db1.RegisterForDataCompare(new ConnectionProperties("dsk00248\\adviser_evol", "UpdateProducts"));
db2.RegisterForDataCompare(new ConnectionProperties("dsk00248\\adviser_evol", "AEMain"));

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

// **** This line always reports Object Reference Not Set ****
work.BuildFromDifferences(differences, Options.Default, true);



The call stack reports
{System.NullReferenceException: Object reference not set to an instance of an object.
at RedGate.SQLCompare.Engine.Dependencies.ResetNodes()
at RedGate.SQLCompare.Engine.Work._1(Differences )
at RedGate.SQLCompare.Engine.Work._1(Differences , Options , Boolean )
at RedGate.SQLCompare.Engine.Work._1(Differences , Difference , Options , Boolean )
at RedGate.SQLCompare.Engine.Work.ProcessDifferences(Differences differences, Difference onlyDifferenceToInclude, Options options, Boolean runOnTwo)
at RedGate.SQLCompare.Engine.Work.BuildFromDifferences(Differences differences, Difference onlyDifferenceToInclude, Options options, Boolean runOnTwo)
at RedGate.SQLCompare.Engine.Work.BuildFromDifferences(Differences differences, Options options, Boolean runOnTwo)
at DataCompare.DataCompare.CompareAndUploadData(String server, String _db1, String _db2, String[] tables) in \UpdateProduct\UpdateProduct\RegdateCompare.cs:line 154


A normal Schema compare works fine, its just the data compare that doesnt.
Has anyone else experienced this??

Thanks
Tony

Michelle T

Your problem is that you're using a mixture of SQL Compare and Data Compare syntax. Data Compare doesn't use the Work object - instead, you build the migration script from a ComparisonSession and a SQLProvider, along with a set of mappings for your tables.

So, after:

Database db1 = new Database();
Database db2 = new Database();

server = "dsk00248\\adviser_evol";
db1.RegisterForDataCompare(new ConnectionProperties("dsk00248\\adviser_evol", "UpdateProducts"));
db2.RegisterForDataCompare(new ConnectionProperties("dsk00248\\adviser_evol", "AEMain"));

You need to create mappings between your objects - here's code to set up default mappings:

SchemaMappings mappings = new SchemaMappings();
mappings.CreateMappings(db1, db2);

Then use a ComparisonSession to compare your databases:

ComparisonSession session=new ComparisonSession();
session.CompareDatabases(db1, db2, mappings);

Then you use a SqlProvider to get the execution block:

SqlProvider sqlProvider = new SqlProvider();
bool runOnTwo = false;
ExecutionBlock executionBlock = sqlProvider.GetMigrationSQL(session, runOnTwo);

Now you can use that execution block like the one you would have got out of the Work object.

Is there anything else you wanted to use the Work object for?

You can find a better-explained example at http://help.red-gate.com/help/SQLDataCo ... /index.htm under 'Worked Example'.

danielstony

Hi,

Thanks for the reply,

We subsequently tried the GetMigrationSQL but it only seemed to return the schema differences, not the data differences. Is there a setting that we should look out for the make it return data not schema?

This is the code we used, I think we are really close now .

Database db1 = new Database();
Database db2 = new Database();
SchemaMappings mappings = new SchemaMappings();

//Should check if this is true
LiveDatabaseSource liveDb = project.DataSource1 as LiveDatabaseSource;
liveDb.ServerName = "dsk00248\\adviser_evol";

db1.RegisterForDataCompare(liveDb.ToConnectionProperties(), Options.Default);

//Should check if this is true
liveDb = project.DataSource2 as LiveDatabaseSource;
liveDb.ServerName = "dsk00248\\adviser_evol";
db2.RegisterForDataCompare(liveDb.ToConnectionProperties(), Options.Default);

mappings.Options = project.Options;
mappings.CreateMappings(db1, db2);

//Disable any mappings here that you may want....
ComparisonSession session = new ComparisonSession();
session.Options = project.Options;
session.CompareDatabases(db1, db2, mappings);

m_TableDifferences = session.TableDifferences;

SqlProvider provider=new SqlProvider();
provider.Options = session.Options;
ExecutionBlock block;
block = provider.GetMigrationSQL(session, new SelectionDelegate(SyncRecord), true);

Thanks
Tony

Michelle T

I'm not sure how you're getting it to provide schema differences - when I use similar code (but taking out the SelectionDelegate from the GetMigrationSQL method, not setting up the options, and using 'new ConnectionProperties' with strings instead of taking data sources out of a project) I get data differences.

I can't run the code snippet without the following information:

1) How do you set up your 'project' object?
2) Where are you getting 'SyncRecord' from?