[dba-SQLServer] Passing variable to xp cmdshell

Doug Steele dbdoug at gmail.com
Wed Apr 15 10:22:48 CDT 2009


The following worked for me:
1. (from the Help system)
DECLARE @cmd sysname, @var sysname
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd

2.
CREATE PROCEDURE zz
 (@cmdin varchar(255))
AS EXEC master..xp_cmdshell @cmdin

followed by

exec zz 'dir "E:\A_Projects"'

Doug Steele


On Wed, Apr 15, 2009 at 1:32 AM, Paul Hartland <paul.hartland at googlemail.com
> wrote:

> To all,
>
> I am having a problem using xp cmdshell in SQL Server 2005, I won't go into
> too much detail but basically I am just trying to put a folder list into a
> temporary table.
>
> if I use exec master..xp cmdshell 'dir "myserver\myinitialfolder" /b'  it
> works fine, however I need to generate the folder on the fly, but if I
> declare a variable say @folder varchar(4000) and then have exec master..xp
> cmdshell @folder I get
>
> Msg 214, Level 16, State 201, Procedure xp cmdshell, Line 1
> Procedure expects parameter 'command_string' of type 'varchar'.
>
> Anyone any ideas whats happening, or what I am doing wrong ?
>
> Thanks is advance for any help on this.
>
> --
> Paul Hartland
> paul.hartland at googlemail.com
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>



More information about the dba-SQLServer mailing list