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.

Belvabelvedere

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.

Sharondasharos

Hi Yann, So are you saying there is no way to trace the source of the error to the property that is causing it? – Belvabelvedere

Not that I know of, no. That's why I say to always publish straight after making any changes to a property. If your data doesn't matter, you could delete the database, & it'll be recreated when you publish the next time. If your data's important, you might have to manually check your LightSwitch tables against the tables in the database. – Sharondasharos

Hi Yann, The data is important. I is not likely that I will be able to find the problem by checking the Lightswitch tables against the database. However, I can restore the database from backup prior to the changes that are so offensive. Can I do that and delete the new tables in Lightswitch that SQL found to be so offensive? – Belvabelvedere

Hi Yann, The data is important. It is not likely that I will be able to find the problem by checking the Lightswitch tables against the database. However, I can restore the database from backup prior to the changes. Can I do that and delete the new tables/properties in Lightswitch that SQL found to be so offensive? These gottchas are so crippling I wonder why, with all of the other great LS capibilities, does MS not prevent those changes from happening in the first place. I have spent many hours working around the issue, including publishing every few minutes. Not often enough. Thank you – Belvabelvedere

Also, can I just start a new application and attach it to the existing database? Assumming that SQL and LS are, as you say, out of sync, I am trying to figure the best way out of this mess. Restore the SQL from backup? If I restore the SQL db how can I be sure the current LS app will match it? – Belvabelvedere

If I were to pay for a Technet Incident do you think MS techs would be able to help me sort this out? – Belvabelvedere

Restoring a backup of the database won't help at all, however if you have a backup of the application that would match the database, that would work. Also, if you have access to some kind of schema compare tool (you could install SQL Server Data Tools), you could publish locally, then do a schema compare between the locally published database & the production database. I could fix it, so MS should be able to as well. – Sharondasharos

Would you be willing to be hired to fix it? – Belvabelvedere

If you'd like me to help fix it, I could do that, but that wasn't what I was getting at. I just meant I can see myself fixing it (as I've done it for my own projects), so MS Support should also be able to do it. You can email me (my first name at live dot com dot au), if you'd like to discuss it any further though. – Sharondasharos

Hi Yann, I ended up restoring the entire sql db from a backup prior to the error and used a copy of the VB LS project from prior to the error. A little new data was overwritten but not a significant amount. It is all good now. – Belvabelvedere

Answer 2

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

Synod

I found this setting on the clog on the SqlSchemaCompare tab – Dwaindwaine

this worked well for me. I was aware that recent changes were manually done on db hence project was out of sync. Ideally you should not choose this route unless you are fully aware of the database behavior. – Cesta

Answer 3

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

Strategic

I was getting this same error, because I added a new Field with NOT NULL condition to an existing Table. It is not important that the existing records contain NULL, because there are processes that will eventually update that field. I commented the "if exists" portion of the code, but then I got a different error message. ` --IF EXISTS (select top 1 1 from [dbo].[PROD_WHSE_ACTUAL]) -- RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT ` Next error message: ` Msg 4901, Level 16, State 1, Line 55` – Hindustani

Answer 4

If you are comparing two schemas in visual studio. Click on View Results in Data Tools Operations Menu.

Above each error there would be a comment on what caused your error. That would provide you error details.

Invitatory

Answer 5

In case you bumped into this question 10 years late after it was asked, becaause of you (Azure) pipleine, the reason might be that you need to update your .dacpac file, that is refference in your .yml file.

Vineland