将传递给 xp_cmdshell 的命令参数转义给 dtexec [英] Escaping command parameters passed to xp_cmdshell to dtexec

问题描述

我正在使用存储过程和对 xp_cmdshell 的调用远程调用 SSIS 包:

I am calling an SSIS package remotely using a stored procedure and a call to xp_cmdshell:

declare @cmd varchar(5000)
set @cmd = '"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /Rep E /Sql Package /SET \Package.Variables[User::ImportFileName].Value;c:\foo.xlsx'
print @cmd
exec xp_cmdshell @cmd

这很好用，但是我不能保证变量值 (c:\foo.xslx) 不会包含空格，所以我想用下面的引号转义:

This works fine, however I can not guarantee the variable value (c:\foo.xslx) is not going to contain spaces so I would like to escape that with quotes like below:

set @cmd = '"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe" /Rep E /Sql Package /SET \Package.Variables[User::ImportFileName].Value;"c:\foo.xlsx"'

但是这样做我得到了错误

But by doing this I get the error

'C:\Program' is not recognized as an internal or external command, operable program or batch file.

如果在 cmd.exe 中执行，上述两个命令都可以正常工作，所以我猜测 SQL Server 正在解释我的双引号并更改某些内容，但我不知道是什么.

Both of the above commands work fine if executed within cmd.exe so I am guessing that SQL Server is interpreting my double quotes and changing something, but I can't figure out what.

推荐答案

在调查之后，您似乎必须使用带有 xp_cmdshell 的 DOS 8.3 符号，例如c:\progra~1... 并且您只能在参数上使用一组引号.

After looking into this, it appears you have to use the DOS 8.3 notation with xp_cmdshell e.g. c:\progra~1... and you can only have one set of quotes on the arguments.

要解决此限制，请使用较旧的 DOS 符号，或将代码放入批处理文件中，这样可以正常运行.

To get around this limitation, either use the older DOS notation, or put your code in a batch file instead which will run fine.

来源:http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/4e7024bb-9362-49ca-99d7-1b74f7178a65

这篇关于将传递给 xp_cmdshell 的命令参数转义给 dtexec的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！