Solved

xp_cmdshell , ECHO and '|' characters

Posted on 2006-12-01
15
2,103 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
Comment Utility
Have you thought about using DTS (if usning SQL Server 2000) or SSIS for 2005?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
Comment Utility
What is the error you are receiving?
0
 

Author Comment

by:mvanral
Comment Utility
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
 
LVL 13

Expert Comment

by:Atlanta_Mike
Comment Utility
Ahhh... I see your problem...

| is a reserved "function" use ||

0
 
LVL 13

Expert Comment

by:Atlanta_Mike
Comment Utility
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
Comment Utility
Well that didn't work right. Sorry.
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
Comment Utility
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:mvanral
Comment Utility
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
Comment Utility
There you go Angel. I was having troubles finding the escape character for the pipe...

0
 

Author Comment

by:mvanral
Comment Utility
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
Comment Utility
Yeah, well.. sometimes the easies thing seems like the most difficult.
0
 

Author Comment

by:mvanral
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
All me...me.me.me... Kidding Angel. ... Do whatever...even if Angel stole my idea :-)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Preface This is the third article about the EE Collaborative Login Project. A Better Website Login System (http://www.experts-exchange.com/A_2902.html) introduces the Login System and shows how to implement a login page. The EE Collaborative Logi…
Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now