如何使用带有文本“|"的sql脚本创建文本文件 [英] How to create text file using sql script with text "|"

问题描述

如果我在没有字符|"的情况下运行脚本它工作但当我添加字符|"它不工作如何添加字符|"使用sql脚本到文本文件?

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 ?

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

谢谢

推荐答案

管道符在批处理命令中有特殊含义，所以必须是 转义 使用插入字符.这应该有效:

The pipe character has a special meaning in batch commands, so it must be escaped using 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

虽然这确实不是一个向文本文件写入数据的好方法:通常SQL Server应该没有写入C:盘根目录的权限，而且xp_cmdshell默认是禁用的.我建议您查看诸如 sqlcmd.exe、bcp.exe 或您首选语言(PowerShell、Perl、Python 等)的小脚本之类的替代方案.

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_cmdshell is disabled by default. I suggest you look at alternatives like sqlcmd.exe, bcp.exe or a small script in your preferred language (PowerShell, Perl, Python, whatever).

从 SQL Server 查询数据通常比从服务器端推送数据更容易、更安全和更灵活.在您的特定情况下，您似乎想写出一个分隔文件，并且 bcp.exe 用于此目的.

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.exe is intended for that purpose.

这篇关于如何使用带有文本“|"的sql脚本创建文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！