Solved

xp_cmdshell , ECHO and '|' characters

Posted on 2006-12-01
15
2,146 Views
Last Modified: 2013-11-18
I am trying to ECHO data to a file.

works:
    Select @FileString  = 'abc,cde,fgh'
    SELECT @EchoString = 'echo ' + @FileString + ' > ' + @FileName

    EXEC @result =  master..xp_cmdshell @EchoString , no_output

Fails:
    Select @FileString  = 'abc|cde|fgh'
    SELECT @EchoString = 'echo ' + @FileString + ' > ' + @FileName

    EXEC @result =  master..xp_cmdshell @EchoString , no_output

Works, but puts the double quote into the file:
    Select @FileString  = 'abc|cde|fgh'
--Note below has double quotes to go around @FileString
    SELECT @EchoString = 'echo "' + @FileString + '" > ' + @FileName

    EXEC @result =  master..xp_cmdshell @EchoString , no_output

How can I get the string with vertical bars in it into a file without the double quotes also going to the file?

Thanks

Marcus
0
Comment
Question by:mvanral
  • 9
  • 4
  • 2
15 Comments
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18055973
Have you thought about using DTS (if usning SQL Server 2000) or SSIS for 2005?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18055992
What is the error you are receiving?
0
 

Author Comment

by:mvanral
ID: 18056076
Yes, but this was meant to be a quick solution to generate a file of column headers....

The error number if i do not include the double quotes is -1, and is because it cuts the string at the first '|' If i enclose the string in double quotes it puts into the file as intended, BUT includes the double quotes themselves...

Any thoughts...
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18056333
Ahhh... I see your problem...

| is a reserved "function" use ||

0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18056380
Like this

    Select @FileString  = 'abc||cde||fgh'
--Note below has double quotes to go around @FileString
    SELECT @EchoString = 'echo "' + @FileString + '" > ' + @FileName

    EXEC @result =  master..xp_cmdshell @EchoString , no_output
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18056385
Well that didn't work right. Sorry.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18056493
How about something like ...

DECLARE @FileString varchar(50), @FileName varchar(50), @EchoString varchar(50), @result int

EXEC @result =  master..xp_cmdshell 'osql -E -dDatabaseName -Q"SELECT ''abc|cde|fgh''" -h-1 -w1000 -oC:\TEST.TXT', no_output
0
 

Author Comment

by:mvanral
ID: 18056581
I tried that and it still stops on the first one and fails and if in double quotes, then the double quotes is the problem...
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18056857
the following should work correctly, note that some "double" quotes are actually 2 single quotes..., ie around the abc.. string...
EXEC @result =  master..xp_cmdshell 'osql -E -dDatabaseName -Q"SELECT ''abc|cde|fgh'' " -h-1 -w1000 -o"C:\TEST.TXT" ', no_output

in regards to the echo, RTFM:
http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/echo.mspx?mfr=true

    Select @FileString  = 'abc^|cde^|fgh'
    SELECT @EchoString = 'echo ' + @FileString + ' > ' + @FileName
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18056916
There you go Angel. I was having troubles finding the escape character for the pipe...

0
 

Author Comment

by:mvanral
ID: 18057048
Please note the entries above are out of order!!!

I have just tried the osql idea (it occurs to me that bcp out would work also)

It worked except for it adding:

(1 row affected)

into the file also....

I have gone looking for a parameter ala SET NOCOUNT ON but did not find one... [as osql works on batches, maybe i could use the nocount]

This seems like a clumbsey solution - for this much effort i should have just used DTS/SSIS....
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18057072
Yeah, well.. sometimes the easies thing seems like the most difficult.
0
 

Author Comment

by:mvanral
ID: 18057129
angelIII - that worked perfect. As often the way - find the correct docs & correct way of looking at the problem - all is easy again.

i am happy with this answer - how should i divy up the points. angelIII had the correct answer and therefore earned the points. If that is correct, please confirm and i will do that - I do appreciate atlanta mike's efforts & fun tour through osql...

Thanks

Marcus
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18057169
you are "free" to distribute the points for "effort".
I guess the others will not be angry if I get all the points (as I gave the solution), as I will not be angry if you split the points.
Glad to be of help, that's why I am here.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 18057215
All me...me.me.me... Kidding Angel. ... Do whatever...even if Angel stole my idea :-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 41
MS SQL: Getting all rows not just one , combining multiple queries 11 27
SQL Query 2 34
job schedule 8 21
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how the fundamental information of how to create a table.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question