SQLCMD variables and SSDT schema compare issue

Question

I want the comparison below to not show a difference. According to MSDN (see step 7), there should be a SQLCMD Variables function available which I cannot find. This documentation is for SQL 2010, but I am using 2012. There doesn't appear to be 2012 version of the documentation available on this page.

How can I specify the value of $(DatabaseName) so that this comparison will work? I already have the value specified in my database project settings.

Compensatory

I don't think you can do this between a project and a live database. Even that page seems to hint at that: "You can only specify SQLCMD variables when comparing two projects" What happens if you generate a script to update your database? – Discord

Also, if this is for a table that's local to the database, why are you specifying the database name? Just use "dbo.ClaimProvider" and you should be all set. I know that's different for cross-database queries, but this doesn't seem to be going across databases. – Discord

@PeterSchott I do a lot of cross-database joins, so it looks nicer to have all the names consistent instead of switching between 3 and 2 part names. – Compensatory

@PeterSchott I'll try generating a script. My team is new to SSDT, so I was trying to use the compare as a means to find changes that are made directly to the database and integrate them into the database project. – Compensatory

@PeterSchott When generating a script, it doesn't include the highlighted change above. I added a comment to force it, and then it included [$(DatabaseName)] in the script instead of the variable's value. I think I might just change it back to Claim and ignore the warning. – Compensatory

Sounds like it's working as designed, then. If you look at the Report for what it will change, you can get a quick overview. It's not picking up a change in this case because there is none. It would then get $(DatabaseName) from your variable settings. I'd either hard-code or completely remove it (to avoid warnings from it being in the same DB). – Discord

I'd also recommend setting up a DDL Audit on your server to catch things that are changing. I blogged about this a while back at: schottsql.blogspot.com/2010/02/… – Discord

Answer 1

You can work around this by schema comparing your development database, rather than the project, to the live database. Keep in mind that you would have to deploy the project to your development database first.

FYI - The fact that the variables are not substituted is only a problem in the comparison window. The variables do not cause false differences during the comparison (in other words, Schema Compare does substitute the variables when comparing) and the variables are substituted when you update the target database.

Aerification

I'm trying to capture what developers have modified in the dev database, and then update the project. If I deployed the project to dev, wouldn't I wipe out their changes? – Compensatory

It could be any server used for development purposes, perhaps even your local db instance or a shared server dedicated for schema comparisons. – Aerification

OR here's a thought... get MS to fix the bug in their Compare feature lol – Nassi

This indeed seems like a shortcoming of SSDT, but after trying this approach, I can confirm that it works. I have tended to use schema compare then update from there to deploy changes, rather than publish from the project. However, using publish to a dev server, then comparing two database instances on different servers, this works. I still would rather have the option to compare project schema to Db schema, and I think this is SUPPOSED to work, is it not? – Yet

One problem with this is reversing changes made from a server - back into the project. This scenario comes up from time to time, when someone makes changes directly via SSMS for example, Now, your project schema no longer reflects the target and since you cant really compare project to Db or Db to project, how do you diff these changes and pull them into your project? – Yet