The schema update is terminating because data loss might occur

I keep bumping into this error that is usually caused by some mistake I have made while building an application in Lightswitch. It is usually associated with relationships. I am ususally moving along and done a number of things before I publish the app and see the error. By this time it is difficult to calulate what I did wrong. Is there a way I trace this error back to see what I need to change in the tables?

Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.

Thank you.

Solution 1:

This error occurs when a change that you've made to an entity's property (in the table designer) would cause the entity's table in the published database to be dropped & recreated, and the table has data in it. This is just the way that SQL Server works, it's not under LightSwitch's control. However, LightSwitch errs on the side of caution, & doesn't permit an operation that might cause the potential loss of any data.

The types of things that might trigger this are:

• renaming a property
• changing it from required to not required etc
• changing a property's data type
• & even (if I remember correctly) changing the position of a property in the list of properties

You can however, add a property to the end of the list of properties, without triggering the table being dropped & recreated.

It's really not a good idea to make too many changes before attempting to publish the application, for exactly the reason you suggest - ending up not knowing what you've done. You especially want to publish after making any changes to the properties of an entity. Even if you just publish to a local SQL Server instance (including SQL Express) on your development machine, to "test out" your changes before you publish to the production server.

The way I've gotten around this (when I used to still used to use LS's intrinsic data, ApplicationData - I now use attached data sources) is to manually make the change to the column in the database itself, using something like SSMS (SQL Server Management Studio). You can then decide to allow the (potential) data loss. Be very careful though what you change, & make sure it's only the same as the change that you made to the property in LightSwitch. Of course back up the database before making any changes to it. If you cause the database to be out of sync with LightSwitch you will have major problems.

Solution 2:

One item that is a clue to the problem is in the error message. For example, in the error message above [Msg 50000, Level 16, State 127, Line 6], Line 6 refers to the line number in the .publish.sql file that is part of the build. This file can be found in the /bin/Debug subdirectory of your project. Since it is a text file, you can open the file with any text editor or with VS2012 to view the file and find the line number with the error.

In my particular problem, I was dropping a column that I no longer needed. In the .publish.sql file that was generated, I was able to locate the IF EXISTS line that checked for rows in the table that I was altering. Since I knew that this change was not destroying data I cared about, I just commented out the IF EXISTS line in VS2012, then executed the script again. The change took place as planned.

Please Note: This suggestion is overriding safety guards in place to protect your data. Only make this change if you feel comfortable making it. *

Solution 3:

When project publish fails with the error like 'The schema update is terminating because data loss might occur'

Right click on the project -> Publish Click on advanced, In general tab -> Check 'Block incremental deployment if data loss might occur', In drop tab -> Drop objects in target not in source Than Click ok to publish

Note: Sometimes when the above did not work try: "Uncheck" Block Incremental deployment and publish