The Problem

For a long time I have struggled with how to correctly setup a SQL Server Database project. The main sticking point is to try to script views that use linked server connections.

Generally when you create a view that references a linked server connection you will get the following error:

[sql]
SQL71561: View: [dbo].[vwTest] has an unresolved reference to object [LinkedServer].[RemoteDB].[dbo].[tblTest].
[/sql]

The End Game

I have been able to achieve the holy grail in Visual Studio database projects;

1. A solution that compiles even with views that run across linked server connections.
2. The solution has Intellisense working for the linked server database.
3. The ability to adjust the details of the linked servers when publishing the database via the SQLCMD Variables.

The Solution

The following instructions are performed on Visual Studio 2012 / SQL Server 2012 and SQL Server Data Tools (SSDT)

Step 1 – Create a database project for your linked server, (RemoteDB)

You need a .dacpac file of your target database.
The easiest way to do this is:

1. Create a new SQL Server Project
2. Right Click on the database project and choose Import –> Database
3. Create a connection to your remote/linkedserver target database.
4. Click finish and visual studio will create a scripted copy of your database.
5. Build the project and fix any compile issues

In my case I was trying to reference an Enterprise level database with a few thousand tables, but rather than generate the entire database I just created the 20 – 30 tables that are referenced most often and used that as my DB project. Over time as I need more and more tables I will just script and import a few tables at a time, via the Import –> Script option.

Once successfully built the .dacpac file will be found in the “bin\Debug” directory

Note: I also generally write a post build event and copy the .dacpac file to a central higher level folder to allow any project to reference the .dacpac file from a central location.

Step 2 – Add a database reference

In the main database project add a database reference as follows:

Some points about these settings:

1. Choose your .dacpac file that you generated in step 1.
2. Database Location – even though the Database is on a “Different Database, Different server”, you need to set it as “Different Database, same server”. This setting allows intellisense to work.
3. Suppress errors – this is just something in case you cause errors in you .dacpac

3. Create a view

Create a view and you will be able to do:

select
*
from [$(RemoteDB)].dbo.tblTest

You should get full intellisense on your schema as compiled in you .dacpac file from step 1.

4. Setup your SQLCMD Value

Our replacement variable value is the two-part name for the LinkedServer].[RemoteDB
NOTE: Leave the leading and trailing values off as the outer braces will be added from your sql script as you have to enter [$(RemotedDB)]
This way when the project is generated your solution will generate the correct 4 part name to your linked server objects

5. Publish

Publish the solution:

1. Choose Publish
2. Click “Load Variables” to load in the values set in step 4.
3. Choose the database to deploy to
4. Click “Create Profile”, to save your publishing profile
5. Click “Generate Script”
6. Review the Generated Script and you will note that for some reason the single ] has been changed to ]], remove the extra ]
7. Right-Click on the window and choose “Excecute” to run the script

Because of the double brace ]] issue, (which I have raise with Microsoft on Microsoft Connect ), you would be able to just click Publish.

The value in the SQLCMD Variable will be replaced into the script when deployed, and your table references will become 4-Part table names with [LinkedServer].[RemoteDB].[Schema].[Table] as the output.

Please Download a working sample of the solution.

Download

Let me know if this works for you.