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

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.

VN:F [1.9.22_1171]
Rating: 9.5/10 (2 votes cast)
How to use SQLCMD variables for a Linked Server Connection with Intellisense, 9.5 out of 10 based on 2 ratings

Categories: SQL Server Tags:
  1. Bulgom
    February 21st, 2014 at 21:23 | #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.

    VA:F [1.9.22_1171]
    Rating: +2 (from 2 votes)
  2. Gail
    March 7th, 2014 at 04:55 | #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

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  3. Scott
    June 27th, 2014 at 01:34 | #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.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • jcrawfor74
      June 27th, 2014 at 02:20 | #4

      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.

      VN:F [1.9.22_1171]
      Rating: +2 (from 2 votes)
  4. Scott
    June 27th, 2014 at 22:02 | #5

    @jcrawfor74
    I pretty much am in the same position as you are. Thank you very much for your reply!

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  5. jcrawfor74
    June 30th, 2014 at 13:16 | #6

    @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, 🙂

    VN:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  6. Yogesh Shinde
    July 14th, 2014 at 18:18 | #7

    Hi, Can we use the same LinkServer which we use SSMS?

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • jcrawfor74
      July 22nd, 2014 at 21:20 | #8

      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.

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  7. Dan
    December 16th, 2014 at 09:42 | #9

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

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  8. Aswin
    April 11th, 2017 at 19:01 | #10

    Is it possible to have a more detailed version of Step 1?

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  1. No trackbacks yet.