locked
Lightswitch 2013-Database schema not publishing RRS feed

  • Question

  • I have an existing application that has been running on Microsoft Azure since September.

    Within the last week, "Publish Database Schema" is not working. I am having to manually modify the Azure SQL database to get database schema changes to publish.

    Questions:

    1) Is there a "log file" that Lightswitch creates during publish that would help determine why the publish is not working?

    2) What tools might be utilized to determine why the schema changes are not publishing?

    3) Is it possible to publish to a new database just to test to see if that process will work on a new database?

    Bill


    Thank you, Bill

    Tuesday, May 13, 2014 8:19 PM

Answers

  • Can you try following the steps in this article to crank up the MSBuild verbosity, then try to publish.  Then look in the Output window for anything SQL or Database related:

    http://blogs.msdn.com/b/david_kidder/archive/2013/10/14/crank-up-your-visual-studio-build-output-to-11.aspx

    Also, there should be a build error stating that publishing the database failed, what is the full text of that message?



    David Kidder | Senior SDET | Microsoft | LightSwitch

    • Marked as answer by jessiedog Thursday, May 15, 2014 9:47 PM
    • Unmarked as answer by jessiedog Thursday, May 15, 2014 9:56 PM
    • Marked as answer by jessiedog Thursday, May 15, 2014 10:24 PM
    Thursday, May 15, 2014 5:32 PM
    Moderator
  • @David,

    Thank you for the link. Your link helped diagnose the issue.

    The solution I am using has been running live since Lightswitch 2010. Previously, it was running on a IIS server with a MS-SQL 2008 back end. The Lightswitch project ran on IIS from mid-2012 through mid-2013.

    In September 2013, I switched to Azure Cloud Service and Azure SQL.

    I have no idea why or how the publish is picking up the "Where should the package be created?" field which is causing the dacpac to be generated in the folder "D:\Temp\20130715\"

    I'm going to use your post on Publish Bankrupcy to remove the value in the "Where should the package be created?" and I suspect this challenge will be solved.

    @Dave and @Simon - thank you for your assistance and well.


    Thank you, Bill

    • Marked as answer by jessiedog Thursday, May 15, 2014 9:46 PM
    • Unmarked as answer by jessiedog Thursday, May 15, 2014 9:56 PM
    • Marked as answer by jessiedog Thursday, May 15, 2014 10:24 PM
    Thursday, May 15, 2014 9:46 PM
  • Have you tried just getting rid of all the publish settings and deleting the .user file yet?  I am thinking that one of the settings is still lurking in there from when you published to IIS and that is causing the dacpac pathing problem.  I think that it is still picking up this rogue setting when you try to publish to Azure. If it doesn't work after clearing out the publish-related values in the lsproj (or ls3proj or lsxproj, depending on what version of LightSwitch you are using) can you post the first part of your lsproj file?  Maybe I can find something.


    David Kidder | Senior SDET | Microsoft | LightSwitch

    • Marked as answer by jessiedog Thursday, May 15, 2014 10:24 PM
    Thursday, May 15, 2014 10:05 PM
    Moderator

All replies

  • Changes which may result in data loss will cause publication to fail (even if no data is actually lost).

    These changes include things like reducing the length of varchar or nvarchar columns, reducing the precision of decimals, changing decimals to integers, etc.

    If you have Visual Studio Professional or above, you should be able to use the Schema Compare feature to force these changes through separately and then publish your LightSwitch application.


    Simon Jones
    If you found this post helpful, please "Vote as Helpful". If it actually answered your question, please remember to "Mark as Answer". This will help other people find answers to their problems more quickly.

    Wednesday, May 14, 2014 7:53 AM
  • 3) Take a copy of the solution and delete the .suo and other project user settings files. Then you can test and deploy fresh.

    What schema changes have you made recently. Have you made any schema changes that may invalidate existing data?

    Run a schema compare to check for any changes.

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Wednesday, May 14, 2014 7:55 AM
  • @Simon & @Dave,

    Thank you for your replies.

    The Schema Compare results shown below utilized the local ApplicationDatabase as the source and the Azure SQL database as the target.

    The "Delete" results show many dbo.aspnet_* tables and views to delete.

    The "Change" results are variants of the following:

    Other than changes to the "Default", I don't see any other changes such as deletion of a field/property, changes to a field/property type or reduction of length of a field/property.

    One point I failed to state, there is no error message that is displayed. The application successfully deploys to Azure; Lightswitch opens a browser session to Azure upon completion.

    Screens for new tables are shown on the menu, when you select the screen the little red x's display.

    I've tried applying the schema changes on a table by table basis but get an error related to a foreign key violation when the script attempts to drop the table it wants to work on. I can manually go through an drop the foreign key restraint but am hesitant to do so because it seems it is something else causing the issue.


    Thank you, Bill



    • Edited by jessiedog Wednesday, May 14, 2014 3:56 PM added images
    Wednesday, May 14, 2014 3:49 PM
  • You say there are new screens for new tables. Why are they not showing in the schema compare? Have you already manually added them?

    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Wednesday, May 14, 2014 3:58 PM
  • @Dave,

    "3) Take a copy of the solution and delete the .suo and other project user settings files. Then you can test and deploy fresh."

    I've searched for *.suo files and am not able to locate any.

    Would you have a link to a list of *.suo and other project user setting files to delete?


    Thank you, Bill

    Wednesday, May 14, 2014 4:01 PM
  • After executing the schema repair and concluding there were no changes that would result in loss of data, I decided to try adding a new table and new screen to see what would occur.

    When I published to Azure, there was no error message, the table did not get added to Azure but the screen does show up when the Lightswitch application is executed.


    Thank you, Bill

    Wednesday, May 14, 2014 4:03 PM
  • Any .suo resides next to the solution file .sln and *proj.user files reside with the sub-project files. These cache various setting and responses.


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Wednesday, May 14, 2014 4:04 PM
  • Are you going to try publishing a new database to azure? There have been numerous changes to parts of azure services in the last month. There is a new Azure SDK update available in VS2013 from what I remember too.

    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Wednesday, May 14, 2014 5:50 PM
  • Dave,

    Yes, my next step is to try to make a copy of the solution, then remove the "....suo resides next to the solution file .sln and *proj.user files..." and then publish to a new Azure database.

    If the database publishes or not, I will learn something. :-)


    Thank you, Bill

    Wednesday, May 14, 2014 6:16 PM
  • Bill, we all will learn something. Trying various deployments using VS2013 Update 2 is on our backlog... :)


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Wednesday, May 14, 2014 6:24 PM
  • No positive results to report. This is what I've tried:

    1) Created a test project with a single table. Published successfully to a test Azure database & cloud service. The database schema published to azure sql.

    2) Cloned the git repository for the live project to a laptop. Attempted to publish to the test Azure database and cloud service. The code published to the cloud service but no tables were created int he test Azure database. The database schema did not publish.

    3) On my desktop, I copied the real project to a temp folder. Deleted the .user file. (I could not find any *.suo files in any folders of the solution) The database schema did not publish.

    4) For the heck of it, I tried unchecking the "publish database schema" checkbox in the wizard; execute publish; turn on checkbox and publish again. The database schema did not publish.

    My next step is to follow the "Declaring LightSwitch Publish Wizard Bankruptcy"

    Does anyone have any other thoughts?



    Thank you, Bill

    Thursday, May 15, 2014 11:51 AM
  • Can you try following the steps in this article to crank up the MSBuild verbosity, then try to publish.  Then look in the Output window for anything SQL or Database related:

    http://blogs.msdn.com/b/david_kidder/archive/2013/10/14/crank-up-your-visual-studio-build-output-to-11.aspx

    Also, there should be a build error stating that publishing the database failed, what is the full text of that message?



    David Kidder | Senior SDET | Microsoft | LightSwitch

    • Marked as answer by jessiedog Thursday, May 15, 2014 9:47 PM
    • Unmarked as answer by jessiedog Thursday, May 15, 2014 9:56 PM
    • Marked as answer by jessiedog Thursday, May 15, 2014 10:24 PM
    Thursday, May 15, 2014 5:32 PM
    Moderator
  • @David,

    Thank you for the link. Your link helped diagnose the issue.

    The solution I am using has been running live since Lightswitch 2010. Previously, it was running on a IIS server with a MS-SQL 2008 back end. The Lightswitch project ran on IIS from mid-2012 through mid-2013.

    In September 2013, I switched to Azure Cloud Service and Azure SQL.

    I have no idea why or how the publish is picking up the "Where should the package be created?" field which is causing the dacpac to be generated in the folder "D:\Temp\20130715\"

    I'm going to use your post on Publish Bankrupcy to remove the value in the "Where should the package be created?" and I suspect this challenge will be solved.

    @Dave and @Simon - thank you for your assistance and well.


    Thank you, Bill

    • Marked as answer by jessiedog Thursday, May 15, 2014 9:46 PM
    • Unmarked as answer by jessiedog Thursday, May 15, 2014 9:56 PM
    • Marked as answer by jessiedog Thursday, May 15, 2014 10:24 PM
    Thursday, May 15, 2014 9:46 PM
  • I thought the bankruptcy route outlined all fields for the publish but I am not able to locate "D:\Temp\20130715\" in the lsproj.user file or .lsproj file.  Would anyone know where to find this data so I may remove it?

    Thank you, Bill

    Thursday, May 15, 2014 9:56 PM
  • Have you tried just getting rid of all the publish settings and deleting the .user file yet?  I am thinking that one of the settings is still lurking in there from when you published to IIS and that is causing the dacpac pathing problem.  I think that it is still picking up this rogue setting when you try to publish to Azure. If it doesn't work after clearing out the publish-related values in the lsproj (or ls3proj or lsxproj, depending on what version of LightSwitch you are using) can you post the first part of your lsproj file?  Maybe I can find something.


    David Kidder | Senior SDET | Microsoft | LightSwitch

    • Marked as answer by jessiedog Thursday, May 15, 2014 10:24 PM
    Thursday, May 15, 2014 10:05 PM
    Moderator
  • I was editing lsproj instead of lsxproj. I've now removed lsproj and ls3proj.

    Issue resolved!!!

    Thank you for everyone's effort.


    Thank you, Bill

    Thursday, May 15, 2014 10:25 PM