How to create text file using sql script with text "|"

Question

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

thanks

what is the purpose of `|` in your query? – John Woo Jan 11 '13 at 09:55 — John Woo, Jan 11 '13 at 09:55
i want to add data in text file in format – Yogesh Jan 11 '13 at 10:17 — Yogesh, Jan 11 '13 at 10:17

score 7 · Accepted Answer · edited May 23 '17 at 12:34

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

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).

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.

It's not necessarily a bad practice. This could be used from a stored procedure, to produce a set of files. SQL Server shouldn't have permissions to write anywhere, but it is fine to give it permissions to write a specific directory. — Federico Razzoli, Jul 30 '21 at 15:17

score 4 · Answer 2 · answered Apr 09 '18 at 12:32

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

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

Much better solution if your `@Text` contains lots of characters that don't fit nicely into the command prompt. If you're hitting permission issues with `sp_OACreate` see [this answer](https://stackoverflow.com/a/49873229/930393) — freefaller, Aug 12 '20 at 13:36

score 0 · Answer 3 · edited Jan 11 '13 at 10:09

0

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

edited Jan 11 '13 at 10:09

Jon Egerton

40,401
11
97
129

answered Jan 11 '13 at 09:59

John Woo

258,903
69
498
492

''' is not recognized as an internal or external command, operable program or batch file. NULL – Yogesh Jan 11 '13 at 10:03
'"Hello world| "' is working but i dont want " " in text file – Yogesh Jan 11 '13 at 10:10
how should `@cmd` looked like? – John Woo Jan 11 '13 at 15:40

How to create text file using sql script with text "|"

3 Answers3

Linked