Gotcha

When working with SQL CMD mode every now and then I will get this vague error and I can’t figure out what the problem is:

A fatal scripting error occurred.
Incorrect syntax was encountered while parsing :SETVAR.

Fix

This happens when you specify a value to a variable with spaces in it. It would be really helpful if the damn error message would just say that because it is incredibly misleading. I just keep forgetting that this is the problem, so I am writing it down this time.

Examples

Here are three examples of execution.

1. The first execution is an example of providing a value with no spaces in it.
2. The second execution will fail with the above error because the value has spaces in it.
3. The third execution is the fix for the second execution. To be clear the fix here is to wrap the value in soft quotes.

-- This will work just fine
:SETVAR someVariableName noSpacesInValue

SELECT '$(someVariableName)' AS TheValue

-- This will not work
:SETVAR someVariableName2 spaces in the value

SELECT '$(someVariableName2)' AS TheValue

-- This is how you fix it
:SETVAR someVariableName3 "spaces in the value"

SELECT '$(someVariableName3)' AS TheValue