Home > SQL Server, SSDT, Visual Studio > Visual Studio SQLCompare does not Compare SQLCMD Variables correctly

Visual Studio SQLCompare does not Compare SQLCMD Variables correctly

The problem

When using the Visual Studio SQL Compare to compare your database schema to your physical database it returns heaps of false positives, where SQLCompare is suggesting that your CMD variable does not match what is on the database.

The Solution

SQLCompare uses the default or local CMD variables that you have on your project when performing the compare.

You need to ensure the default variables match the target database you are comparing against.

To change your defaults open the properties of your Database project, switch to the “SQLCMD Variables” tab and edit the defaults.

When you get your variables in alignment with your target comparison database you will get a true indication of the differences.

VN:F [1.9.22_1171]
Rating: 7.3/10 (3 votes cast)
Visual Studio SQLCompare does not Compare SQLCMD Variables correctly, 7.3 out of 10 based on 3 ratings

Categories: SQL Server, SSDT, Visual Studio Tags:
  1. Oleg Boldyrev
    September 30th, 2014 at 17:09 | #1

    Somehow that does not work for me.
    I have two database projects in a solution (VS 2012), one referencing another. The SQLCMD variable AnotherProject, denoting the referenced database, was automatically added, with the default value set to the name of the referenced project. I substituted it with a physical database name [ServerName].[DatabaseName], moving the old default to Local, built the solution, ran Schema Compare.
    In the Synonims, where I had substituted the database references with [$(AnotherProject)].[dbo].[…, the variable is not resolved to the default value during the comparison.

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • jcrawfor74
      October 1st, 2014 at 23:33 | #2

      A few points.

      • As in the article you need to ensure the defaults for the project (Alt+Enter) have the correct values, as the schema compare will substitute these values and they must match the target DB being compared
      • Did you reference your database as different database, same server? Have a read of this post as it may provide some guidance on how to reference another database and get intellisense working. .
      • I have memories also of issues with synonyms not working with intellisense, in visual studio, so maybe the do not compare also
      • Sometimes the comparison will be finding something else as wrong in the script. It will still highlight the SQLCMD variable, but it may be something else that is causing the error. Once the other issue is fixed the comparison will not find any differences. It can be elements like differences in white space and other attributes that cause the discrepancy, expand all the + buttons on the comparison and sometimes you will find differences in “Quoted identifiers” that are actually causing a comparison difference.
      • I have had similar issues with calculated columns generating differently when applied to the database with extra brackets and braces, and then when a compare is done they are picked up as being different, even though they are generated off the same model, I raised it with Microsoft but they have done nothing to resolve

      Hope that helps.

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  2. Dorababu
    May 3rd, 2017 at 17:27 | #3

    I had similar issue can you help me

    https://www.experts-exchange.com/questions/29019865/Powershell-compare-DACPAC.html

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • jcrawfor74
      May 3rd, 2017 at 20:11 | #4

      I think the problem is your code in the left hand example of the compare is wrong. You have the variable $(testDB) NOT $(Database_Name)

      So the procedure should be;

      select 1 from [$(testDB)].dbo.tblTest

      If you do this correctly the procedure will not be found to be different, and it will NOT show up in the schema compare results.

      If you do have a record with a difference lets say:

      select 1 from [$(testDB)].dbo.tblTest with(nolock)

      The compare will find a difference and it will highlight both the “with(nolock)” and the [$(testDB)]. You can ignore the [$(testDB)] as it is just the simplistic compare utility not being smart enought to replace it with your variable.

      Hope this helps

      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)
  3. Dorababu
    May 3rd, 2017 at 20:18 | #5

    Hi the variable I am passing is correct but still I couldn’t compare them properly

    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
  4. Dorababu
    May 3rd, 2017 at 20:32 | #6

    Here I am comparing with the one that got extracted after database got published so here is the diff it is showing

    CREATE PROCEDURE [dbo].[Procedure1]
    AS
    SELECT * from [$(Database_Name)].dbo.tbltest
    RETURN 0
    GO

    CREATE PROCEDURE [dbo].[Procedure1]
    AS
    SELECT * from [TaxDB].dbo.tbltest
    RETURN 0
    GO

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