PSIcapture Troubleshooting: Scripting Error: Error applying database schema. The following error(s) occurred: Error executing script: This column already had a default.

Problem:

User Upgrades machine to 3.6.1.62 and ensures that the product can open. BatchManager is working and you can view all batches. User then upgrades to version 4.6.1.24. The Services will not start and you look in the logs and find this error.

[5/13/2014 10:23:44 AM - Error - v4.6.1.24]
Error applying database schema.The following error(s) occurred:
Error executing script:
This column already had a default.

ALTER TABLE [ManagedBatches] ALTER COLUMN Closed SET DEFAULT 0

   at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
   at PSIGEN.Capture.Data.DatabaseSchema.ExecuteScript(DbConnection cnn, DbTransaction transaction, String script)


   at PSIGEN.Capture.Data.DatabaseSchema.ApplySchema()
   at PSIGEN.Capture.Data.BatchManager.BatchManagerDataLayer.CreateDatabase(String dataPath, Assembly resourceAssembly, String schemaResource, String dataName)
=== INNER EXCEPTION ===
The following error(s) occurred:
Error executing script:
This column already had a default.

ALTER TABLE [ManagedBatches] ALTER COLUMN Closed SET DEFAULT 0

   at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
   at PSIGEN.Capture.Data.DatabaseSchema.ExecuteScript(DbConnection cnn, DbTransaction transaction, String script)


   at PSIGEN.Capture.Data.DatabaseSchema.ApplySchema()

Solution:

Open the BatchManger.sdf that will not upgrade in Linqpad.

Run the following Syntax:  ALTER TABLE [ManagedBatches] ALTER COLUMN Closed DROP DEFAULT

BatchManager.sdf can now be run directly into the Batch Manager ProgramData folder for 5.0.2.x+.

Development Explanation:
Back in v. 3.1.1, we had that default value on the Closed column.  Sometime prior to v4.1.1, it was removed from the database creation script.  However, a matching DROP DEFAULT was never added to any of the database upgrade scripts.

The only time you’ll have the issue is when upgrading from a version earlier than (or perhaps equal to) v4.1.1.