How to find SPID of a executing SqlAgent job using SMO

Question

With SMO objects using Server.JobServer.jobs to get a list of jobs, I can find the status of each job. For those that are currently executing I would like to find the SPID it is executing on. I can also get a list of the server's processes using Server.EnumProcesses(). This gives me a list of currently active SPIDs. I want to match the two.

The best I've been able to come up with is to convert the jobid to a string and substring the jobId out of the program string in the EnumProcesses table (which, at least on my system, embeds the jobId in this description). It's really ugly for a couple of reasons not the least of which is that the Guid in the program description and the guid for jobID have their bytes switched in the first 3 pieces of the string representation. Yuck.

Is there a better way to do that using SMO?

Answer 1

Using SMO, not that I know of.

I have done this using T/SQL. Take the job_id value and cast it to varbinary. That should then match the value in the application name (after the "SQL Agent" part).