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.
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.
Here are three examples of execution.
- The first execution is an example of providing a value with no spaces in it.
- The second execution will fail with the above error because the value has spaces in it.
- 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