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.

6 thoughts on “Visual Studio SQLCompare does not Compare SQLCMD Variables correctly

  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.

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

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

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

Leave a Reply

Your email address will not be published.

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