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?

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.

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

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!