Passing Variables into xp_cmdshell

• dtevlin

  Ten Centuries

  Points: 1084

  More actions

  April 27, 2005 at 11:56 pm

  #166594

  What I need to do is pass a path varaible stored in a table into a cmdshell call, but I can't seem to figure it out.

  DECLARE @var varchar, @cmd

  SET @var = select path_name from PathVariables where active =1

  SET @cmd = 'IF EXISTS @var ...stuff'

  Exec master..xp_cmdshell @cmd

  Thanks

• philcart

  SSC-Forever

  Points: 47796

  More actions

  April 28, 2005 at 1:56 am

  #555445

  How about

  DECLARE @var varchar, @cmd
  SET @var = select path_name from PathVariables where active =1
  SET @cmd = 'IF EXISTS ' + @var + ' ...stuff'
  Exec master..xp_cmdshell @cmd

  Or if you're daring, try using xp_fileexist or xp_getfiledetails, then do '...stuff' 

  --------------------
  Colt 45 - the original point and click interface

• Rob Reid

  SSC Enthusiast

  Points: 138

  More actions

  April 29, 2005 at 10:11 am

  #556012

  Try this

  Eg if the @path var contains 'c:\' from your table it will output the contents of the directory.

  declare @cmd varchar(150)

  declare @path varchar(100)

  select @path = path from tblTest

  set @cmd = 'dir "' + @path + '"'

  exec master..xp_cmdshell @cmd

   

  if your path has spaces in filenames etc make sure you enclose the path with " "

  Rob Reid

  Strategies UK

• dtevlin

  Ten Centuries

  Points: 1084

  More actions

  April 29, 2005 at 2:23 pm

  #556089

  Thanks for all your help. It definately put me on the right road.

  I went with:

  Declare @cmd varchar(100), @path varchar(50), @path2 varchar(50)

  SET @path = (Select column from table where condition = 'condition')

  SET @path2=(Select column from table where condition = 'condition')

  SET @cmd = 'copy ' +@path+ ' '+@path2+''

  EXEC master..xp_cmdshell @cmd

  what I found was the need to put a space between the 2 paths which is why there are a pair of single quotes with a space between them.