SQl job find running state with open query

sp_configure 'show advanced options', 1;

RECONFIGURE;




Go



sp_configure 'Ad Hoc Distributed Queries', 1;

RECONFIGURE;  


Declare @SQLServerJobID varchar(100)

select @SQLServerJobID=job_id from msdb..sysjobs where name ='Job name'

Declare @DynSQL Nvarchar(MAX)

Declare @current_execution_status int

SET @DynSQL =

'SELECT




@current_execution_status=current_execution_status

FROM OPENROWSET


(''SQLOLEDB'', ''Server=.;TRUSTED_CONNECTION=YES;'',''SET FMTONLY OFF EXEC msdb..sp_get_composite_job_info @job_id=''''' + CONVERT(NVARCHAR(50),@SQLServerJobID) + ''''''') '

EXEC sp_executesql @DynSQL, N'@current_execution_status INT OUTPUT',@current_execution_status output

Select @current_execution_status