Lately I’ve been using Visual Studio Team System 2008 Database Edition with the GDR R2 add-on, also known as “The Data Dude”, to compare databases with each other.
And during a schema compare I noticed that the dude can be quite stubborn now and then.
Here’s an example and an explanation of why, as far as I can tell, the dude is behaving that way.
At work we use the data dude’s schema compare to prepare our deployment scripts. The most fun can be had when preparing a script to deploy the latest finalized version, version x, from the Development (DEV) to the Acceptance (UAT) environment.
In our DEV database there have already been changes for the next version, version x+1, so these changes need to be skipped.
Now imagine the following situation:
- a table called TableA exists
- a new field called NewFieldA was added to TableA
- a view called ViewA exists and was modified – it uses TableA but not NewFieldA
The changes to ViewA belong with version x, while the changes to TableA are part of version x+1. So the script that I’m going to generate should contain an ALTER VIEW ViewA … but not an ALTER TABLE TableA ….
Using the dude, I open up the dropdown on the line that states Different definition on TableA and I select Skip. The value gets changed from Update to Skip. However, when the focus changes to another line in the grid the value changes back to Update! Furthermore, it’s now grayed out, impossible to change!
The first time that you see this happening you can’t believe your eyes and try it once more, first closing everything and then re-doing your actions. Just to see if you didn’t select the wrong option somewhere. But alas, the dude remains stubborn!
Tip: if you have just executed the compare and you find that some update actions cannot be modified, try to do a refresh (right-click on the background of the comparison window and click Refresh). I noticed that after the refresh you will get the dropdown for these lines and thus will be able to change the Update Action.
It took me a while to realize, but here’s what seems to be happening. Like with all things stubborn, it helps if you know what the thing in question is actually being stubborn for.
The dude knows that ViewA depends on TableA and it also knows that both ViewA and TableA where changed. However, the dude is a little short-sighted, it doesn’t know that NewFieldA is not used in ViewA. It doesn’t know that the change to ViewA was not related to the change on TableA. So it automatically assumes that you’re making a mistake and prevents you from messing up. It thinks, “You stupid user, I know it better, I am smart software! And that’s not all, I AM THE DATA DUDE! And if there’s one thing you don’t want, that’s to mess with the Data Dude!”.
Here’s a screenshot of the dude’s stubbornness:
In this particular case it refused to skip certain new tables, even though there were no dependent objects that needed those tables! The first two lines show the grayed-out items. These tables were located under a new schema and I had to put the schema’s Update Action to Skip to get the tables be skipped as well. The Update Action for the tables was changed automatically when I modified the action for the schema.
But, if you ask me, I don’t see why I shouldn’t be able to Skip a new table even when I am going to create the new schema to which it belongs? It’s the table that depends on the schema, not the other way around.
The Feature Request
It would have been great if I could manually override the dude’s decision. As that was not the case, I have to manually change the generated script so that it only contains the changes that I want. As long as it’s technically feasible, I think manual changes to the Update Action should be allowed.
One More Tip
Take a good look at the following screenshot.
By default, the node that reads Different dependencies was collapsed. So you think, good, the line says Skip so nothing will happen. Except, did you notice that minuscule exclamation mark in the bottom-left corner of the icon? That means that other actions may be happening further down the dependency tree, as illustrated in the screenshot.
So don’t let the dude mislead you into thinking nothing will happen when it says Skip. Sometimes the actual actions are hidden further down and you need to open up every node with the exclamation mark on the icon to verify if that’s what you want.
Have fun training the dude,