I recently started using a SQL Database Project in Visual Studio 2015 to on a (pre-existing) complex database, and since the previous source control was pretty much nonexistent, it’s been a welcome change.
Overall, the upgrade process went reasonably well, with one exception that kind of screeched the whole thing to a halt. Since I’m sure I’ll run across the same issue again someday and by then I will have forgotten what the solution was, I’ll share it with you – and myself – in blog form.
The Schema Problem
In this particular setup, we were using the Schema Compare feature with the source being an existing (modified) version of the database, and the target being the DB project schema itself. There were quite a few changes to update and the list of changes/adds was pretty long. Working from top to bottom, we were applying changes to the DB project to synchronize with the updated version of the database. Everything went fine throughout all of the changes for the default schema, and by that point we were pretty confident in the process.
But then there was a problem. Any changes in the non-default schema would NOT apply to the DB project. To demonstrate the scenario, I recreated the problem with a dummy setup: one database with changes and a corresponding SQL Database Project.
The steps to synchronize changes to the DB project:
- Start a Schema Compare
- Select the existing database as target, with the project as Source
- Use the “Switch Source and Target” button to reverse the order
- Select the changed tables, views, or stored procedures in the Action column and verify the changes
- Click the “Update” button to apply the changes.
- Reload the Compare (I wish this happened automatically, but it doesn’t)
- Verify that the change is no longer in the list.
Except at step 7, the change was always still there. Why??
It was pretty obvious that the problem was the non-default schema, but trying every setting we could think of was getting us nowhere. Even making the schema default didn’t work. Searching all corners of every forum with every combination of possible search terms led to a lot of solutions that suggested re-installing Visual Studio, changing versions or service packs, or that it was just a bug in the SQL Database Project type, and that we were truly out of luck! Noooooooo!
Actually, the solution is a lot simpler than all that. Go to the bottom of the change list, and note if there are unsynchronized changes to the second schema itself:
Because it is at the end of the list, these changes will get applied last, but in order for the rest of the changes to save, the schema changes must be applied FIRST. Select only the schema and Update – even if the changes are minor, like what I have here, only an Authorizer change. Reload the compare and make sure NO schemas are in the change list.
Then, Update the rest of your changed objects. All the changes will apply correctly and you will be on your merry way.