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 '+'.
Legacy Comments
|
James Curran
2004-03-29 |
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 |
|
Tara
2004-03-29 |
re: xp_cmdshell How is that different from what I posted though? I showed what is legal and what is not. |
|
James Curran
2004-03-29 |
re: xp_cmdshell THis works also.... EXEC ('master.dbo.xp_cmdshell ''copy C:\temp\test.txt \\' + @ServerName +'\SomeShare\'' ') |
|
James Curran
2004-03-29 |
re: xp_cmdshell Ooops... Sorry, didn't notice the top half.... |
|
MeanOldDBA
2004-03-29 |
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 + '''') |
|
Tara
2004-03-29 |
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. |
|
DavidM
2004-03-29 |
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... |
|
Tara
2004-03-29 |
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. |
|
DavidM
2004-03-29 |
re: xp_cmdshell It is implied because it does not say "expression".. Check the difference between PRINT or the UPPER function.... |
|
Tara
2004-03-29 |
re: xp_cmdshell Ok, I see that now. |
|
Cindy
2004-03-30 |
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\"') |
|
Lavos
2004-03-30 |
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. |
|
Tara
2004-03-30 |
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. |
|
James Curran
2004-03-31 |
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. |
|
Charles
2004-05-21 |
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 |
|
Tara
2004-05-21 |
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. |
|
samba
2006-04-17 |
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. |
|
Jack Diamond
2006-06-21 |
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" |