SQLCMD variable substitution has been working in our project for years but after updating to VS 16.5 this stopped working.
Publish Profile (or schema compare) will not evaluate SQLCMD variables.
The generated script always includes any file that uses an SQLCMD variable and incorrectly updates the procedure on the target database. It uses the variable name (e.g. ($databasename)) instead of the substitution value (i.e. no substitution happens at all)
There is an option under Advanced... which says "Do not evaluate SQLCMD variables". Turning this off or on has no effect.
This defect was previously reported in the Visual Studio Community but it appears to have been closed prematurely with others saying it's not actually been fixed.
I have raised another case in the Visual Studio Community pages which is going through their triage process however, I wondered if anyone else here has experienced the same issue and found an alternative solution?
I'm seeing this issue in both Visual Studio 2019 and 2022.
I'm currently using
Visual Studio 2019 16.11.7
Microsoft SQL Server Data Tools 16.0.62111.11070
Visual Studio 2022 17.0.1
Microsoft SQL Server Data Tools 17.0.62110.20190
For anyone else coming across this we found that the replacements were working for a different project which targeted a different database on the same server.
This told us that it was probably project related. When compiling the solution we noticed we had the following warning
Warning: SQL71502: Procedure: [XXX].[YYY] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects:....
This did not fail the build however even though "Suppress Reference Warnings" was set to false in the database reference.
We removed the table reference containing the SQLCMD variable from one of the procedures causing us problems (e.g. [$(MyDbName)].[Schema].[Table]) and re-wrote it BY HAND. It's important you type this out and use intellisense to select your referenced database SQLCMD variable. Using copy and paste didn't solve the problem for us!
The resulting SQL is identical (Git thinks there's no changes and there's nothing to check in) but there's clearly some type of reference being maintained by visual studio (perhaps by something in the .vs folder??). What's even more strange is that this has affected all users in the same way even though we do not check in the .vs folder.
Anyway - HTH