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

Answer 1

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.