Escaping Space Character using setvar in SQL Server Management Studio

Question

Firstly, I'm not a DBA and I don't have any (admin) experience with SQL Server, so please use words with few syllables when answering...

I'm running SQL Server 2008 R2 and trying to write a Proof Of Concept script which sets up Nonpartitioned, Bidirectional, Transactional Replication. I've managed to tweak the script from the MS site to work, now I'm trying to customise it to our needs. This involves pulling out a bunch of the values into variables.

I've set SSMS to be in "SQLCMD Mode" and all of my variables parse okay, with the exception of one.

:setvar ReplicationDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\repldata';

This gives me the error:

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

I have narrowed my problem down to the presence of spaces in the variable value. According to what I've been reading this should work without a problem. I've tried replacing the single quotes with doubles (both the normal variety and the angled kind) but all give me the same error.

I can't find anything on the web which tells me how I should escape the space character. All the articles I've seen say that if the string is in quote marks, SSMS just interprets whatever is between a pair of them as a single string - regardless of whitespace.

Does anyone have any ideas?

I guess I could change the value of the variable to be:

:setvar ReplicationDirectory 'C:\Progra~1\Micros~1\MSSQL10_50.SQL2\MSSQL\repldata';

But I'd rather solve the "why can't I use spaces?" problem instead.

Many thanks.

Tom

Answer 1

Use double, not single, quotes:

:setvar ReplicationDirectory "C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\repldata"

select '$(ReplicationDirectory)' as test

OR

:setvar ReplicationDirectory "'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\repldata'"

select $(ReplicationDirectory) as test

SQLCMD mode variables are really command line variables, not T-SQL variables (which start with the @ symbol) and you have to set them accordingly.

Answer 2

The trailing semicolon is causing issues in addition to the spaces in the single quoted string.

:setvar test1 'test test'; errors on the space and the semicolon

:setvar test1 'test test' errors on the space

:setvar test3 "test test"; errors on the semicolon

:setvar test4 "test test" makes the spaces usable in the variable as test test

:setvar test4 "'test test'" makes the single quotes and the spaces usable in the variable as 'test test'