VS2013 Database Project, how to link to other database projects in the solution

Question

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

I created an empty solution, and then added each database to the solution in the form of a project, by right-clicking on the database in SQL Object Explorer and creating a new database. Once all three projects have been created, a solution will be built. However, I have warnings about quotes and quotes (only for SPS) that links cannot be resolved to another database. In QuotingUi, I have not only warnings about sp links, but also errors (red squigglies) in all views.

I tried to add other projects to each project as projects (it seems he wants only a DLL), or a database, or both, and then rebuild, then close and reopen the solution, etc. I set up the build dependency for QuoutingUi for two other projects and the build order that builds them first. There is no joy.

He began treatment with Deborah Kurata, but I don’t think she covers this scenario.

Suggestions are welcome.

Answer 1

It looks like you are still using the existing three-part name (for example, [SameServerDb]. [Dbo]. [Table2]) in your stored procedures and views. You must update it to use the SQLCMD variable name for reference instead. SQLCMD variables are used, so you can change the name of the referenced database during deployment.

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

Note that the database variable name is $ (SameServerDb). Now I just change any link 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 of your databases to which they refer: In the Solution Explorer window, right-click the link → Add Database Link and select another project. Repeat 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