Problem while running the package [exe] created using SDK

sakthivenkatesh

Hi,

I use the following code to compare databases, generate synchronization script and then package it using SDK. After the exe is created, when i try to run the exe i get the below error message. Why does this happen? I dont grant access to user inside the synchoronization script, when i run the script alone it runs fine. Let me know if i am missing any setting.

Error:

SQL Error: User or role 'iibkh' already exists in the current database
Assembly: SpectrumPackage
Package: Package1.resx
Batch: Batch9
Exec sp_grantdbaccess M'AM\iibkh', N'iibkh'

string sourceServer;
            string destServer;
            string sourceDB;
            string destDB;

            Database db1 = new Database();
            Database db2 = new Database();
            ComparisonSession session = new ComparisonSession();
            TableMappings mappings = new TableMappings();

            try
            {
                //Get the config information from app.config file.
                if (m_compType == 1)
                {
                    sourceServer = appSettings["Comp1_SourceServer"];
                    destServer = appSettings["Comp1_DestServer"];
                    sourceDB = appSettings["Comp1_SourceDatabase"];
                    destDB = appSettings["Comp1_DestDatabase"];
                }
                else
                {
                    sourceServer = appSettings["Comp2_SourceServer"];
                    destServer = appSettings["Comp2_DestServer"];
                    sourceDB = appSettings["Comp2_SourceDatabase"];
                    destDB = appSettings["Comp2_DestDatabase"];
                }

                //db1.RegisterForDataCompare(new ConnectionProperties(sourceServer, sourceDB), Options.Default);
                //db2.RegisterForDataCompare(new ConnectionProperties(destServer, destDB), Options.Default);

                db1.Register(new ConnectionProperties(sourceServer, sourceDB), Options.Default);
                db2.Register(new ConnectionProperties(destServer, destDB), Options.Default);

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

                mappings.Options = new EngineDataCompareOptions(
                MappingOptions.Default,
                ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,
                SqlOptions.Default);

                //Gets the table names which should be compared.
                string[] tables;
                if (m_compType == 1)
                {
                    tables = appSettings["Comp1_Tables"].Split(';');
                }
                else
                {
                    tables = appSettings["Comp2_Tables"].Split(';');
                }
                for (int i = 0; i < tables.Length; i++)
                {
                    mappings.Join(db1.Tables[tables[i]], db2.Tables[tables[i]]);
                }


                // Peform the comparison
                session.Options = mappings.Options;
                session.CompareDatabases(db1, db2, mappings);
                m_TableDifferences = session.TableDifferences;

                // now get the ExecutionBlock containing the SQL
                // we want to run this on WidgetLive so we pass on true as the second parameter
                SqlProvider provider = new SqlProvider();
                //
                // Also rememeber to set up the provider options
                //
                provider.Options = session.Options;
                ExecutionBlock block;

                //Gets the sync SQL scrpit.
                block = provider.GetMigrationSQL(session, true);

                //Generates the path to save the script file.
                path = appSettings["SaveScriptToPath"];
                string tmp = DateTime.Today.ToString("ddMMMyy", DateTimeFormatInfo.InvariantInfo);
                path = path + tmp;
                string packageFolder = path;
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                if (m_compType == 1)
                {
                    path = path + @"\" + appSettings["Comp1_ScriptFileName"];
                }
                else
                {
                    path = path + @"\" + appSettings["Comp2_ScriptFileName"];
                }
                block.SaveToFile(path, EncodingType.ASCII);
                StringBuilder sb = new StringBuilder(File.ReadAllText(path));
                if (m_compType == 1)
                {
                    sb.Insert(0, "Use SPECTRUMDATA\r\n");
                }
                else
                {
                    sb.Insert(0, "Use LOGISTICS\r\n");
                }
                File.WriteAllText(path, sb.ToString());

                //select all the differences
                foreach (Difference difference in schema)
                {
                    difference.Selected = true;
                }

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

                ExecutionBlock schemaBlock = work.ExecutionBlock;

                string TemplateFolder = appSettings["TemplateFolder"];
                string packageName; 
                if (m_compType == 1)
                {
                    packageName = appSettings["Comp1_PackageName"];
                }
                else
                {
                    packageName = appSettings["Comp2_PackageName"];
                }
                PackagerEngine engine = new PackagerEngine(TemplateFolder, packageFolder, packageName, schemaBlock, block, OutputType.Executable);
                engine.Package();
                
            }
            finally
            {
                session.Dispose();
                db1.Dispose();
                db2.Dispose();
            }

Michelle T

Okay, things which look wrong about this code:

1) You're trying to get both data and schema comparison off one Register call. That doesn't work. You need to have seperate Database objects for the data and schema comparison, calling RegisterForDataCompare on the ones you want to use for data comparison and Register on the ones you want to use for schema comparison.

2) You appear to only be saving out the data comparison script, which wouldn't have the grantdbaccess statement in it, that would be in the schema script contained in schemaBlock.

I'm not sure why you're getting the SQL error, but it may well be something to do with trying to reuse the same Database object for schema and data comparisons - if it persists after you've fixed that, get back to me and I'll see if I can work out what's causing it.