[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

xp_cmdshell , ECHO and '|' characters

Posted on 2006-12-01
15
Medium Priority
?
2,265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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 …
Suggested Courses

649 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