topic RE: How to fetch the data's from SQL Server agent jobs? in Product Forum

How to fetch the data's from SQL Server agent jobs?

JamunaT — Wed, 05 May 2021 15:19:00 GMT

Hi,

I need to fetch the data's from SQL server agent jobs, For that I have used data SQL server set connection action that's fine & then used data SQL server set collection action, In this action I need to give the SQL input to fetch the data's from SQL server agent jobs, I don't know how to give the query in this SQL input field.
Can anyone guide me please
Thanks in advance.

------------------------------
Jamuna T
RPA developer
Changepond Technology
chennai
------------------------------

RE: How to fetch the data's from SQL Server agent jobs?

Ben.Lyons1 — Thu, 06 May 2021 09:39:00 GMT

Please see my response on your other thread

------------------------------
Ben Lyons
Product Consultant
Blue Prism
UK
------------------------------

RE: How to fetch the data's from SQL Server agent jobs?

bruce.liu — Fri, 07 May 2021 00:57:00 GMT

Hi Jamuna,

Paste below from my response to your question from another thread you have created:

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
------------------------------