SSDT : External Database Reference Error

Today I face a challenge with SSDT (SQL Server Data Tools). I encounter some errors when I have multiple database references in my SQL Server Database Project in Visual Studio 2013.

The errors that I am facing now:

SQL71561: View: [dbo].[View_1] has an unresolved reference to object [DatabaseB].[dbo].[Table_1].
SQL71501: View: [dbo].[View_1] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [DatabaseB].[dbo].[Table_1].[Column1] or [dbo].[Table_1].[t2]::[Column1].

After googling around, the suggested cause of the errors is I am using 3 part name for the same database and then using the table which comes from different database in my query without having database reference in my project. The actual root cause is the database project itself perform database object validation at the background, and it cannot find the other database.

SELECT t1.*

FROM Database1.dbo.Table_1 AS t1

LEFT JOIN Database2.dbo.Table_1 AS t2

ON t1.Column1 = t2.Column1

Therefore, I have added other database projects that I need into my solution like the following screenshot.

Then, add the required database reference. Note that I do not need database variable, so I left the field empty. The example usage is correctly showing how I should and would use the database reference.

After that, I rebuild my project but I am still getting the same error. I have no choice but to remove the 3 part name for current database and remain the 2 part name as highlighted red in above query.

Finally, no more error has occur and my project is able to be built. Happy! But, later discover that there are more challenges await me.

See my solutions explorer screenshot above, I have more than two databases. I have a lot more complicated queries that need to deal with multiple databases. For example:

In Database1:

SELECT t1.*

FROM dbo.Table_1 AS t1

LEFT JOIN Database2.dbo.Table_1 AS t2

ON t1.Column1 = t2.Column1

In Database2:

SELECT bla bla bla

FROM dbo.Table_1

UNION ALL
SELECT bla bla bla
Database1.dbo.Table_2

So, as you can see Database1 need to add Database2 as database reference and then Database2 need to add Database1 as database reference too. If you go and do that in Visual Studio, you will get the following error:

A reference to library 'Database' cannot be added. 

Adding this project as a reference would cause a circular dependency.

It seem like Visual Studio treat the database reference as assembly reference. Having both database projects referred to each other is considered as circular reference. So, how am I suppose to do now?

I found a workaround but not everyone may accept it. I realize that by adding Data-tier Application Package (*.dacpac) as database reference, Visual Studio will not complain about circular dependency. Therefore, I try to extract dacpac for all the related databases by using SQL Server Management Studio (SSMS).

Just click Next button all the way until the Finish. By default, the Data-tier Application is extracted and stored at C:\Users\[UserName]\Documents\SQL Server Management Studio\DAC Packages\[DBName].dacpac. I would recommend to extract the DAC package files to a centralized location, so that it is easier to retrieve, track and manage the packages later.

While extracting the DAC package file, you may encounter the same error SQL71561 and SQL71501 again.

You have to extract the package manually by using SQLPackage.exe.

The location of the SQLPackage.exe is C:\Program Files (x86)\Microsoft SQL Server\[version]\DAC\bin

Below is the command line that I use to extract dacpac:

sqlpackage.exe /Action:Extract /ssn:. /sdn:Database1 /tf:"E:\DAC Packages\Database1.dacpac"

/ssn = Source server name
/sdn = Database name
/tf = Target file

More parameters info can be found HERE.

After you have all the DAC packages ready in one centralized location, now back to Visual Studio to add them as database reference.

My current setup is:
Database1 has Database2 as reference.
Database2 has Database1 as reference.

No more circular dependency complaint and no more database reference not found error. Also, another advantage of using DAC package as database reference is you need not to create or add other database project which is not developed by you to be included into the solution.