locked
Re-publishing error: Schema update is terminating...? RRS feed

  • Question

  • Hi,

    I really missed reading some important threads on re-deploying an LS Project,  now I am in a mess. 

    Upon Re-publishing my project, I got this error:

    Error 69 .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 10 Rows were detected. The schema update is terminating because data loss might occur. C:\Program Files\MSBuild\Microsoft\VisualStudio\LightSwitch\v2.0\Publish\v1.0\Microsoft.LightSwitch.Publish.targets 1148 10 TMIS

    I'm sorry if my query redundants other threads regarding this issue.  Though unanimously suggested to use SQL Compare Tools to push any schema changes in my database, my problem is that I can't install SSDT in my desktop due to hardware incompatibility issues.

    My question is: Is there any alternative solution to solve this error? Is there a way I can set my project as a fresh unpublished project? Or anyone out there have solved this issue in a simpler way?

    I appreciate so much any help extended, Thanks.

    KharolJ

    Saturday, December 22, 2012 2:44 AM

Answers

  • KharolJ:

    Don't try to delete the ApplicationDatabase.mdf from your project.  That is the intrinsic database.  The problem is in the database that was created when you published. To delete the records from that database, you need to connect to the database with a tool like SQL Server Management Studio.

    Another thing that you can try is to just change the name of the database in the publish wizard.  This will create a new database with the new name and you will avoid the problem that you are having altogether.

    Mark

    • Marked as answer by kharolJ Thursday, December 27, 2012 9:07 AM
    Thursday, December 27, 2012 2:14 AM

All replies

  • It looks like you've made some changes that require SQL server to drop a table & then recreate it (this is just the way SQL server works). When the table has data in it, LightSwitch errs on the side of caution, & refuses to perform the update that could possibly cause data loss.

    If you have VS Premium, or above, you could use the built-in database tools that come with it to compare/update the schema.

    If not, you could try manually making the required changes in SSMS (SQL Server Management Studio). Then when you publish your application, LightSwitch should no longer see a problem, because the project's schema & the database schema will match.

    I think this will work. I think I've had to do this myself, but it was a while ago, as I tend to use attached databases these days. Make sure you backup both the LightSwitch project & the published database before trying to manually make any changes to the published database.

    What "hardware incompatibility issues" are causing you to not be able to install SSDT? How long ago did you try? There's a fairly recent update available, so maybe whatever problem you're having has been fixed?. I haven't heard of any hardware reasons that prevent installing SSDT, which is why I ask the question.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Saturday, December 22, 2012 4:55 AM
    Moderator
  • KharolJ:

    I have a couple of ideas for you:

    As Yann said, it looks like there is a difference in the schema of one or more tables between the LightSwitch project and your published database that would cause the publishing the wizard to have to drop and recreate one or more tables.  You are getting the error because there is data in the table(s) that would be lost if a table was dropped.

    If you don't have a lot of data in the database and if you can just delete the data, the publish should then work.  That's the easiest fix if possible.

    If you have a lot of data, you can do this:

    1) Backup your published database and your LightSwitch project.

    2) Rename the published database.

    3) Publish your project again; You will now have a new, empty database.

    4) If you want to move some data from your previous (renamed) database, you can use the SQL Azure Migration Wizard (free download from codeplex.com). The migration wizard will work on standard Microsoft SQL as well as SQL Azure. The following link is a thread that explains how to do this:

    Azure Update publish fails

    God Luck,

    Mark


    • Edited by Yann DuranModerator Thursday, December 27, 2012 12:44 AM Fixed link txt, added a link to SQL Azure Migration Wizard download
    Wednesday, December 26, 2012 11:54 PM
  • Hi,

    Best regards to everyone, nice to be back from holiday vacation.

    That's true, I made some changes to my database tables.  Its just too late for me to knew that changing the table's structure could cause such problem.

      I just want to add that I am not particular for any data loss since I am just in a trial stage.  I tried deleting the "ApplicationDatabase" file from ...bin\data but still the issue persist.  I do believed that the intrinsic database file has nothing to do in publishing the project since the data are not carried out in the finished application.  I also tried uninstalling the application but still with no success.  So if MArk is suggesting to delete those records, what particular file should I delete?

    Thank you so much and best wishes for the coming new year.:)

    KharolJ

    Thursday, December 27, 2012 1:47 AM
  • If you're not worried about data loss in your published database, you can delete the published database using SSMS (don't manually just delete the file, delete the database in SSMS only). A complete new database will be created for you when you publish/install the application again.

    SSMS is where you'd delete the data too, if you were following Mark's suggestion. But seeing as you have a schema mismatch as well as data you don't care about, it's simpler & easier to just delete the whole database, & let it be recreated.

    I would never, EVER, delete the ApplicationDatabase file in bin\data. If you're lucky, LS 2012 will recreate it. If you're unlucky, you may not be able to debug your application any more. It may have improved in LS 2012, but deleting this file in LS 2011 caused big problems (I know because it happened to me).


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Thursday, December 27, 2012 2:08 AM
    Moderator
  • KharolJ:

    Don't try to delete the ApplicationDatabase.mdf from your project.  That is the intrinsic database.  The problem is in the database that was created when you published. To delete the records from that database, you need to connect to the database with a tool like SQL Server Management Studio.

    Another thing that you can try is to just change the name of the database in the publish wizard.  This will create a new database with the new name and you will avoid the problem that you are having altogether.

    Mark

    • Marked as answer by kharolJ Thursday, December 27, 2012 9:07 AM
    Thursday, December 27, 2012 2:14 AM
  • "Another thing that you can try is to just change the name of the database in the publish wizard.  This will create a new database with the new name and you will avoid the problem that you are having altogether"

    Yes Mark, that's a good suggestion!


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    Please click "Mark as Answer" if a reply answers your question. Please click "Vote as Helpful" , if you find a reply helpful.
     
    By doing this you'll help others to find answers faster.

    Thursday, December 27, 2012 3:01 AM
    Moderator
  • Hi Mark and Yann,

    Again thank you for sharing your precious time.  HOw I wish I can use SSMS to perform your suggestion, that would mean additional knowledge for me.  But installing the SSMS2012 express edition in my machine does not allow.  I already tried it twice but the same error is popping saying that my machine does not meet the minimum requirement to install the product. I have a 2.2ghz Celeron Processor,  1GB RAM, and 32bits OS. I actually have a 1.0 windows experience index.

    Nevertheless I tried your second suggestion and it just work fine and perfect.

    I would never, EVER, delete the ApplicationDatabase file in bin\data. If you're lucky, LS 2012 will recreate it. If you're unlucky, you may not be able to debug your application any more. It may have improved in LS 2012, but deleting this file in LS 2011 caused big problems (I know because it happened to me).

    Yann, Its good you shared your experience about deleting the intrinsic database manually.  I  have tried it thrice actually and thanks God I didn't met any problem.  I will refrain from doing it from now on.

    KHarolJ

    Thursday, December 27, 2012 9:07 AM