在SQL Server中使用spWriteStringToFile时文件开头的BOM

I'm using SQL server to export data from a query in both HTML and CSV format. I also write out a batch file which calls CoreFTP to SFTP these files to a site. There is a nightly process that calls the batch file and SFTP these files. Everything has the potential to work except that the batch file has a BOM (byte order mark) at the start which cmd.exe chokes on. At least I think that is what it is.

我正在使用SQL Server以HTML和CSV格式从查询中导出数据。我还写了一个批处理文件,它调用CoreFTP将这些文件SFTP到一个站点。有一个夜间进程调用批处理文件和SFTP这些文件。一切都有可能工作,除了批处理文件在cmd.exe阻塞的开始处有一个BOM(字节顺序标记)。至少我认为就是这样。

When the batch file executes the error is '{unprintable character}C' is not recognized as an internal or external command. The unprintable character does not show in a text editor, but it is there. I think it has something to do with UTF encoding.

当批处理文件执行错误时,'{unprintable character} C'不会被识别为内部或外部命令。不可打印的字符不会在文本编辑器中显示,但它就在那里。我认为它与UTF编码有关。

Is there a way to change the encoding when using spWriteStringToFile?

有没有办法在使用spWriteStringToFile时更改编码?

Greg

DECLARE @CoreFileName VARCHAR(30)
SET @CoreFileName = CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 105)

DECLARE @FileName VARCHAR(30)

SET @FileName = 'ED_' + @FileName 

SET @FileName = CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 105) + '.html'
EXEC spWriteStringToFile @OutPut, 'D:\OutFolder\FTP\', @FileName 

SET @FileName = CONVERT(VARCHAR(10), CURRENT_TIMESTAMP, 105) + '.csv'
EXEC spWriteStringToFile @CSVOutPut, 'D:\OutFolder\FTP\', @FileName 

DECLARE @CoreFTP VARCHAR(200)
SET @CoreFTP = 'C:' + CHAR(13) + CHAR(10) + 'cd "C:\Program Files\CoreFTP\"' + CHAR(13) + CHAR(10) 

SET @CoreFTP = @CoreFTP + 'coreftp.exe -site OpenDoor -B -u D:\OutFolder\' + @CoreFileName + '.* /NCHIN/InpatientNotice/'
EXEC spWriteStringToFile @CoreFTP, 'D:\OutFolder\', 'SendFiles.Bat'

1 个解决方案

#1

Just found the answer. In the sp look for

刚刚找到答案。在sp寻找

@objTextStream OUT, @FileAndPath,2,true

and change it to

并将其更改为

@objTextStream OUT, @FileAndPath,2,false

Greg