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

Posted on 2014-08-25
Last Modified: 2014-08-27

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

set nocount on; select distinct REPLACE(REPLACE(REPLACE(,'[',''),']',''),' ', '')  +'|'+ '' + 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 ?

Question by:bibi92
    1 Comment
    LVL 24

    Accepted Solution

    You need to convert your h.run_duration to varchar:
    select distinct 
        REPLACE(REPLACE(REPLACE(,'[',''),']',''),' ', '')  +'|'+ '' + 
        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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now