Set SQLCmd variables based on configuration

Question

I'm trying to set SQLCmd variables based on configuration (Debug / Release, etc) but the Configuration dropdown is greyed out (see image below). For Dev environment, I want the SQLCmd variable to be Stage, for the Release environment, Prod.

I can't figure out an easy way to do this without going into the properties. We have 9 projects and about 6 variables each. Each time we do a schema comparison we have to manually change the variables and it's very tedious.

Our publish scripts are fine for the different environments, it's just setting up for schema compare that makes it time-consuming.

Shastashastra

Would Publish Profiles work? They wouldn't help you for schema compares, but they'd help with actually pushing or generating scripts/diff reports. – Predatory

I am trying to do the exact same thing - did you ever figure out how to do this? – Murderous

FYI, SSMS is pseudo Visual Studio. Meaning it utilizes a lot of the same UI but not necessarily all the options. As SSMS (older) did not need this functionality and capability, they probably disabled by nature. You can probably use the SQL Tools for Visual Studio to get some of the same functionality. Might want to consider why you are doing what you are doing and if Dynamic SQL Would be a better fit than SQLCMD – Flugelhorn

Answer 1

Most anything configured in a project file's properties gets stored in the project file itself. You'll notice if you add SQLCMD variables in the properties UI form, that the *.sqlproj file will have something similar to the following added:

...
<ItemGroup>
  <SqlCmdVariable Include="MyFavoriteVegetable">
    <DefaultValue>Zucchini</DefaultValue>
    <Value>$(SqlCmdVar__1)</Value>
  </SqlCmdVariable>
</ItemGroup>
...

You can view a project file's contents by double-clicking it in Visual Studio. Or, for projects targeting older frameworks, by right-clicking the project and selecting "Unload Project", then opening the file. Then, right-click and "Reload Project" after you're done editing.

As with anything in a project file, you can utilize MSBuild conditions to change a project's behavior, for example when changing the target configuration from Debug to Release.

<ItemGroup>
  <SqlCmdVariable Include="MyFavoriteVegetable">
    <DefaultValue Condition="'$(Configuration)' != 'Release'">Zucchini</DefaultValue>
    <DefaultValue Condition="'$(Configuration)' == 'Release'">Cauliflower</DefaultValue>
    <Value>$(SqlCmdVar__1)</Value>
  </SqlCmdVariable>
</ItemGroup>

Note that if you change the solution configuration while viewing the project properties UI, you will have to close and re-open that page to see the change.

Note that this behavior only affects performing actions from within Visual Studio, and won't have an impact on the output build artifacts for this project or its *.DACPAC. You will have to input the variable's value at publish time. For example, when using the SqlPackage command-line in a pipeline.

SqlPackage /Action:Publish /SourceFile:"C:\AdventureWorksLT.dacpac" `
    /TargetConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=AdventureWorksLT;Persist Security Info=False;User ID=sqladmin;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;" `
    /v:MyFavoriteVegetable="Carrots"

Finally, as a related reminder, be sure to enclose SQLCMD variables in brackets or quotes as needed, as I personally often forget.

EXECUTE [dbo].[CookDinnerStoredProc] @MainIngredient = '$(MyFavoriteVegetable)'

I tested the above project file changes using the following SDK, but I'm sure it works with many others using MSBuild syntax.

<Sdk Name="Microsoft.Build.Sql" Version="0.1.15-preview" />

Pastor