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 differing versions or configuration then tools may be in a different place. VS seems to be a bit like that. The first thing to do is open SQL Server Object Explorer (SSOE) in Visual Studio – use the keyboard shortcut Ctrl+\ followed by Ctrl+S if there’s no GUI option visible. The options referenced below are in the resulting SSOE panel.
Use Add SQL Server (command strip or right-click top of hierarchy) to add your remote and local database servers. If you’re using the internal database for development, this should be accessible as (localdb)\v11.0 with Windows authentication. The remote database uses your normal Azure credentials used in publishing.
Find the local database for your application. I find I sometimes have to start with debugging to get my database to appear. Right click and select Schema Compare. In the resulting window, pull down the Select Target dropdown, Select Target, radio option Database and pick your remote database. There’s a gear icon in the middle of the top command strip, click that and on the General tab tick Allow Incompatible Platform. Then click Compare.
The comparison output is a list of tables (and views etc) which differ, source on the left and target on the right. They’re grouped by the corrective action needed – Delete, Change, Add. The problem is likely to be in the Change section. You can probably ignore any aspnet tables and focus on the data tables you’ve created. Highlight anything suspicious and the bottom panels will give you a diff between the CREATE TABLE statements that would be needed to generate them. Anything in red on the target (Azure) side is potentially the cause of the problem – foreign key and nullable differences are particularly likely candidates.
There is an Update button which will bring the target schema in line with the source schema. I’ve never run this. 🙂 If you make sure only the table or tables that need fixing are ticked in the Action column and then click the Script icon on the command strip (Generate Script – Shift+Alt+G) then you’ll get the SQL needed to manually fix the problem using a query window of your choice. If you have any data in the affected tables, the script will refuse to run because of potential data loss. It’s obviously up to you decide if you want to manually override it.
For me the solution was bringing the schemas back into line and redoing the changes in an acceptable way. That might not be the solution for you but this will at least give you an insight into what is causing the error.