Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Location
    NM
    Posts
    2

    Exclamation Unanswered: SQL 2000 job hanging

    My company uses SQL2000 SP2 on Windows 2000 and we're seeing intermittent hanging on specific SQL jobs. The only commonality I can identify is that they all use xp_cmdshell.

    Here is an example:
    A scheduled job runs a stored procedure called sp_Run_rpt. The sp_Run_rpt is below:
    CREATE PROCEDURE sp_Run_rpt (@strType varchar(1) = null,
    @strDBServer varchar(20) = null,
    @strDBName varchar(20)) AS
    /*--------------------------------------------------*/
    /* Execute a report and save output in a text file. */
    /*--------------------------------------------------*/
    set DEADLOCK_PRIORITY Low
    declare @strCommand varchar(255)
    select @strCommand = 'd:\Rpt_run.bat ' + @strType + ' ' + @strDBServer + ' ' + @strDBName
    EXEC master..xp_cmdshell @strCommand, no_output
    RETURN 0
    /*--------------------------------------------------*/
    /* //end */
    /*--------------------------------------------------*/
    GO

    This job runs every 15 minutes, and will sometimes run successfully for days before it hangs. Can anyone help?

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    the agent will sit there waiting for the xp_cmdshell to complete. If it doesn't complete then it will hang.
    This is often because someone has put some user interaction in the called exe but can be anything wrong with it.

  3. #3
    Join Date
    Feb 2003
    Posts
    4

    Re: SQL 2000 job hanging

    i had the same problem and i upgraded to sp3. It seams that the problem has been resolved.

  4. #4
    Join Date
    Feb 2003
    Location
    NM
    Posts
    2

    Exclamation RE: SP3

    SQL 2000 SP3 or an OS SP3?

    From what I've heard the SQL 2000 SP3 only fixed security issues and probably would not address this issue.

    Please let me know, I'm very interested in solving this problem. When you experienced the issue, was it intermittent, like ours is?

    Thanks for your help!

Posting Permissions

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