no default and does not allow NULL values

npaterson

Thanks Redgate for the previous support.
New question, I get the following warning in the code I've written utilizing the toolkit compare api:

"[LOYALTY_DISCOUNT] on table [dbo].[CATALOG_IDENTIFIER] must be added but has no default and does not allow NULL values"

Your help would be appreciated, the code below is my code:

private void btnCompare_Click(object sender, EventArgs e)
{
//using (Database widgetStaging = new Database(),
// widgetProduction = new Database())
//{
// // Connect to the two databases and read the schema
// widgetStaging.Register(new ConnectionProperties(".", "WidgetStaging"), Options.Default);
// widgetProduction.Register(new ConnectionProperties(".", "WidgetProduction"), Options.Default);

// // Use the databases...
//}
using (RedGate.SQLCompare.Engine.Database dbSource = new RedGate.SQLCompare.Engine.Database(),
dbTarget = new RedGate.SQLCompare.Engine.Database())
{
StatusLabel.Text = "Status: Comparing Databases...";
Cursor.Current = Cursors.WaitCursor;
Application.DoEvents();
try
{
dbSource.Register(new ConnectionProperties(this.txtSourceServer.Text, this.cbSourceDB.Text), Options.Default);
dbTarget.Register(new ConnectionProperties(this.txtTargetServer.Text, this.cbTargetDatabases.Text), Options.Default);
//Use the CompareWith method to generate a Differences collection.
Differences DevStageDiff = dbSource.CompareWith(dbTarget, 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);

StringBuilder txt = new StringBuilder();
// Iterate through the collection to examine the schema differences in detail, and set the
// Selected property to true for the objects that you want to synchronize, or false for
// those that you do not.

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.Type == DifferenceType.OnlyIn1) && (difference.DatabaseObjectType == ObjectType.Table
|| 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))
//if (difference.Type == DifferenceType.Different && (difference.DatabaseObjectType == ObjectType.Table || 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;
}
//We don't want to copy over any users that exist in the source:
if (difference.DatabaseObjectType == ObjectType.User || difference.DatabaseObjectType == ObjectType.Schema)
{
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);

if (work.Messages.Count > 0)
{
StringBuilder err = new StringBuilder();
for (int i = 0; i < work.Messages.Count; i++)
{
err.Append("Work Errors: " + work.Messages.Text + System.Environment.NewLine + work.Messages.WarningSeverity.ToString());
}
this.txtErrors.Text = err.ToString();

}
StringBuilder warn = new StringBuilder();
foreach (RedGate.SQLCompare.Engine.Message message in work.Warnings)
{
warn.Append("WARNING: " + message.Text + System.Environment.NewLine);
}
this.txtWarnings.Text = warn.ToString();
//Perhaps this should be work.ScriptObject???
ExecutionBlock block = work.ExecutionBlock;

this.txtCode.Text = "Block Code:" + System.Environment.NewLine + block.GetString();
///////////////////////////////////////////////////////////////////////////////////////////////
/*
Instantiate a BlockExecutor object and call the ExecuteBlock method to run the SQL script. Ensure you run it on the correct database
You can receive feedback events about the progress of the comparison and synchronization by using the ICancellable interface
*/

// REM BlockExecutor executor = new BlockExecutor(); executor.ExecuteBlock(block, this.txtTargetServer.Text, this.cbTargetDatabases.Text);
////////////////////////////////////////////////////////////////////////////////////////////////
/*
Dispose of the objects
The Database and ExecutionBlock objects make extensive use of temporary files. To ensure that these files are deleted you must dispose of the objects you have used.
*/
dbSource.Dispose();
dbTarget.Dispose();
block.Dispose();
StatusLabel.Text = "Status: Completed Comparing Databases.";
Cursor.Current = Cursors.Default;
Application.DoEvents();
this.txtDiff.Text = txt.ToString();
}
catch (Exception ex)
{
DoError(ex.ToString(), ex.Source.ToString());
return;
}
}
}

Michelle T

That's a warning to do with your database layout, not your code:

If you add a column to a table, and it doesn't have a DEFAULT value set, and it does have NOT NULL set, then SQL Server doesn't know what values to fill the new column with in existing records, and may throw an error about this.

So we warn you if you're adding a column like this, as the synchronization might fail because of SQL Server throwing that error.

You can fix this by putting a default on the column, or allowing NULL values in it, so that SQL Server can fill it with appropriate values. If you have no data in that table, you can just ignore the warning.

npaterson

Hi, thanks for your help.