在 SQL Server Management Studio 中使用 setvar 转义空格字符
首先，我不是 DBA，也没有任何 SQL Server（管理）经验，所以在回答时请使用少音节的单词...
我正在运行 SQL Server 2008 R2 并尝试编写一份证明设置非分区、双向、事务复制的概念脚本。我已经设法从 MS 站点调整脚本以使其正常工作，现在我正在尝试根据我们的需求对其进行自定义。这涉及将一堆值提取到变量中。
我已将 SSMS 设置为“SQLCMD 模式”，并且所有变量都解析正常，除了一个变量。
:setvar ReplicationDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2\MSSQL\repldata';
这给了我错误：
发生致命脚本错误。 解析时遇到错误语法：setvar
我已将问题范围缩小到变量值中存在空格。根据我读过的内容，这应该没有问题。我尝试用双引号替换单引号（正常品种和有角度的品种），但都给了我相同的错误。
我在网上找不到任何东西告诉我应该如何转义空格字符。我见过的所有文章都说，如果字符串包含在引号中，SSMS 只会将它们之间的任何内容解释为单个字符串 - 无论空格如何。
有人有什么想法吗？
我想我可以将变量的值更改为：
:setvar ReplicationDirectory 'C:\Progra~1\Micros~1\MSSQL10_50.SQL2\MSSQL\repldata';
但我宁愿解决“为什么我不能使用空格？”问题代替。
非常感谢。
汤姆
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除了单引号字符串中的空格之外，尾随分号也会引起问题。
:setvar test1 'test test';空格和分号错误:setvar test1 'test test'空格错误:setvar test3 "test test" ;分号上的错误:setvar test4 "test test"使变量中的空格可用作test test:setvar test4 "'test test '"使单引号和空格在变量中可用，如'test test'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 astest test:setvar test4 "'test test'"makes the single quotes and the spaces usable in the variable as'test test'使用双引号，而不是单引号：
OR
SQLCMD 模式变量实际上是命令行变量，而不是 T-SQL 变量（以
@符号开头），您必须相应地设置它们。Use double, not single, quotes:
OR
SQLCMD mode variables are really command line variables, not T-SQL variables (which start with the
@symbol) and you have to set them accordingly.