Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work
<< TechNet Webcast: Authoring Reports in SQL Server 2000 Reporting Services – Level 200 | Home | TechNet Webcast: The Guru's Guide to SQL Server 2000 Memory Management Intervals – Level 300 >>

xp_cmdshell

Why is it that you can pass a built string to xp_cmdshell but you can't build your string at the same time.  What I mean is this:

This is legal:

DECLARE @cmd VARCHAR(255)
DECLARE @ServerName SYSNAME

SET @ServerName = 'Server1'
SET @cmd = 'copy C:\temp\test.txt \\ + @ServerName + '\SomeShare\'

EXEC master.dbo.xp_cmdshell @cmd

 

This is not legal:

DECLARE @ServerName SYSNAME

SET @ServerName = 'Server1'

EXEC master.dbo.xp_cmdshell 'copy C:\temp\test.txt \\ + @ServerName + '\SomeShare\'


 Output:

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '+'.

Any comments?

Print | posted on Monday, March 29, 2004 2:22 PM | Filed Under [ SQL Server - General ]

Feedback

Gravatar

# re: xp_cmdshell

I have no expanation.
But this works:
DECLARE @ServerName SYSNAME
SET @ServerName = 'Server1'

DECLARE @CmdString Varchar(100)
SET @CmdString = 'copy C:\temp\test.txt \' + @ServerName + '\SomeShare\'

EXEC master.dbo.xp_cmdshell @CmdString
3/29/2004 3:12 PM | James Curran
Gravatar

# re: xp_cmdshell

How is that different from what I posted though? I showed what is legal and what is not.
3/29/2004 3:17 PM | Tara
Gravatar

# re: xp_cmdshell

THis works also....

EXEC ('master.dbo.xp_cmdshell ''copy C:\temp\test.txt \\' + @ServerName +'\SomeShare\'' ')
3/29/2004 3:23 PM | James Curran
Gravatar

# re: xp_cmdshell

Ooops... Sorry, didn't notice the top half....
3/29/2004 3:24 PM | James Curran
Gravatar

# re: xp_cmdshell

And you can do this, which is just really messed up.


DECLARE @cmd NVARCHAR(2500)

SELECT @cmd = '.exe'
EXEC('EXEC xp_cmdshell ''' + 'dir *py' + @cmd + '''')
3/29/2004 3:28 PM | MeanOldDBA
Gravatar

# re: xp_cmdshell

Well yes I could use dynamic sql, but I'd rather avoid that. I don't really need a solution for this as I can just build the string first and then pass it to xp_cmdshell. I'm really just curious as to why it won't allow the "not legal" way that I posted.
3/29/2004 3:31 PM | Tara
Gravatar

# re: xp_cmdshell

Well according to BOL, the "command string" must be a string and not an expression ie: must be a constant... Compared to the PRINT command which can take a string expression...

I think all xp procs use this convention...

3/29/2004 4:00 PM | DavidM
Gravatar

# re: xp_cmdshell

I read the xp_cmdshell topic in BOL but didn't see that in there. This is all I see in BOL:

'command_string'

Is the command string to execute at the operating-system command shell. command_string is varchar(255) or nvarchar(4000), with no default. command_string cannot contain more than one set of double quotation marks. A single pair of quotation marks is necessary if any spaces are present in the file paths or program names referenced by command_string. If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround.


3/29/2004 4:13 PM | Tara
Gravatar

# re: xp_cmdshell

It is implied because it does not say "expression".. Check the difference between PRINT or the UPPER function....
3/29/2004 4:17 PM | DavidM
Gravatar

# re: xp_cmdshell

Ok, I see that now.
3/29/2004 4:24 PM | Tara
Gravatar

# re: xp_cmdshell

This works:

DECLARE @ServerName SYSNAME, @cmd varchar(255)

SET @ServerName = 'srv-filer'

exec ('xp_cmdshell "copy C:\temp\block.sql \\' + @ServerName + '\Admin\"')

3/30/2004 7:42 AM | Cindy
Gravatar

# Call me mean but....

I almost hate to say it, but I think the comments really go to show who "gets it" and who doesn't when answering questions.

This behavior is the same as it is for the linked server rowset functions, except that those don't even allow you to use a variable.

Either the string is "hard coded" or you have to use dynamic SQL.


As a followup question, *why* does it make a difference whether it is a literal string, varchar variable, or expression that evaluates to a varchar?

I'm guessing it has to do with limitations in the parsing engine and how it builds up what commands it's going to execute.
3/30/2004 3:11 PM | Lavos
Gravatar

# re: xp_cmdshell

I wasn't looking for a solution or work around to the problem, just an answer as to why it behaved that way. David answered it for me.
3/30/2004 3:16 PM | Tara
Gravatar

# re: xp_cmdshell

>> *why* does it make a difference whether it is a literal string, varchar variable, or expression that evaluates to a varchar? <<

I don't think it's anything special like that. I remember years ago (Eeek! a quarter century!) when I was ploughing through a disassembly of the BASIC interpreter (written by Bill Gates personally) that came with the original Radio Shack TRS-80. When I got to the part where it parsed lines like "GOTO 1100", it specically looked for a numeric value after the goto. But had he substituted one function call for another (ie, a two byte change in the ROM), it could have handled lines like "GOTO 1100 + (X*10)". As far as I could see, there was absolutely no technical reason why it couldn't be done. It was strictly a business decision, and probably not a fully thought through one.
3/31/2004 7:02 AM | James Curran
Gravatar

# re: xp_cmdshell

Why can't filenames with spaces work. The following gives nonesense output:

DECLARE @CmdString Varchar(100)
SET @CmdString = 'dir C:\temp\test two.txt'

EXEC master.dbo.xp_cmdshell @CmdString
5/21/2004 3:59 AM | Charles
Gravatar

# re: xp_cmdshell

SQL Server Books Online says:

"If you have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround."

You could also try using brackets.
5/21/2004 9:12 AM | Tara
Gravatar

# re: xp_cmdshell

xp_cmdshell 'osql -S PM05242POS01 -E -q"exec master..xp_cmdshell "NET START SQLSERVERAGENT""'
does not work

but xp_cmdshell 'osql -S PM05242POS01 -E -q"exec master..xp_cmdshell "ipconfig""'
works

how to get it to work with spaces in the command

Thanks,
Samba.
4/17/2006 1:35 PM | samba
Gravatar

# re: xp_cmdshell

It seems that the only problem is really that there is a missing quotation mark in the section above saying "This is NOT legal"
6/21/2006 7:33 AM | Jack Diamond
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET

Copyright © Tara Kizer