Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 181
  • Last Modified:

build a query for having jobs status and duration : name|run_status|run_duration

Hello,

I try to build a query for having name|run_status|run_duration :

set nocount on; select distinct REPLACE(REPLACE(REPLACE(j.name,'[',''),']',''),' ', '')  +'|'+ '' + h.run_duration + '' +  '|' +  case h.run_status when 0 then 'Failed' when 1 then 'Successful' when 3 then 'Cancelled' when 4 then 'In Progress' end as JobStatus from msdb..sysjobhistory h, msdb..sysjobs j where j.job_id = h.job_id and h.run_date = (select max(hi.run_date) from msdb..sysjobhistory hi where h.job_id = hi.job_id and hi.run_date is not null) and h.run_status <> '4' and instance_id = (select max(hi.instance_id) from msdb.dbo.sysjobhistory hi where h.job_id = hi.job_id) order by 1

The following error message is returned :
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '|' to data type int.

How can I resolve it ?

Thanks
0
bibi92
Asked:
bibi92
1 Solution
 
chaauCommented:
You need to convert your h.run_duration to varchar:
select distinct 
    REPLACE(REPLACE(REPLACE(j.name,'[',''),']',''),' ', '')  +'|'+ '' + 
    CAST(h.run_duration AS VARCHAR(30))+ '' +  '|' +  
    case h.run_status when 0 then 'Failed' when 1 then 'Successful' 
          when 3 then 'Cancelled' when 4 then 'In Progress' end as JobStatus 

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now