Deactivating IncludeDependencies Still Includes CREATE USER

bsoddy

I've run into a peculiar problem with our publishing functionality, which utilizes SQL Compare against a null database in conjunction with SQL Packager to generate a stored executable. The executable acts as a snapshot of the database and is used for restorative purposes.

Even with the IncludeDependencies Option deactivated, I'm still finding that the generated executable contains CREATE USER statements, which can cause issues when restoring a publish.

Included is a scrubbed version of the executing code:

using (Database oDatabase = new Database())
using (ComparisonSession oSession = new ComparisonSession())
{
SqlProvider oProvider = null;
TableMappings oMappings = new TableMappings();
TableMappings oNewMappings = new TableMappings();
Options oOptions = Options.IgnoreFileGroups |
Options.IgnoreFillFactor |
Options.IgnoreWhiteSpace |
Options.IgnoreWithElementOrder |
Options.IgnoreDatabaseName;

// Register the database for schema and data comparison
oDatabase.RegisterForDataCompare(oConnectionProperties, oOptions);
oDatabase.Register(oConnectionProperties, oOptions);

// Build the schema ExecutionBlock
Differences oDifferences = oDatabase.CompareWith(null, oOptions);
foreach (Difference oDifference in oDifferences)
{
oDifference.Selected = true;
}
Work oWork = new Work();
oWork.BuildFromDifferences(oDifferences, oOptions, true);

// Since we're packaging up a single database, set the following flag
oMappings.Options.MappingOptions |= MappingOptions.MissingFrom2AsInclude;
oMappings.Options.MappingOptions |= MappingOptions.IgnoreCase;

// Create the mappings
oMappings.CreateMappings(oDatabase.Tables, null);
oNewMappings = oMappings;

oSession.Options = oNewMappings.Options;

m_oRedGateFormProgress = new FormRedGateProgress();
BackgroundWorker worker = new BackgroundWorker();

worker.WorkerReportsProgress = true;
worker.DoWork += delegate
{
try
{
oSession.CompareDatabases(oDatabase, null, oNewMappings);
m_oRedGateFormProgress.SetProgress(100);
}
catch
{

}
};
worker.RunWorkerCompleted += delegate
{
m_oRedGateFormProgress.Close();
};

m_oRedGateFormProgress.SetText("Comparing databases");
m_oRedGateFormProgress.SetUpFormForJustMessaging();
m_oRedGateFormProgress.Load += delegate
{
worker.RunWorkerAsync();
};

oSession.Status += new StatusEventHandler(RedGateStatusCallback);

m_oRedGateFormProgress.ShowDialog();
m_oRedGateFormProgress.Dispose();
worker.Dispose();

oSession.Status -= new StatusEventHandler(RedGateStatusCallback);

// Create a sql provider to get the insert SQL
oProvider = new SqlProvider();
oProvider.Options = oNewMappings.Options;

// Package the database to a temp local directory
using (ExecutionBlock oDataBlock = oProvider.GetMigrationSQL(oSession, true))
{
using (PackagerEngine oPackagerEngine = new PackagerEngine(Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location) + @\Template,
oPackageStore.FullName,
OutputFileNameNoExt,
oWork.ExecutionBlock,
oDataBlock,
OutputType.Executable))
{
oPackagerEngine.AddProperty("PACKAGE_UPGRADE_DATABASE", Convert.ToString(true));

oPackagerEngine.Package();
}
}
}

Brian Donahue

I don't see anything in the code that's specifically excluding users from the set of selected differences. You may want to add that condition in the bit where you run through all of the differences and select them.

bsoddy

Ah, updating the Difference.Selected portion for DatabaseObjectType.User resolved this.

Thanks for the reply, Brian!