Where is the table data in XML format kept?

Monday

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.

Sergio R

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

Monday

Where are these folders kept? I don't have them in my project.

Sergio R

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

Monday

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.

Monday

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

Sergio R

Normally any data in the Shadow database will need to be in a migration script, so something unusual must have happened there