sql server - The schema update is terminating because data loss might occur

Question

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.

question from:https://stackoverflow.com/questions/14781137/the-schema-update-is-terminating-because-data-loss-might-occur

Answer 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.