xp_cmdshell and & - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

xp_cmdshell and &

Author

Topic

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-08 : 15:41:08

I have a sproc that writes one-line of space-delimited data to a text file, using xp_cmdshell
SET @cmd = 'ECHO ' + @data + ' > '+@Path+@fileName
EXEC xp_cmdshell @cmd
All is well and good, until the data looks like
HSUB1234 Smith & Wesson"

That & is bombing everything out
'Wesson' is not recognized as an internal or external command,operable program or batch file.

How do I get around this?

Thanks,
Jim

Everyday I learn something that somebody else already knew

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-12-08 : 15:46:27

SET @cmd = 'ECHO "' + @data + '" > '+@Path+@fileName

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-08 : 15:50:55

Thanks for the quick reply! Unfortunately this creates a text file that looks like
"HSUB2134 Smith & Wesson", which bombs out the app that uses it. Is there a way to get the file to contain just HSUB1234 Smith & Wesson, without any quotes or anything?

Thanks,

Jim

Everyday I learn something that somebody else already knew

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-12-08 : 15:57:02

This is actually a DOS problem and not a SQL problem but try

SET @cmd = 'ECHO ' + replace(@data, '&', '^&') + ' > '+@Path+@fileName

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-08 : 16:04:45

That makes it look like Smith ^& Wesson. I can of course use replace (@data,' & ',' and ')

but Smith and Wesson isn't what a company wants to see. Thanks for your help, I'll keep hacking at the keyboard until something works!

Jim

Everyday I learn something that somebody else already knew

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2009-12-08 : 16:13:51

It should look like that in the @CMD variable but ^ is the escape character for & and should result in
Smith & Wesson
as the result in the file

declare @CMD varchar(2000)
declare @data varchar(2000)
set @data = 'Smith & Wesson'

SET @cmd = 'ECHO ' + replace(@data, '&', '^&') + ' > c:\temp\test.txt'
select @CMD
exec master..xp_cmdshell @CMD

Works for me.
C:\Temp\test.txt contains
Smith & Wesson

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-08 : 16:30:03

It must have been a freak copy and paste error, I recopied and it worked!

Thanks again,

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources