Using xp_cmdshell

Question

Using xp_cmdshell

Haidong Ji

SSCrazy

Points: 2453
More actions
December 28, 2003 at 12:00 am

#106963

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/hji/usingxp_cmdshell.asp

Viewing 15 posts - 1 through 15 (of 52 total)

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

bustell Ten Centuries Points: 1209 More actions · Answer 1

xp_cmdshell is a very powerful tool. I have used it in the past to generate DOS FTP scripts and then execute the FTP command to FTP exported data to a client directly from a store procedure.

Pat Buskey

Haidong Ji SSCrazy Points: 2453 More actions · Answer 2

Yes Pat. I agree. xp_cmdshell can handle many automation jobs. I use it a lot in my environment.

You mentioned FTP files using xp_cmdshell. One of my articles in the past talked about automating FTP using VBScript. I am not saying which method is better, just throw it out for your reference (watch out for line breaks):

http://www.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp

Cheers.

Angel-120263 SSC Veteran Points: 201 More actions · Answer 3

Ok.. this SP.. its great.. but.. i am trying to call some BAT or EXE files.. and does not work... Is there somebody that use xp_cmdshell to call one BAT or EXE file.. ?

for example:

xp_cmdshell 'c:\Winnt\System32\calc.exe'

Tnks

bustell Ten Centuries Points: 1209 More actions · Answer 4

Jose,

I am not sure if this is the problem or not. Maybe it has to do with starting a program that requires user input?

I tried to execute calc.exe and it did not work. However, I was able to exec .bat files as long I was not using the .bat file to open calc.exe

If you look at the task manager, it does start the program, but you are denied access to it so maybe it's a permission problem.

Hopefully someone will post a more definitive answer.

Pat Buskey

Haidong Ji SSCrazy Points: 2453 More actions · Answer 5

Jose,

Pat is right. You will not see Calc started unless you are running SQL Server as a console application, because Calc is going to be a subprocess that has an interface and requires user interaction.

xp_cmdshell is generally used to execute

operating-system command. It is not designed to start applications which have a user interface.

If you start an application like Calc which has a user

interaction via xp_cmdshell, xp_cmdshell will start your application, pass the control to your application, and wait indefinitely until it ends. Since

you cannot close such an application manually while it is running in background, xp_cmdshell will never regain control from your application. You may have to kill the corrrespoding process manually via Task Manager-->Processes tab to terminate the application and allow xp_cmdshell

to get the control back.

For more information, check out xp_cmdshell on BOL.

Hope this helps.

Haidong

Angel-120263 SSC Veteran Points: 201 More actions · Answer 6

Yes.. Haidong..

I wasn't in my 5 sense.. jejeje... i was complicating my life.. jejeje.. The EXE file.. was running..(Not Calc.exe... my Application) but i want to see it.. to believe .. jejeje..

Tnks.. for your message..

neotokyo SSCertifiable Points: 7239 More actions · Answer 7

what about xp_cmdshell with jobs?

i understand you can start a job from a different server, but can't

find any good examples.

_________________________

Haidong Ji SSCrazy Points: 2453 More actions · Answer 8

You can consider the master/target server administration technique. You can also try dmo/smo to start a job from a different machine.

kivancimer SSC Enthusiast Points: 118 More actions · Answer 9

Hello all,

I have a question

Everytime i use xp_cmdshell on sql server 2000..cmd.exe porcess lefts open everytime.Once there was 80 cmd.exe process n task manager.how can i kill those processes after its done

RBarryYoung SSC Guru Points: 143329 More actions · Answer 10

Please provide examples of your code for those cases where the CMD.EXE's are hanging around.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

kivancimer SSC Enthusiast Points: 118 More actions · Answer 11

Thanks for response but i have figured out the problem.it was about bad exception handling.

Anvie Ten Centuries Points: 1246 More actions · Answer 12

I have the same problem. My cmd.exe left open even the processes is already completed.

EXECUTE xp_cmdshell '"D:\Folder\Task.exe" & exit'

Can you please help me to solve this.

Thanks!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

When I try to execute this I get "The system cannot find the path specified."

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

By the way, I would not use the " & Exit". It should not be necessary and it may be what is causing your problem.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]