How to use SQLCMD variables for a Linked Server Connection with Intellisense

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:

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

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:

DBReference
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.

Intellisense

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

SQLCMD Variables

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.

10 thoughts on “How to use SQLCMD variables for a Linked Server Connection with Intellisense

  1. Thanks for this useful post. After trying a long time to bypass the double square bracket issue with build event scripts and sqlcmd predeploy scripts i found that it suffice to replace square bracket by double quotes.
    In SQLCMD Variable config, replace LinkedServer].[RemoteDb by LinkedServer”.”RemoteDb
    In scripts replace [$(RemotDB)] by “$(RemoteDb)”.
    You then can benefit of Intellisense and automatic deployment.

  2. Thank you for your comment, Bulgom. It has saved me lots of time.

    To deploy using a dacpac script, I had to do this: LinkedServer\”.\”RemoteDb

  3. Great post, and thank you!

    I was wondering if you had found a solution to the problem with SQL Compare in later deployments of objects. In my project, I have the variables and values set up precisely as you have here. However, when I compare to my deployed database, all objects that refer to that SQLCMD variable show as being changed, because the comparison is comparing [$(ServerVariable)] to [ServerName].[DatabaseName]. I can’t seem to find a way to have it use those the variable values in the process of the comparison.

    1. Yes, I think I have the answer to that one.

      If you are performing a schema compare, the visual studio comparison tool uses the “default” SQLCMD variables as defined for the project.
      If you press Alt+Enter (or right click properties on the database project), Switch to the SQLCMD tab. What ever you find as the defaults on this screen is the value that the schema compare uses to look for differences.

      If you have a table/view/procedure that is showing up as different in the schema compare results, when you view the differences it will show the [$(ServerVariable)] as being different. If you have your SQLCMD varibles in alignment, this can be ignored as it is generally a false positive. Look in the script and you will find other things wrong. Fix the rest of the script and compare again and the script will now show as being equal.

      I have this same problem in that I have different linked server names for staging and development environments. When I switch between environments, I have to point schema compare to a different destination database and align the “default” SQLCMD variables with the linked server name that I am using for that server, ( have actually been doing this all week in testing some big changes for a data warehouse solution I have been working on 🙂

      Let me know if this helps.

  4. @Scott
    Just to clarify and make sure you got my point. It does work. You just need to ensure that the “Default” SQLCMD values, as configured on your project match the environment you are comparing against.

    When you do this the SQLCompare will work correctly, 🙂

    1. Not sure I really understand the question? Yes, you have the use name of the linked server you have configured on your server via SQL Server management Studio… otherwise the generated code will not work.

  5. It seems variables cannot be used in a Visual Studio Database Project, such as CREATE USER. VS complains it cannot find the user account.

Leave a Reply to Yogesh Shinde Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.