xp_cmdshell调用含PUT命令的FTP脚本时查询挂起问题求助

问题背景 #

我编写了一个SQL存储过程，用于备份数据库、压缩文件后通过FTP传输至远程服务器，代码如下：

CREATE PROCEDURE BackupAndTransferViaFTP
    @ftpServer VARCHAR(255),
    @ftpUsername VARCHAR(255),
    @ftpPassword VARCHAR(255),
    @databaseName NVARCHAR(255)
AS
BEGIN
    BEGIN TRY

        -- 启用xp_cmdshell
        EXEC sp_configure 'show advanced options', 1;
        RECONFIGURE;
        EXEC sp_configure 'xp_cmdshell', 1;
        RECONFIGURE;

        DECLARE @localBackupPath NVARCHAR(255)
        SET @localBackupPath = 'D:\Thon\Backup\' + @databaseName + 'Backup.bak';

        DECLARE @zipBackupPath NVARCHAR(255)
        SET @zipBackupPath = 'D:\Thon\Backup\' + @databaseName + 'Backup.zip';

        -- 备份数据库
        DECLARE @backupCommand NVARCHAR(1000)
        SET @backupCommand = 'BACKUP DATABASE ' + QUOTENAME(@databaseName) + ' TO DISK = ''' + @localBackupPath + '''';
        EXEC sp_executesql @backupCommand;

        -- 压缩备份文件
        DECLARE @cmd NVARCHAR(1000);
        SET @cmd = 'powershell.exe Compress-Archive -Path "' + @localBackupPath + '" -DestinationPath "' + 'D:\Thon\Backup\' + @databaseName + 'Backup.zip"';
        EXEC xp_cmdshell @cmd, NO_OUTPUT;

        -- 删除原始备份文件
        DECLARE @delBackupfile NVARCHAR(1000);
        SET @delBackupfile = 'DEL ' + @localBackupPath ;
        EXEC xp_cmdshell @delBackupfile, NO_OUTPUT;

---FTP传输部分---------------------------------------------------------
        DECLARE @ftpScriptFilePath NVARCHAR(255)
        SET @ftpScriptFilePath = 'D:\Thon\Backup\' + @databaseName + 'Backup_FTP_Script.txt';

        -- 转义特殊字符
        select @ftpserver = replace(replace(replace(@ftpserver, '|', '^|'),'<','^<'),'>','^>')
        select @ftpUsername = replace(replace(replace(@ftpUsername, '|', '^|'),'<','^<'),'>','^>')
        select @ftpPassword = replace(replace(replace(@ftpPassword, '|', '^|'),'<','^<'),'>','^>')
        select @zipBackupPath = replace(replace(replace(@zipBackupPath, '|', '^|'),'<','^<'),'>','^>')

        -- 生成FTP脚本文件
        DECLARE @Command NVARCHAR(2000)
        SET @Command = 'ECHO open ' + @ftpserver + '>' + @ftpScriptFilePath + '&'
            + 'ECHO ' + @ftpUsername + '>>' + @ftpScriptFilePath + '&'
            + 'ECHO ' + @ftpPassword + '>>' + @ftpScriptFilePath + '&'
            + 'ECHO prompt ' + '>>' + @ftpScriptFilePath + '&'
            + 'ECHO binary ' + '>>' + @ftpScriptFilePath + '&'
            + 'ECHO put ' + @zipBackupPath + '>>' + @ftpScriptFilePath + '&'
            + 'ECHO quit' + '>>' + @ftpScriptFilePath;
        EXEC xp_cmdshell @Command, NO_OUTPUT;
 
        -- 执行FTP命令
        DECLARE @ftpCommand NVARCHAR(1000)
        SET @ftpCommand = 'ftp -s:"' + @ftpScriptFilePath + '"';
        EXEC master..xp_cmdshell @ftpCommand;

        -- 关闭xp_cmdshell
        EXEC sp_configure 'xp_cmdshell', 0;
        RECONFIGURE;
        EXEC sp_configure 'show advanced options', 0;
        RECONFIGURE;

    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
    END CATCH;
END;

生成的FTP脚本内容如下：

open "abc.com"
"user"
"password"
prompt
binary
put "D:\Thon\Backup\hBackup.zip"
quit

当前遇到的问题：

• 执行存储过程时，查询长时间挂起且无法取消，仅能通过任务管理器结束ftp.exe File Transfer Program进程终止
• 移除FTP脚本生成语句中的+ 'ECHO put ' + @zipBackupPath + '>>' + @ftpScriptFilePath + '&'行后，存储过程可正常运行
• 直接在命令提示符中执行对应的@ftpCommand命令，能正常完成传输

排查方向 #

1. xp_cmdshell输出缓冲区阻塞 #

执行FTP命令的xp_cmdshell未添加NO_OUTPUT参数，FTP传输产生的大量输出可能填满SQL Server缓冲区导致阻塞。修改执行FTP命令的语句：

EXEC master..xp_cmdshell @ftpCommand, NO_OUTPUT;

2. FTP脚本的引号解析错误 #

生成的FTP脚本中，服务器地址、用户名、密码都被添加了多余的"转义引号，FTP客户端解析时可能进入等待输入的异常状态。调整脚本生成逻辑，仅对put命令的路径添加引号：

SET @Command = 'ECHO open ' + @ftpserver + '>' + @ftpScriptFilePath + '&'
    + 'ECHO ' + @ftpUsername + '>>' + @ftpScriptFilePath + '&'
    + 'ECHO ' + @ftpPassword + '>>' + @ftpScriptFilePath + '&'
    + 'ECHO prompt ' + '>>' + @ftpScriptFilePath + '&'
    + 'ECHO binary ' + '>>' + @ftpScriptFilePath + '&'
    + 'ECHO put "' + @zipBackupPath + '">>' + @ftpScriptFilePath + '&'
    + 'ECHO quit' + '>>' + @ftpScriptFilePath;

生成的正确脚本格式应为：

open abc.com
user
password
prompt
binary
put "D:\Thon\Backup\hBackup.zip"
quit

3. SQL Server服务账号权限不足 #

手动执行FTP命令使用的是当前用户权限，而存储过程依托SQL Server服务账号运行，该账号可能没有读取备份压缩文件的权限，导致FTP传输卡住。检查：

• SQL Server服务运行账号对D:\Thon\Backup目录的读权限
• 确认压缩文件的所有者和权限设置

4. FTP主动模式的网络阻塞 #

默认FTP客户端使用主动模式，遇到防火墙或NAT时会建立连接失败，导致进程挂起。在FTP脚本中添加passive命令启用被动模式：

SET @Command = 'ECHO open ' + @ftpserver + '>' + @ftpScriptFilePath + '&'
    + 'ECHO ' + @ftpUsername + '>>' + @ftpScriptFilePath + '&'
    + 'ECHO ' + @ftpPassword + '>>' + @ftpScriptFilePath + '&'
    + 'ECHO prompt ' + '>>' + @ftpScriptFilePath + '&'
    + 'ECHO binary ' + '>>' + @ftpScriptFilePath + '&'
    + 'ECHO passive ' + '>>' + @ftpScriptFilePath + '&'
    + 'ECHO put "' + @zipBackupPath + '">>' + @ftpScriptFilePath + '&'
    + 'ECHO quit' + '>>' + @ftpScriptFilePath;

5. 路径转义字符导致文件识别失败 #

对@zipBackupPath做的特殊字符转义（如|转成^|），会让FTP客户端无法识别真实文件路径，导致找不到文件而进入等待状态。可去掉不必要的转义逻辑，仅对路径中的空格等必须转义的字符处理。

内容的提问来源于stack exchange，提问作者Thôn Mai Gia