Search code examples
sqldatabase-projectsql-server-data-toolssqlpackage

Why is my database project upgrade script including refactoring from referenced database projects?

Hopefully this will make some sense ..

We have a bunch of database projects that we use to maintain schema and generate upgrade scripts. To generate the upgrade scripts, we use an automated build engine which uses SqlPackage.exe to compare the database project with a "schema template", which is a copy of the live database, but with no data.

One of these databases has references to three of the other database projects.

Recently, we've found that when the upgrade script is generated for this database project, it includes refactoring SQL for tables that are in one of the references project, and not in the project in question.

So the upgrade script includes two lots of SQL:

  1. Print statements at the start saying that a schema object will not be renamed.
  2. A bunch of insert statements into __RefactorLog.

The problem is twofold:

  1. These refactors should not be included in this upgrade script; they belong to one of the referenced database project - they have nothing to do with this one.
  2. Because this database does not require refactoring, it has no table called __RefactorLog because they upgrade script doesn't need to create it. This causes a build failure.

I've looked through the database project settings and can't see any reason why the refactoring from a referenced database project would be included in referring project.

Any ideas?

UPDATE - The missing __RefactorLog table was a red herring. For some reason it was included in the schema template but was not in the live database, so SqlPackage assumed that it did not need to create it. However, I'd still love to know why all of the refactoring from a referenced database project is included in the problematic one.

Solution

  • This is a bug in SSDT - there's a Connect bug tracking this. Note that for "Same Database" references the refactorlog is expected to be included. This is because all the objects will be pushed to one database. The bug is that the refactorlog for "Different Database" references is incorrectly included, which I'm guessing is what you're running into.