Debugging Lightswitch publishing errors using SQL Compare

Visual Studio Lightswitch is Microsoft’s rapid development environment for Silverlight and HTML5. A common use case is to develop completely locally using the built in SQL database and then publish code and schema to a remote server and database. This generally works well but if you follow a develop-publish-develop-publish cycle then sometimes your local database schema changes will be rejected by the remote server. When this happens, you’ll typically get one of two unhelpful errors: An exception occurred when deploying the database for the application. Failed to instantiate Microsoft.Data.Schema.Sql.SchemaModel.ExternalPropertyAnnotation because it was not registered. or Incorrect syntax near ‘MULTI_USER These usually mean that there’s a change in your local schema which the remote server can’t apply. A typical scenario would be adding a One-To-Many foreign key relationship to a table with existing data, implicitly creating a required field. The foreign key constraint can’t be applied because there’s no data in the column yet. I had both errors trying to publish a Lightswitch application to Windows Azure. I tracked down and fixed the problem by using the tools available in Visual Studio. Since I’d seen only scattered references and hints to using the tools in this way, I thought I’d post this quick walkthough. This was originally written to update this thread on social.msdn.microsoft.com I’m using Visual Studio Professional 2012 Update 3 configured for Lightswitch development with the current version of SQL Server Data Tools (SSDT) installed. You’ll certainly need reasonably up to date versions of both and if you have […]