What are the challenges you face when working across database platforms? Take the survey
Options

Where is the table data in XML format kept?

MondayMonday Posts: 77 Silver 3
edited January 28, 2019 11:52PM in SQL Change Automation
I seem to have run into an issue where old static data must be cached some place. I have a table where I just selected "Include table data" it wants to make a script to insert 9 rows (without the if count(*) = 0) and update 1. The script it generates will be impossible to run. I have to assume that at some point I set this table to include table data when it had 1 row then set I to not include table data. When I view the compare I can see it has 1 row of changed data. How do I get rid of the old static data for this table? It seems that when you un-select "Include table data" this data should be removed if it exists so this problem does not happen.
Tagged:

Answers

  • Options
    Table data is kept in the Data subfolder and metadata about which data is linked is stored in RedGateDatabaseInfo.xml


    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Options
    MondayMonday Posts: 77 Silver 3
    Where are these folders kept? I don't have them in my project.

  • Options
    Sorry, I assumed this is was a SQL Source Control project.

    In SQL Change Automation Projects this will be on the .sqlproj file in the /IncludeObjectsInDataSync tag:


    You don't need to remove this manually: Un-selecting "Include table data" will remove the table from this, however, you also need to delete or edit the migration script with the static data
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Options
    MondayMonday Posts: 77 Silver 3
    The  .sqlproj just references which tables should have static data. Unselecting it just removes the reference but it seems the data is still left behind. Where does  SQL Change Automation store that static data with this type of project? In this screen shot I just set the table RequestTypesSLA to include data. When I do a compare it has a row of data in it. Where is that one row of data coming from? Since I just told it to include static data there should not be an update in there. The script it generates should be if count(*) = 0 insert all rows like every other script it generates when I first select include table data.
  • Options
    MondayMonday Posts: 77 Silver 3
    Well I figured it out. The data was in the shadow database. Deleting the row in the shadow database fixed the issue. Un-selecting "Include table data"  should also truncate the data in the table for the shadow database. Is this a bug or by design?

    Thanks
  • Options
    Normally any data in the Shadow database will need to be in a migration script, so something unusual must have happened there
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
Sign In or Register to comment.