?
Solved

xp_cmdshell , ECHO and '|' characters

Posted on 2006-12-01
15
Medium Priority
?
2,299 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
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 …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

809 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