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