Hi Bru...

my post in the other thread (didnt realise how old it was sorry, did a search) was about a different problem with a similar script... was asking for an SQL Server 2000 equivalent of the posters script as it was for SQL Server 2005+ using sys.dm_exec_sql_text(p.sql_handle).

I am trying to create a query or stored procedure to get further information than sysprocesses provides, eg:

job_id, job_name, step_id, step_name, dts_name. I can get all that to work except for the 'dts_name'. I am simply asking if there is a way to make that exact script work. 🙂

Anyhow... some more info as requested...

I'd like to use it for troubleshooting mainly, eg: by running it in a scheduled job.

This has stemmed from a hanging DTS issue which I still haven't been able to find a solution for despite posting on several forum websites. 🙁 Intermittently a DTS will hang & I'd actually like to find the DTS's SPID so I can kill it off. Also since I'm using xp_cmdshell(...dtsrun...) too I can't just kill the job's SPID as that waits for teh xp_cmdshell to finish first.

🙂

Eg: http://www.sqlservercentral.com/Forums/Topic597931-5-1.aspx