Help with xp_cmdshell....URGENT!!!!!!

[tlaksh]

Hi,

I have sql code that uses the xp_cmdshell procedure to call and run a .bat file. I am passing a variable value to the .bat file from the sql code.

Now at the dos prompt if I type the following it works..

C:\event.bat %username%

This picks up the name of the user and passes it as a parameter to the event.bat file

But if I pass this value through xp_cmdshell..

EXEC master..xp_cmdshell 'C:\event.bat %username%'

it does not take the value of username but treats
%username% as the value to the parameter in the event.bat file

Why is this? Can anybody help me with this..or is there a way of going around it??

Thanks for all the help...

Lakshmi.

 

[tlbroadbent]

When you excute a bat file via xp_cmdshell, it executes in it's own space and doesn't have all of the SET values you see in the DOS window. %username% is probably not defined.

When executing xp_cmdshell, just pass a parameter such as xp_cmdshell myname without the "%" because it will always run under the same username. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Andel]

declare @username varchar(30)
declare @cmd varchar(30)
set @username = 'yourusername'
set @cmd = 'c:\event.bat ' + @var
exec master..xp_cmdshell @cmd

Andel
andelbarroga@hotmail.com

http://andyb.8m.com

 

[tlaksh]

Hi Terry

%username% was only a example.. I need to pass a variable value to the event.bat..i.e computer name that i am picking up from the sql table...and running this bat file for all the computernames in the table...

Hence i cannot hardcode the value in xp_cmdshell

Thanks
Lakshmi

 

[tlbroadbent]

Correction:

When you excute a bat file via xp_cmdshell, it executes in it's own space and doesn't have all of the SET values you see in the DOS window. %username% is probably not defined.

When executing xp_cmdshell, just pass a parameter such as xp_cmdshell 'c:\myfile.bat myname' without the "%" around "myname" because it will always run under the same username. Terry

http://members.home.net/tlbroadbent/index.htm

 

[tlbroadbent]

If your values are available in a SQL table then do as recommended by Andel.

declare @username varchar(30), @compname varchar(30), @cmd varchar(128)

select @username = username, @compname = compname
From yourtable

set @cmd = 'c:\event.bat ' + @username + ',' + @compname

exec master..xp_cmdshell @cmd

Of course this only executes one time. You can easily incorporate this into a stored procedure with a cursor to execute for each occurrence in your table.
Terry

http://members.home.net/tlbroadbent/index.htm

 

[tlaksh]

Thanks u guys....it worked....

UR help is highly appreciated.

Lakshmi