Problem while running the package [exe] created using SDK

Forum for users of SQL Packager database archive utility

Problem while running the package [exe] created using SDK

Postby sakthivenkatesh » Fri Oct 17, 2008 5:11 pm


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.


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();

//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\"];
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(
ComparisonOptions.TrimTrailingSpaces | ComparisonOptions.Default,

//Gets the table names which should be compared.
string[] tables;
if (m_compType == 1)
tables = appSettings[\"Comp1_Tables\"].Split(';');
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))
if (m_compType == 1)
path = path + @\"\\\" + appSettings[\"Comp1_ScriptFileName\"];
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\");
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\"];
packageName = appSettings[\"Comp2_PackageName\"];
PackagerEngine engine = new PackagerEngine(TemplateFolder, packageFolder, packageName, schemaBlock, block, OutputType.Executable);

Posts: 3
Joined: Fri Oct 17, 2008 4:00 pm

Postby Michelle Taylor » Tue Oct 21, 2008 10:34 am

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.
Michelle Taylor
Posts: 537
Joined: Mon Oct 30, 2006 11:45 am
Location: Red Gate Software

Return to SQL Packager Previous Versions

Who is online

Users browsing this forum: No registered users and 0 guests