how to use xp_cmdshell with a variable?

[sumgirl]

Hey all. I am no DB / query guru, so bare with me if this is dumb. I
want to run a query that provides a value and then run xp_cmdshell
using that value. Something like the following two step manual op I do
now:

(1) select packageid from v_package where pkgsourcepath like 'c:
\packages'
-- statement above will always return a single value


(2) EXEC master..xp_cmdshell 'dir /s "[value returned from query above
would go here without brackets of course]'


I just want to find a way to do this in one shot so I can batch it up
in some from that can be scheduled. Please help! This is SQL Server
2000 and I am simply using Query Analyzer. Thanks in advance.

 

[jbenson001]

You can use dyncamic sql to do this. There are many examples here if you search the posts.

 

[sumgirl]

I did a search on dynamic SQL and was instantly lost. Any chance you could post an example based on my question so I can try and understand knowing what the results would be?

 

[ca8msm]

Have a go at implementing some dynamic SQL yourself. If you get stuck, post what you have created so far and people will be happy to show you where you may have gone wrong.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[sumgirl]

I know this is lack of expertise / experience and probably totally doofuslike for this form. Heres what I started with, and I have tweaked a bit here and there and cant make it work:

DECLARE @SQLvar nvarchar(200)
DECLARE @SQLwork nvarchar(200)

SET @SQLvar = 'select pkgsourcepath from v_package where name like ' + '''myinstall%''' + ''

SET @SQLwork = 'EXEC master..xp_cmdshell ' + '''dir /s ''' + EXEC(@SQLvar)

EXEC(@SQLwork)

 

[ca8msm]

Before running this (and instead of of until you are certain it is correct):

EXEC(@SQLwork)

run this instead:

PRINT @SQLwork

You should then be able to see what is actually being executed. If it isn't what you expected, you can go back through your code to see where you went wrong.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[jbenson001]

Try somthing like this

DCLARE @SQLvar varchar(200)
DECLARE @SQLwork varchar(200)

SET @SQLvar = select pkgsourcepath from v_package where name like 'myinstall%'

SET @SQLwork = 'EXEC master..xp_cmdshell ' + '''dir /s ''' + @SQLvar

 

[sumgirl]

I tried that jbenson001, but I keep getting "Incorrect syntax near the keyword 'select' on line 3."

I can pull the select out and execute it seperately with no problem, and it returns one value as expected.

I appreciate the help all.

 

[jbenson001]

Put parens around the select statement:

SET @SQLvar = [b]([/b]select pkgsourcepath from v_package where name like 'myinstall%'[b])[/b]

 

[sumgirl]

That got it! Thanks jbenson001, this method has all kinds of obvious uses for me!

 

[jbenson001]

glad to help
Jim