1 BEST ANSWER
Best Answers
Hi Jamuna,
Please remove GO from the statement. It is not needed for a single statement procedure.
I have tested this in my environment and you will likely encounter an issue where Blue Prism complains about "Can't convert System.Guid to a Blue Prism data type.". In this case, I would advise you to use the SELECT query behind the stored procedure dbo.sp_help_jobactivity to do what you need. The trick is to convert the UNIQUEIDENTIFIER column job_id to CHAR(36). The query may look like below, note the highlighted sections:
SELECT ja.session_id, CONVERT(CHAR(36),ja.job_id) AS job_id,j.name AS job_name,ja.run_requested_date,ja.run_requested_source,ja.queued_date,ja.start_execution_date,ja.last_executed_step_id,ja.last_executed_step_date,ja.stop_execution_date,ja.next_scheduled_run_date,ja.job_history_id,jh.message,jh.run_status,jh.operator_id_emailed,jh.operator_id_netsent,jh.operator_id_paged FROM(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id=jh.instance_id) join msdb.dbo.sysjobs_view j on ja.job_id=j.job_id WHERE j.name='job name'
Note you will need to execute the query using an account with sufficient permission to msdb database.
In my testing, I am able to obtain a collection like below:
Hope this helps.
------------------------------
Bruce Liu
Senior Product Consultant, Professional Services
Blue Prism
Australia/Sydney
------------------------------
Please remove GO from the statement. It is not needed for a single statement procedure.
I have tested this in my environment and you will likely encounter an issue where Blue Prism complains about "Can't convert System.Guid to a Blue Prism data type.". In this case, I would advise you to use the SELECT query behind the stored procedure dbo.sp_help_jobactivity to do what you need. The trick is to convert the UNIQUEIDENTIFIER column job_id to CHAR(36). The query may look like below, note the highlighted sections:
SELECT ja.session_id, CONVERT(CHAR(36),ja.job_id) AS job_id,j.name AS job_name,ja.run_requested_date,ja.run_requested_source,ja.queued_date,ja.start_execution_date,ja.last_executed_step_id,ja.last_executed_step_date,ja.stop_execution_date,ja.next_scheduled_run_date,ja.job_history_id,jh.message,jh.run_status,jh.operator_id_emailed,jh.operator_id_netsent,jh.operator_id_paged FROM(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id=jh.instance_id) join msdb.dbo.sysjobs_view j on ja.job_id=j.job_id WHERE j.name='job name'
Note you will need to execute the query using an account with sufficient permission to msdb database.
In my testing, I am able to obtain a collection like below:
Hope this helps.
------------------------------
Bruce Liu
Senior Product Consultant, Professional Services
Blue Prism
Australia/Sydney
------------------------------
Hi Jamuna,
Please remove GO from the statement. It is not needed for a single statement procedure.
I have tested this in my environment and you will likely encounter an issue where Blue Prism complains about "Can't convert System.Guid to a Blue Prism data type.". In this case, I would advise you to use the SELECT query behind the stored procedure dbo.sp_help_jobactivity to do what you need. The trick is to convert the UNIQUEIDENTIFIER column job_id to CHAR(36). The query may look like below, note the highlighted sections:
SELECT ja.session_id, CONVERT(CHAR(36),ja.job_id) AS job_id,j.name AS job_name,ja.run_requested_date,ja.run_requested_source,ja.queued_date,ja.start_execution_date,ja.last_executed_step_id,ja.last_executed_step_date,ja.stop_execution_date,ja.next_scheduled_run_date,ja.job_history_id,jh.message,jh.run_status,jh.operator_id_emailed,jh.operator_id_netsent,jh.operator_id_paged FROM(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id=jh.instance_id) join msdb.dbo.sysjobs_view j on ja.job_id=j.job_id WHERE j.name='job name'
Note you will need to execute the query using an account with sufficient permission to msdb database.
In my testing, I am able to obtain a collection like below:
Hope this helps.
------------------------------
Bruce Liu
Senior Product Consultant, Professional Services
Blue Prism
Australia/Sydney
------------------------------
Please remove GO from the statement. It is not needed for a single statement procedure.
I have tested this in my environment and you will likely encounter an issue where Blue Prism complains about "Can't convert System.Guid to a Blue Prism data type.". In this case, I would advise you to use the SELECT query behind the stored procedure dbo.sp_help_jobactivity to do what you need. The trick is to convert the UNIQUEIDENTIFIER column job_id to CHAR(36). The query may look like below, note the highlighted sections:
SELECT ja.session_id, CONVERT(CHAR(36),ja.job_id) AS job_id,j.name AS job_name,ja.run_requested_date,ja.run_requested_source,ja.queued_date,ja.start_execution_date,ja.last_executed_step_id,ja.last_executed_step_date,ja.stop_execution_date,ja.next_scheduled_run_date,ja.job_history_id,jh.message,jh.run_status,jh.operator_id_emailed,jh.operator_id_netsent,jh.operator_id_paged FROM(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id=jh.instance_id) join msdb.dbo.sysjobs_view j on ja.job_id=j.job_id WHERE j.name='job name'
Note you will need to execute the query using an account with sufficient permission to msdb database.
In my testing, I am able to obtain a collection like below:
Hope this helps.
------------------------------
Bruce Liu
Senior Product Consultant, Professional Services
Blue Prism
Australia/Sydney
------------------------------
