xp_cmdshell , ECHO and '|' characters

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
mvanralAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Atlanta_MikeCommented:
Have you thought about using DTS (if usning SQL Server 2000) or SSIS for 2005?
0
 
Atlanta_MikeCommented:
What is the error you are receiving?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mvanralAuthor Commented:
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
 
Atlanta_MikeCommented:
Ahhh... I see your problem...

| is a reserved "function" use ||

0
 
Atlanta_MikeCommented:
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
 
Atlanta_MikeCommented:
Well that didn't work right. Sorry.
0
 
Atlanta_MikeCommented:
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
 
mvanralAuthor Commented:
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
 
Atlanta_MikeCommented:
There you go Angel. I was having troubles finding the escape character for the pipe...

0
 
mvanralAuthor Commented:
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
 
Atlanta_MikeCommented:
Yeah, well.. sometimes the easies thing seems like the most difficult.
0
 
mvanralAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Atlanta_MikeCommented:
All me...me.me.me... Kidding Angel. ... Do whatever...even if Angel stole my idea :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.