SQL 如何使用带有文本“|”的sql脚本创建文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14275493/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to create text file using sql script with text "|"
提问by Yogesh
if i run below script without char "|" it working but when i am adding char "|" it is not working how to add char "|" using sql script to text file ?
如果我在没有字符“|”的情况下运行脚本 它工作但当我添加字符“|” 如何添加字符“|”不起作用 使用 sql 脚本到文本文件？
DECLARE @Text AS VARCHAR(100)
DECLARE @Cmd AS VARCHAR(100)
SET @Text = 'Hello world| '
SET @Cmd ='echo ' + @Text + ' > C:\AppTextFile.txt'
EXECUTE Master.dbo.xp_CmdShell @Cmd
thanks
谢谢
回答by Pondlife
The pipe character has a special meaning in batch commands, so it must be escapedusing the caret character. This should work:
管道字符在批处理命令中具有特殊含义，因此必须使用插入字符对其进行转义。这应该有效：
DECLARE @Text AS VARCHAR(100)
DECLARE @Cmd AS VARCHAR(100)
SET @Text = 'Hello world^| '
SET @Cmd ='echo ' + @Text + ' > C:\AppTextFile.txt'
EXECUTE Master.dbo.xp_CmdShell @Cmd
Although this is really not a good way to write data to a text file: usually SQL Server should not have permission to write to the root of the C: drive, and xp_cmdshellis disabled by default. I suggest you look at alternatives like sqlcmd.exe, bcp.exeor a small script in your preferred language (PowerShell, Perl, Python, whatever).
虽然这确实不是一个将数据写入文本文件的好方法：通常 SQL Server 应该没有写入 C: 驱动器根目录的权限，并且xp_cmdshell默认情况下是禁用的。我建议你看看喜欢的替代品sqlcmd.exe，bcp.exe或者在您的首选语言的一个小脚本（PowerShell中，Perl，Python和其他）。
It is generally much easier, safer and more flexible to query data from SQL Server than it is to push it out from the server side. In your specific case, it looks like you want to write out a delimited file, and bcp.exeis intended for that purpose.
从 SQL Server 查询数据通常比从服务器端推送数据更容易、更安全和更灵活。在您的特定情况下，您似乎想写出一个分隔文件，并且bcp.exe是为此目的而设计的。
回答by Pasetchnik
Other way to do this
其他方法来做到这一点
DECLARE @File varchar(300) = 'c:\Temp\out.txt'
DECLARE @Text varchar(8000) = 'Sample text'
DECLARE @OLE INT
DECLARE @FileID INT
EXECUTE sp_OACreate 'Scripting.FileSystemObject', @OLE OUT
EXECUTE sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @File, 8, 1
EXECUTE sp_OAMethod @FileID, 'WriteLine', Null, @Text
EXECUTE sp_OADestroy @FileID
EXECUTE sp_OADestroy @OLE
Check for errors using SP result
使用 SP 结果检查错误
EXECUTE @result = sp_OAMethod @OLE, 'OpenTextFile', @FileID OUT, @File, 8, 1
if @result<>0 GOTO ON_ERROR
Haven't found the way to read error message though
还没有找到读取错误信息的方法
回答by John Woo
wrap it with two pairs of single quotes,
用两对单引号包裹它，
DECLARE @Text AS VARCHAR(100)
DECLARE @Cmd AS VARCHAR(100)
SET @Text = '''Hello world| '''
SET @Cmd ='echo ' + @Text + ' > C:\AppTextFile.txt'
EXECUTE Master.dbo.xp_CmdShell @Cmd
