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.

   Reply

   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.

      Reply

2. I had similar issue can you help me

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

   Reply

   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

      Reply

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

   Reply

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

   Reply

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