VS2010 Database Development Tools dependency resolution

Today, I am talking about how VS2010 Database Development Tools resolved a potential "Catch22" dependency resolution. To start with, here is the problem:

I have a database that has dependencies on another database. I will call then "Source" and "Main" database. "Main" database being the one that has dependencies on the "Source" database. Since I did not like to have explicit reference to "Source" database in my "Main' database, I created synonyms for all objects that needed to be referred into the source database.

So, I have synonyms that go like this:

CREATE SYNONYM [Main].[DimCustomer] FOR [$(AdvWorkLt)].[DW].[DimCustomer];

CREATE SYNONYM [Main].[DimProduct] FOR [$(AdvWorkLt)].[DW].[DimProduct];

…

 

The [$(AdvWorkLt)] is a SQLCMD variable in my database project that points to my Source database:

Next, I created views on top of these synonyms:

CREATE VIEW [dbo].[DimCustomer]

    AS SELECT * from [Main].[DimCustomer]

 

Since I did not like to do a "Select *" in my view, I used the refactoring capabilities of Database Development Tools to expand the wildcard:

The refactoring action gave me the following SQL:

CREATE VIEW [dbo].[DimCustomer]

    AS SELECT [Main].[DimCustomer].[IDCustomer],

     [Main].[DimCustomer].[CustomerID],

             [Main].[DimCustomer].[Title],

             [Main].[DimCustomer].[FirstName],

             [Main].[DimCustomer].[MiddleName],

             [Main].[DimCustomer].[LastName],

             [Main].[DimCustomer].[Suffix],

             [Main].[DimCustomer].[CompanyName],

             [Main].[DimCustomer].[ADWSalesPerson],

             [Main].[DimCustomer].[EmailAddress],

             [Main].[DimCustomer].[Phone],

             [Main].[DimCustomer].[CustomerEffDate],

             [Main].[DimCustomer].[CustomerEndDate],

             [Main].[DimCustomer].[CustomerCurrent],

             [Main].[DimCustomer].[LoadID]

FROM [Main].[DimCustomer]

 

Now, I questioned how the project would looks like when it would create a deployment script. To be honest, I had some doubts on how Database Development Tools would be able to render the dependencies between the synonyms and the views. I was in for a pleasant surprise because the dependencies has been resolved perfectly in the resulting deployment script:

PRINT N'Creating [Main].[DimCustomer]...';

 

 

GO

CREATE SYNONYM [Main].[DimCustomer] FOR [$(AdvWorkLt)].[DW].[DimCustomer];

 

 

GO

PRINT N'Creating [dbo].[DimCustomer]...';

 

 

GO

CREATE VIEW [dbo].[DimCustomer]

    AS SELECT [Main].[DimCustomer].[IDCustomer],

     [Main].[DimCustomer].[CustomerID],

             [Main].[DimCustomer].[Title],

             [Main].[DimCustomer].[FirstName],

             [Main].[DimCustomer].[MiddleName],

             [Main].[DimCustomer].[LastName],

             [Main].[DimCustomer].[Suffix],

             [Main].[DimCustomer].[CompanyName],

             [Main].[DimCustomer].[ADWSalesPerson],

             [Main].[DimCustomer].[EmailAddress],

             [Main].[DimCustomer].[Phone],

             [Main].[DimCustomer].[CustomerEffDate],

             [Main].[DimCustomer].[CustomerEndDate],

             [Main].[DimCustomer].[CustomerCurrent],

             [Main].[DimCustomer].[LoadID]

FROM [Main].[DimCustomer]

GO

 

In conclusion, VS2010 Database Deployment Tools is able to resolve object dependencies gracefully. I was a bit skeptical about it but now, I am convinced more than ever that this is the best tool to manage databases.

Comments

[Bruce]

Thanks for sharing this post. It's really an amazing post. Keep posting such good things.http://goo.gl/UYDaZ

[raffaeu]

Could you explain how you point the SQLCMD variable to another database project? If I try to use your solution when I build my project (3 databases) I receive the following error:
Warning SQL04151:
Synonym: [dbo].[syn_tbl_user] has an unresolved reference to object [$(userDb)].[dbo].[tbl_user]

[saUpe]

drug interaction cipro lipitor mg sotalol and metformin together allied bolt company wa fluid build up from levaquin delta dental allied flomax reaction pros and cons of working parents http://churmura.com/board/showthread.php?87705-duetact-for-diabetes-duetact-2-mg-pill-picture&p=106417 - duetact for diabetes duetact 2 mg pill picture http://smf.gwskinaid.com/index.php?topic=12633.new#new - remeron 60 milligrams consumers review remeron http://pwntheweb.com/index.php?topic=228860.new#new - telfast decongestion telfast 180mg spain http://tepekoy.tk/showthread.php?28-Hardal-otu&p=93&posted=1#post93 - pravachol and joint pain compare zocor pravachol http://ranndarab.net/vb/showthread.php?p=3074#post3074 - gaining weight on levlen levlen and weight gain american lithium production allied data service do not mix cialis with prednasone