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.

3 thoughts on “VS2010 Database Development Tools dependency resolution

Add yours

  1. 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]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: