Red Gate forums :: View topic - Problem while running the package [exe] created using SDK
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Problem while running the package [exe] created using SDK

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
sakthivenkatesh



Joined: 17 Oct 2008
Posts: 3

PostPosted: Fri Oct 17, 2008 6:11 pm    Post subject: Problem while running the package [exe] created using SDK Reply with quote

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'

[code]
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();
}
[/code]
Back to top
View user's profile Send private message
Michelle Taylor



Joined: 30 Oct 2006
Posts: 525
Location: Red Gate Software

PostPosted: Tue Oct 21, 2008 11:34 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group