VS2013 Database Project how to reference other database projects in solution

Question

Working with VS2013 and doing my first database project solution. I have three existing databases on the same server. Quotes, Quoting and QuotingUi. Each have a few stored procedures that reference one of the other databases. This is done with database.schema.table (Quotes.dbo.Mytable) syntax and works well in SSMS and all production situations. QuotingUi has a number of views that pull data from Quoting using the same syntax. SPs and EF models have no problem with this.

I have created an empty solution and then added each database to the solution as a project by right clicking on the database in the SQL Object Explorer and Create New Database. Once all three projects are created the solution builds. I do however have warnings on Quotes and Quoting (sps only) that references cannot be resolved to the other database. In QuotingUi I have not only warnings about the sp references but errors (red squigglies) on all the views.

I have tried adding the other projects to each project both as projects (seems it only wants dlls) or databases or both, then rebuild, then close and reopen solution etc. I have set a build dependency for QuoutingUi for the other two projects and a build order that builds them first. No joy.

Have begun Deborah Kurata Pluralsight Course but I do not believe she covers this scenario.

Suggestions welcome.

Answer 1

It sounds like you're still using the existing three part name (e.g. [SameServerDb].[dbo].[Table2]) in your stored procedures and views. You should update it to use the SQLCMD variable name for the reference instead. SQLCMD variables are used so that you could change the referenced database name at deployment time.

This is covered in the help documentation, but here's an example for you. In this case I added a reference to "SameServerDb" as shown below:

Note the "Database Variable" name is $(SameServerDb). Now I just change any reference from [SameServerDb] to [$(SameServerDb)]:

CREATE VIEW [dbo].[View2]
AS SELECT * FROM [$(SameServerDb)].[dbo].[Table2]

Answer 2

For all database projects in your solution, add all their referenced databases: In Solution Explorer window, right click References -> Add database reference, and select the other project. Repeat for all referenced projects. Also, make sure you rebuild all projects.

You can also suppress certain warnings: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9b698de1-9f6d-4e51-8c73-93c57355e768/treat-specific-warning-as-error?forum=ssdt