data in new table not packaged

paul

SQL Bundle 5.2

RedGate.SQL.Shared.dll 5.3.0.1
RedGate.SQLCompare.Engine.dll 5.2.0.32
RedGate.SQLDataCompare.Engine.dll 5.2.0.40
RedGate.SQLPackager.Engine.dll 5.2.0.49


We are programmatically creating an upgrade package. The only difference between the 2 databases is that the latest version database has a new table with data. Upon execution of the upgrade package the new table is created but no data is present.

If we use the SQL Packager GUI to produce the upgrade package, the upgrade package works correctly and creates the new table as well as the data within it. So, the problem must lie within the code creating the data script part of the package.

Upon stepping through the code and viewing the data script we can see that there are no insert statements for the data rows in question. Further testing shows that we can use the programmatic method to successfully update data but only in tables that already exist in both databases.

We are using the default options in creating the data block.

Any ideas as to why data is left out of the comparison if the table in question is new to the previous version database?

Any help is much appreciated.

Thanks,

Paul

Brian Donahue

Hi Paul,

When creating a package, the first step is to create a schema script and a data script. On update packages, the schema is created, but because the table doesn't exist yet in the other database, the data is not scripted.

Luckily, there is an option in Data Compare's engine that will create a 'fake' mapping for the missing table that you can specify before creating the mappings:

mappings.Options.MappingOptions=MappingOptions.Default | MappingOptions.MissingFrom2AsInclude;
				mappings.CreateMappings(db1, db2);

The MissingFrom2AsInclude option will do this mapping for you automatically so that the data from the table missing in db2 will also be scripted.

paul

Ok, I understand the concept of why it is failing. Unfortunately, your suggestion didn't fix it.

When we use the Data Compare GUI we can see the new table has a status of UnableToCompare.

We have tried seemingly every option configuration with no luck.

Is there some other way to make it think the table exists in the other database even though it doesn't exist so we can get the insert data to show up?

Our code is creating upgrade packages just fine if they don't include a new table with data. That's the only problem we've encountered during testing as of yet.

This may require us to open a support ticket with you guys including some code exchange. If so, then we will go that route.

None of the examples included with the Bundle seem to show an upgrade package creation. Would it be possible to get ahold of one of those examples that includes this new table with data issue or would that require a support ticket?

Thanks,

Paul

Brian Donahue

Hello Paul,

The above solution was created to solve the problem that I described in my first reply; basically that if the table doesn't exist in DB2, then there is nothing to be compared and the upgrade package will fail.

Comparing the two databases in Data Compare will result in an object that could not be compared, for the very same reason that the table does not exist in the second database.

I'll need to look into this further to get some sample code cooked up.

Brian Donahue

Modifying the ComparisonSession example for the Data Compare API will cuase it to show inserts for tables that exist in DB1 but not in DB2...

public class ComparisonSessionExample
	{
		public void RunExample()
		{
			Database db1=new Database();
			Database db2=new Database();
			ComparisonSession session=new ComparisonSession();
			SchemaMappings mappings = new SchemaMappings();        
           
			try
			{
				db1.RegisterForDataCompare(new ConnectionProperties(".", "WidgetDev"), Options.Default);
				db2.RegisterForDataCompare(new ConnectionProperties(".", "WidgetLive"), Options.Default);
                
				// Create the mappings between the two databases
				
				mappings.Options.MappingOptions=MappingOptions.Default | MappingOptions.MissingFrom2AsInclude;
				mappings.CreateMappings(db1, db2);
				session.CompareDatabases(db1, db2, mappings);
                
				foreach (TableMapping mapping in mappings.TableMappings)
				{    
					TableDifference difference=session.TableDifferences[mapping.Obj1.FullyQualifiedName];                
					//loop through all the rows
					foreach(Row row in difference.ResultsStore)
					{
						//go through the non same records
						if (row.Type != Row.RowType.Same)
						{
							Console.WriteLine("{0} Row {1} type {2}", mapping.Obj1.FullyQualifiedName, row.Index, row.Type.ToString());
							int i=0;
							foreach (FieldPair field in difference.ResultsStore.Fields)
							{
								int field1=field.OrdinalInResults1;
								int field2=field.OrdinalInResults2;
                        
								if (field1 != field2)
								{
									// Get the values
									object value1=row.Values[field1];
									object value2=row.Values[field2];
									if (value1 == null)
										value1="NULL";
									if (value2 == null)
										value2="NULL";
									Console.WriteLine("{0}\t{1}\t{2}\t{3}",field.Field(false).Name, value1.ToString(),row.FieldDifferent(i)?"<>":"==",  value2.ToString());
								}
								else
								{
									// This is part of the unique index we are comparing on
									object value=row.Values[field1];    
									Console.WriteLine("*{0}\t{1}",field.Field(false).Name, value.ToString());
								}
								i++;
							}// End of foreach field pair
						}
					}// End of foreach row
				}// End of foreach mappings
			}
			finally
			{
				session.Dispose();
				db1.Dispose();
				db2.Dispose();
				}
		}
	}

paul

Thanks for the code, we found the problem..

We had to flip the database order in the mappings and flip the direction in the GetMigrationSQL function.. oops.. DB2 just wasn't the right DB2..

Thanks for your help and sorry to waste your time.

You guys really have a great product here but as with everything, there's a learning curve.. :-)

Thanks,

Paul

Brian Donahue

Hi Paul,

Thanks! I'm glad it's working!