Professional Web Applications Themes

Why would xp_cmdshell stop working? - Microsoft SQL / MS SQL Server

I have a stored procedure that stopped working today. The code that fails uses xp_cmdshell to run pkzipc.exe. I have reduced the procedure to the following code that is failing. Running this from cmd prompt works fine - from my sp, runs forever without doing anything. set cmd = 'pkzipc -extract d:\incr\zipfile d:\incr EXEC master..xp_cmdshell cmd Has been working for >100 days with no apparent problems. I did find that I could see dozens of CMD sessions running in Task Manager. Trying to kill the process tree on one of them increases the CPU Useage by about 50% and doesn't ...

  1. #1

    Default Why would xp_cmdshell stop working?

    I have a stored procedure that stopped working today. The code that fails
    uses xp_cmdshell to run pkzipc.exe. I have reduced the procedure to the
    following code that is failing. Running this from cmd prompt works fine -
    from my sp, runs forever without doing anything.

    set cmd = 'pkzipc -extract d:\incr\zipfile d:\incr
    EXEC master..xp_cmdshell cmd

    Has been working for >100 days with no apparent problems. I did find that I
    could see dozens of CMD sessions running in Task Manager. Trying to kill
    the process tree on one of them increases the CPU Useage by about 50% and
    doesn't close that task after 2-3 minutes.

    I would like to do this a different way but I'm not sure if there is a
    better way to have SQL extract these files.

    This is happening on a Windows 2000 Server with SP3, SQL 2000 with SP3.
    Just installed the latest recommended MS patches this last weekend.

    Is there a better way to do this?
    Any ideas on how to fix it?

    Thanks


    Richard Guest

  2. #2

    Default Re: Why would xp_cmdshell stop working?

    User changed permissions? Permissions on d:\ or the folder where pkzpic
    lives? Someone change the user SQL Server / agent logs on / runs as?





    "Richard Sutterfield" <com> wrote in message
    news:#phx.gbl... 



    Aaron Guest

  3. #3

    Default Re: Why would xp_cmdshell stop working?

    Are those processes self-cleaning up? Is there a hidden limit as to how many
    concurrent pkzip commands could run? Just my guessing.

    I used to use winzip to do compress and uncompress. The command looks
    similar:

    DECLARE cmd varchar(250)
    SET cmd = 'c:\wzzip.exe c:\mydb.zip c:\mydb.bak'
    exec master..xp_cmdshell cmd

    Use wzunzip.exe to replace wzzip.exe for decompress. This is with Winzip 8.0
    command line utilities.



    Richard


    "Richard Sutterfield" <com> wrote in message
    news:%phx.gbl... 



    Richard Guest

  4. #4

    Default Re: Why would xp_cmdshell stop working?

    I'm not sure what you mean by self-cleaning up. I don't see anything in
    your code example that would clean up so I am assuming that you mean does my
    other code prevent multiple stored procedures from running. My stored
    procedure should only allow one session at a time to run pkzipc.exe and
    won't run again until it finishes. I'm not sure how all these sessions got
    hung or whatever happened. I also tried just running a directory command
    and piping it into a file. That query ran for over an hour and still hadn't
    done anything when I cancelled it. It took about 20 minutes to cancel. No
    permissions have changed (this isn't totally in my control and has happened
    before when I wasn't apprised of the change so I'm still looking at this).
    I'm afraid I have so many aborted CMD sessions that it's hosing up my
    server. I'm going to reboot tonight and start debugging this thing a step
    at a time. Any other ideas would be appreciated. Thanks.

    "Richard Ding" <com> wrote in message
    news:phx.gbl... 
    many 
    8.0 


    Richard Guest

  5. #5

    Default Re: Why would xp_cmdshell stop working?

    I have been told no permissions or logins have changed but I have been told
    that before when changes had been made so I'm checking further. Will
    probably restart and debug a step at a time to see if I can get an error
    message or something somewhere. Any other ideas would be appreciated as
    well. Thanks.

    "Aaron Bertrand - MVP" <com> wrote in message
    news:phx.gbl... [/ref]
    fails [/ref]
    fine - [/ref]
    that [/ref]
    kill [/ref]
    and 
    >
    >[/ref]


    Richard Guest

  6. #6

    Default Re: Why would xp_cmdshell stop working?

    Figured out it was another sp (recently changed) that used xp_cmdshell to
    run an external process that was leaving CMD sessions hung. This one was
    initiated by an ASP page and so didn't have any limit on how many would run.
    As soon as I rebooted the server, the sp that ran pkzipc.exe worked fine.
    Thanks for the help.


    Richard Guest

Similar Threads

  1. Question stop(); not working?
    By Unregistered in forum Brainstorming Area
    Replies: 0
    Last Post: February 13th, 11:34 PM
  2. DataBindings stop working!
    By mab_bond in forum Coldfusion Flash Integration
    Replies: 0
    Last Post: December 14th, 07:47 PM
  3. sshd stop working
    By Micha Feigin in forum Debian
    Replies: 1
    Last Post: August 1st, 09:10 PM
  4. How stop a xp_cmdshell process ???
    By lubiel in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 10:02 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139