Passing Variables into xp_cmdshell

Question

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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.