How to make SSDT Profile.xml SqlCmdVariable an empty string or optional

Question

I am using SSDT (and sqlproj) for our MSSQL projects. We have a few variables we need to set when publishing to each environment.

This works great on most of our environments where we assign values to all of the variables, but when we publish to our live database, I would like to be able to make the DomainPrefix a blank string.

When I try to alter the Live.profile.xml to set DomainPrefix to no value I get the error: "An error occurred during deployment plan generation. Deployment cannot continue. Missing values for the following SqlCmd variables:DomainPrefix."

This what I would like the Live.profile.xml to look like:

<?xml version="1.0" encoding="UTF-8"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" ToolsVersion="12.0">
  <PropertyGroup>
    <TargetDatabaseName>DB_NAME</TargetDatabaseName>
    <DeployScriptFileName>DB_NAME.sql</DeployScriptFileName>
    <TargetConnectionString>CONNECTION_STRING</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
  <ItemGroup>
    <SqlCmdVariable Include="DomainPrefix">
      <Value></Value>
    </SqlCmdVariable>
    <SqlCmdVariable Include="Environment">
      <Value>live</Value>
    </SqlCmdVariable>
  </ItemGroup>
</Project>

Does anyone know how to set a SqlCmdVariable to a blank value or make it an optional variable?

Using:

• VS 2013 sqlproj
• SqlPackage.exe to run the publish to the DB from command line

Ribbon

How are you using DomainPrefix? Is that part of Pre/Post scripts, used within objects/code, names of servers, something else? If you're only using that for Pre/Post scripts, you might be able to manipulate those scripts accordingly with a section for Production that's slightly different. – Popedom

DomainPrefix specifically is being used in a post script, and we do have some code to handle it - but its also one of several variables that we want to set as a blank string, so would be good to know if there's a way to make it blank/optional. – Ribbon

I'm not aware of any way to do that which is why I wanted to make sure you were only using it in a Post script. You can check for some value (e.g. "Prod") and make it blank if that's the case and/or make a section for Production. I think making MS never consider a blank variable because that could cause havoc if used within the code, especially for DB names. – Popedom

I dont think there is an easy option unless you start hacking at it, read more here: blogs.msdn.com/b/sql_server_appendix_z/archive/2013/02/23/… – Pals

@PeterSchott and Saan, I have posted an answer which should solve the issue, and for 2 different interpretations of how this variable will be used. – Distressful

@EugeneNiemand That is definitely thinking along the right lines. However, and quite unfortunately, most of the SQLCMD commands are disallowed outside of running the SQL script via SQLCMD.EXE. Only a small subset is available for SQLCMD-mode in SSMS / Visual Studio (with :out and shelling out via !! not making the cut), and I do not believe all of those are available via SSDT publishing. I posted an answer below with the best I could come up with given the restrictions. – Distressful

Answer 1

There does not appear to be any way of passing through a NullOrWhiteSpace string as a SqlCmdVariable. The basic syntax for SqlCmdVariable is:

<SqlCmdVariable Include="DomainPrefix">
  <DefaultValue>
  </DefaultValue>
  <Value></Value>
</SqlCmdVariable>

Either Value or DefaultValue needs to have some non-white space value.

So, for your first option, as @Peter suggested in a comment on the question, you can handle this in your Post Deployment SQL script by testing for a specific value, such as <Live> or whatever. Do something like the following:

DECLARE @DomainPrefix NVARCHAR(50) = N'$(DomainPrefix)';

IF (@DomainPrefix = N'<Live>')
BEGIN
  SET @DomainPrefix = '';
END;

And then just concatenate @DomainPrefix to the strings instead of including $(DomainPrefix) in them.

Of course, if you need the SQLCMD variable to be available in the main T-SQL context and not just as a T-SQL variable (for example, if you are using this as a Linked Server or Database prefix along the lines of UPDATE $(DomainPrefix)[DatabaseName].[dbo].[TableName]... where $(DomainPrefix) is usually defined as [LinkedServerName]. ), then you should be able to get away with using a Value of /**/ (or even /* Live */ ) so that the resulting T-SQL would interpreted as either:

UPDATE [LinkedServerName].[DatabaseName].[dbo].[TableName]...

or:

UPDATE /* Live */[DatabaseName].[dbo].[TableName]...

both of which are valid T-SQL and work. So in this case you would use the following:

<SqlCmdVariable Include="DomainPrefix">
  <Value>/* Live */</Value>
</SqlCmdVariable>

Distressful

Clever and I can see how that could get around your issue. That's one reason I was trying to figure out where you were using the variable and for what purpose. :) – Popedom

@PeterSchott Thanks :-) . However, I am not the O.P. ;-) – Distressful