Upgrade pitfalls from version 3.0...

dhs2002

Hi everyone. I have been using the 'sql pro bundle' to do a couple of specific, automated tasks since late 2005. I am fairly confident that what I am using out of the bundle are these two products:

SQL Data Compare Pro 3.0 -> 5.0
SQL Toolkit 3.0 -> 6.0

I would leave well enough alone but one of the 2 servers that we keep synced with Data Compare is going to MSSQL 2005 soon. So I need to revisit/upgrade the sync solution.

I use the data compare pro to do straight forward command line automation comparison and updating 5 tables one sql server to another.

I use the SQL toolkit (API) with VB.net 2003, net framework 1.1 with references to Redgate.SQL.Shared and Redgate.SQLDatacompare.Engine to compare two tables and then poll the results to discover what was changed. I think this is also pretty straight forward use of the Data Compare.

What I was wondering is are there any pitfalls here? I understand that I can sync between MSSQL 2000 and 2005 servers with the newer version of redgate. I am not sure about the .net framework or vb version as well as if there are any backward compatiblity issues with functions/methods etc. Ideally this upgrade would require minimal to no code changes.

Any insight would be helpful. I am putting this out there now rather than downloading the free trial to try and avoid any potential headaches. You input is appreciated.

richardjm

We did change the API quite a lot from version 3 to version 5 (version 6 is in Alpha with almost no changes). Mainly this is for the better. There is a very good example in the SQL Data Compare webhelp.

Mainly we now use the SQL Compare API for registering the databases. Setting up a comparison session now involves use of the new Mapping API which is in SQL Data Compare - the main thing you may be interested in there is the TableMappings object which you can join tables that need to be compared together and pass that object into a call to CompareDatabases. There is plenty of help on the SQL Toolkit forum on this subject but I wouldn't imagine it'd take you much time to change over your code.

HTH

Michelle T

If you're thinking of upgrading to Toolkit 6, bear in mind that version 6 is .NET 2.0 only - 5 is the last toolkit that supports .NET 1.1.

dhs2002

Thank you for the replies. That is a fantastic help and thank you for making this software that makes at least a few tasks simple, reliable, and easy. I wish everything worked so well.

dhs2002

I have been converting my program over to the version 5 of the Data Compare API.

I am having trouble figuring this part out from the documentation. I need to identify records that only exist in table 1 and ones that only exist in table 2. I use this to identify items that have been added and deleted from the first table.

In version 3 I used something like this and then just stepped through the rows of each. The .In1Records was very handy for this but does not seem to be carried over into version 5.

Dim NewRecords As ResultsStore = difference.In1Records
Dim DelRecords As ResultsStore = difference.In2Records

In version 5, how does one identify the records that only exist in one table or the other?

Thanks,
David

Brian Donahue

Hi David,

I think that the idea now is to use the Reader to loop through each row in the store the same way you would use, say, a SqlDataReader.

public void RunExample()
		{
			Database db1=new Database();
			Database db2=new Database();
 
			db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
			db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
 
			using(ComparisonSession session=new ComparisonSession())
			{
 
				// Create the mappings between the two databases
				TableMappings mappings = new TableMappings();
				mappings.CreateMappings(db1.Tables, db2.Tables);
 
				session.CompareDatabases(db1, db2, mappings);
 
				Reader resultsReader=session.TableDifferences["[dbo].[Widgets]"].ResultsStore.GetReader();
 
				// work out the two positions in the fields
				int description1=resultsReader.Fields["Description"].OrdinalInResults1;
				int description2=resultsReader.Fields["Description"].OrdinalInResults2;
 
				// work out the position of the RecordID column
				// this is part of the unique index we are comparing on
				int recordID=resultsReader.Fields["RecordID"].OrdinalInResults1;
 
				// sort by description in the first database
				resultsReader.SortOrder.ColumnSorts.Add(new ColumnSort(resultsReader.Fields["Description"], true));
				resultsReader.ApplySort();
 
				foreach(Row row in resultsReader)
				{
					if (row.Type==Row.RowType.In1) Console.WriteLine("Record {0} only exists in database 1", row.Values[recordID]);
					if (row.Type==Row.RowType.In2) Console.WriteLine("Record {0} only exists in database 2", row.Values[recordID]);
					Console.WriteLine();
				}
			}
			db1.Dispose();
			db2.Dispose();
		}

dhs2002

Ahh there it is. In1 and In2.
Thanks!