Problem using DC7 API -- data is not being migrated properly

emes001

Alright, so I have a simple program where the user inputs 3 items: a servername, the name of the "model" database, and the name of a new database (to be created and modeled after "model" database).

What I don't understand is that 2 months ago, my program worked fine: the new database was created, the structure was transferred using the SQL Compare 7 API, and then the data was transferred using the SQL Data Compare 7 API. Now all of a sudden I reopen the project to find that only the structure is transferred, but not the data? It is possible that I modified my code and broke the program before leaving it 2 months ago, but I would hope not. Below is a snippet of what I've been using:

public static bool CreateNewDB(string serverName, string database, string modelServer, string modelDatabase) {
            Microsoft.SqlServer.Management.Smo.Server _server = new Microsoft.SqlServer.Management.Smo.Server(serverName);
            Microsoft.SqlServer.Management.Smo.Database _db = new Microsoft.SqlServer.Management.Smo.Database(_server, database);
            _db.Create();

            try {
                RedGate.SQLCompare.Engine.Database db1 = new RedGate.SQLCompare.Engine.Database();
                RedGate.SQLCompare.Engine.Database db2 = new RedGate.SQLCompare.Engine.Database();

                db1.Register(new ConnectionProperties(modelServer, modelDatabase), Options.Default);
                db1.RegisterForDataCompare(new ConnectionProperties(modelServer, modelDatabase), Options.Default);
                db2.Register(new ConnectionProperties(serverName, database), Options.Default);
                db2.RegisterForDataCompare(new ConnectionProperties(serverName, database), Options.Default);


                Differences stagingVsProduction = db1.CompareWith(db2, Options.Default);

                foreach (Difference diff in stagingVsProduction) {
                    diff.Selected = true;
                }

                Work work = new Work();
                work.BuildFromDifferences(stagingVsProduction, Options.Default, true);

                ExecutionBlock block = work.ExecutionBlock;

                BlockExecutor executor = new BlockExecutor();
                executor.ExecuteBlock(block, serverName, database);

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

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

                SqlProvider provider = new SqlProvider();
                ExecutionBlock block2 = provider.GetMigrationSQL(session, true);

                BlockExecutor executer2 = new BlockExecutor();
                executer2.ExecuteBlock(block2, serverName, database);
                

                db1.Dispose();
                db2.Dispose();
                block.Dispose();
                block2.Dispose();
                session.Dispose();
            }
            catch (Exception ex) {
                Business.FileTool.Append(@"C:\redgatelog.txt", ex.ToString(), "");
                return false;
            }
            return true;
        }

The code is called via:
if (Business.Database.CreateNewDB("localhost", "TestDB1", "localhost", "MyTemplateDB"))
// do something

In my catch, I append the exception thrown to the file "redgatelog.txt"... yet there has never been a modification to this file, and the function CreateNewDB always returns true... which makes me believe that the DataCompare ran successfully, but for some reason didn't (maybe bad SQL generated?).

I have my licenses.licx file set up correctly.

Brian Donahue

If your project suddenly stops working the way you describe, then it's more than likely a data driven error condition and not problems with the code.

My first thought is that maybe the tables had changed so that the indexes had been dropped and therefore the CreateMappings method returns no mappings. That would prevent the data from being scripted. Maybe adding some logging to write out which tables' data is going to be migrated would help.

Brian Donahue

I think this should give you the information that you need:

                foreach (SchemaMapping m in mappings)
                {
                    if (m.Obj1!=null&&m.Obj2!=null&&m.Include)
                    Console.WriteLine(m.Obj1.Name);
                }