Clever Geek Handbook

How to find the SPID of a running SqlAgent job using SMO

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

The best I could think of is to convert the jobid to a string and tweak the jobId from the program string in the EnumProcesses table (which, at least on my system, inserts the jobId value into this description). This is really ugly for several reasons, not least because the Guid in the program description and the guid for the jobID have their bytes included in the first 3 parts of the string representation. Ugh.

Is there a better way to do this with SMO?

+1


source to share


2 answers


Using SMO, not what I know.



I did it using T / SQL. Take the job_id value and add it to varbinary. This should then match the value in the application name (after the SQL Agent part).

+1


source


Use SP_who2, in the Program result field you will find SQLAgent-Jobid. you will get the job id from sysjobs from the MSDB table. now you can get job sp_id



+1


source






More articles:


All Articles