Drop Users? I hope not!

npaterson

Hi, this is my first time programming against the toolkit, it looks promising. However, I wrote some code to sync the main objects of two databases - i.e. tables, procs, udfs and views. However, when I dump the script to a multiline textbox, the script it generates will drop users - I certainly don't want that to happen. It's also including script to sync the data, I'd like to handle that in a seperate process. Please advise. Here's my code

foreach (Difference difference in DevStageDiff)
{
txt.Append(difference.Type.ToString() + "\t" + difference.DatabaseObjectType.ToString() + "\t" + difference.Name + System.Environment.NewLine + System.Environment.NewLine);
//// All objects that differ are selected for synchronization by default. However, you should
//// explicitly set the Selected property to true for the objects that you want to synchronize;
//// this will ensure the objects are included in the synchronization script when you script
//// the differences.
if (difference.Type == DifferenceType.Different && (difference.DatabaseObjectType == ObjectType.Table || difference.DatabaseObjectType == ObjectType.Rule
|| difference.DatabaseObjectType == ObjectType.Field || difference.DatabaseObjectType == ObjectType.Function
|| difference.DatabaseObjectType == ObjectType.Index || difference.DatabaseObjectType == ObjectType.StoredProcedure
|| difference.DatabaseObjectType == ObjectType.Trigger || difference.DatabaseObjectType == ObjectType.UserDefinedType
|| difference.DatabaseObjectType == ObjectType.View))
{
difference.Selected = true;
}
else
{
difference.Selected = false;
}


Work work=new Work();
work.BuildFromDifferences(DevStageDiff,Options.Default | Options.IgnoreUsers | Options.NoSQLPlumbing ,true);

ExecutionBlock block = work.ExecutionBlock;

this.txtCode.Text = "Block Code:" + System.Environment.NewLine + block.GetString();

Brian Donahue

Hi,

Your code will include object types of 'user' if the user is missing in the target database. Your loop would only exclude users if they existed in both databases, but are different. You'll want to put an additional condition in for DifferenceType.Missing and ObjectType==DatabaseObjectType.User.

SQL Compare Engine is incapable of migrating data, so I'd assume that what is happening is that one of the conditions that force a table to be rebuilt has been met, and you're seeing in the script a series or queries to copy the data from the old table to the new one. These conditions are detailed in our knowledge base here:

http://www.red-gate.com/support/kb/KB200708000113.htm

Michelle T

Option.IgnoreUsers doesn't actually ignore users - it just ignores permissions held by users and the role membership of users. It's equivalent to the 'Ignore Users' permissions and role memberships' option in the UI - the name is just shortened for the API.

As written, your code isn't working because your work.BuildFromDifferences statement is within your foreach loop, which means one difference gets processed and then you build from differences, leaving all the others still selected (including those users). When I ended the foreach loop after the if statement, it started working fine.

The script that appears to be synchronizing the data is in fact just ensuring the current data which is in the target database survives the transition to the new schema - it isn't bringing data over from the source and shouldn't change the data in the target except where this is unavoidable because the schema doesn't support the old data any more.

(Also, do you actually mean 'difference.Type == DifferenceType.Different' in your 'if' statement? This won't catch objects which differ but are only in one or the other database (DifferenceType.OnlyIn1 or DifferenceType.OnlyIn2).

You don't have to worry about selecting differences with a difference.Type of DifferenceType.Equal - their difference.Selected property won't even be checked because they're alredy equal.)

If you are very concerned about possibly synchronizing objects which are not of the types you have specified, you will also need to specify your own set of options rather than using Options.Default. Options.Default contains the option Options.IncludeDependancies, which ensures that your synchronization script will work but may do so by synchronizing objects you haven't explicitly selected (but that one of your explicit selections depends upon).

npaterson

Thanks for your help, there's just one small correction:
"As written, your code isn't working because your work.BuildFromDifferences statement is within your foreach loop"

work.BuildFromDifferences statement is not within the foreach loop (you can see my modification includes removing the option.default and specifying options now):

foreach (Difference difference in DevStageDiff)
{
//// All objects that differ are selected for synchronization by default. However, you should
//// explicitly set the Selected property to true for the objects that you want to synchronize;
//// this will ensure the objects are included in the synchronization script when you script
//// the differences.
if (difference.Type == DifferenceType.Different && (difference.DatabaseObjectType == ObjectType.Table || difference.DatabaseObjectType == ObjectType.Rule
|| difference.DatabaseObjectType == ObjectType.Field || difference.DatabaseObjectType == ObjectType.Function
|| difference.DatabaseObjectType == ObjectType.Index || difference.DatabaseObjectType == ObjectType.StoredProcedure
|| difference.DatabaseObjectType == ObjectType.Trigger || difference.DatabaseObjectType == ObjectType.UserDefinedType
|| difference.DatabaseObjectType == ObjectType.View))
{
difference.Selected = true;
txt.Append(difference.Type.ToString() + "\t" + difference.DatabaseObjectType.ToString() + "\t" + difference.Name + System.Environment.NewLine + System.Environment.NewLine);

}
else
{
difference.Selected = false;
}
if (difference.DatabaseObjectType == ObjectType.User)
{
difference.Selected = false;
}

}
// Use the default folder structure for any new files
//WriteToFileOptions folderOptions = new WriteToFileOptions();
//Create a Work object, and build it from the differences generated in the previous step.
//Use the ExecutionBlock property to access the script to synchronize the databases.
Work work=new Work();
work.BuildFromDifferences(DevStageDiff, Options.IgnoreUsers | Options.IgnoreFileGroups
| Options.IgnoreFillFactor | Options.IgnoreUserProperties | Options.IgnoreWhiteSpace | Options.IgnoreCollations
| Options.IgnoreWithElementOrder | Options.IgnoreExtendedProperties | Options.IgnoreQuotedIdentifiersAndAnsiNullSettings
| Options.IgnoreFullTextIndexing | Options.IgnoreStatistics | Options.NoSQLPlumbing | Options.IgnoreOwners | Options.IgnorePermissions, true);