Using xp_cmdshell

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

  • 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

  • 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.

  •  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 

  • 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

  • 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

  • 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..

  • what about xp_cmdshell with jobs?

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

    find any good examples.

    _________________________

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

  • 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

  • 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]

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

  • 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!

  • 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]

  • 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]

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

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