Thursday, February 12, 2009

Substituting view/synonym causes naming collisions in Database Edition GDR

The other day we discovered a duplicated table in the database. We're doing some refactoring, and a certain amount of duplication during this process is inevitable, but to cut a long story short, the two instances of this table needed collapsing into one.

So I dropped one instance, and created a synonym in it's place (pointing to the other). This saves fixing all the views and external references (in the SSAS cube) to that table. Those references will go away over time.

But when I tried to sync the database using GDR it just didn't get it. It's quite happy to create the new synonym, just too dumb to drop the original table first - so there's a naming collision I had to fix by hand. It basically does this:

CREATE SYNONYM [dbo].[Table_2] FOR [dbo].[Table_1];
GO
DROP TABLE [dbo].[Table_2];

Dur! And it does the same thing if you substitute a view for a table too:



CREATE VIEW [dbo].[Table_2]
AS
-- Create view instead of original table
SELECT One FROM dbo.Table_1;

GO
-- Drop original table
DROP TABLE [dbo].[Table_2];
Anyone see the problem here? This is not an edge case guys.

I should point out that this only happens when sync'ing a database with the database model. If you sync two live databases, GDR gets the execution order right.

Not impressed.

(By way of comparison Sql Compare 7 is quite happy with both these scenarios, whether comparing against a live database or a snapshot. Drop table; create synonym - it's not that hard. And Sql Compare 7's been out a while (8 is nearly out))


[Update: 2008-02-20] And another thing: GDR's idea of dropping an object (eg: if you sync to a dev database and the object isn't there any more) is to comment out the object definition in the database project. All well and good, but it's too stupid to also comment out any extended properties the object might have (say the ones the view designer put in to store the view layout), so obviously those extended properties cause the build to crap out as the object they reference doesn't exist.

3 comments:

Matt Ryan said...

Much as I'm a fan of SQL Compare, they did have a similar bug about a year ago (haven't re-checked recently), where the comparison results missed some triggers etc if comparing database script folders (but was fine for snapshots or live databases)

Struggle to understand why these types of 'source' dependent bugs can exist in either case; seems like such a simple config change to handle for running a few permutations of the same test suite...

Especially since our workaround was to just convert any script folders to snapshots before running the comparison.

piers7 said...

I'll have to confess I've never used the sync-to-scripts option in Sql Compare. Especially with their snapshot-in-source-control comparison tool (Scream) I've always found it easiest to just work with the snapshots themselves.

But yeah, that does seem pretty silly

ceiling fans said...
This comment has been removed by a blog administrator.

Popular Posts