0 Rows Affected - Requires a value Returned

  • Buddy84

    Valued Member

    Points: 65

    Hi, I'm currently using Zabbix Monitoring and when connecting via ODBC, I'm executing the below command, but the script errors within the Zabbix Tool as if no backup jobs have failed it doesn't return a value (which we would expect).

    The issue being that Zabbix needs to have a value response, it can be anything such as ISNULL etc, but unless it does return something, then the monitoring failures.

    Any suggestions on how this script can be edited to provide a result, even if no backup jobs have failed?

    use msdb

    ;WITH CTE_MostRecentJobRun AS

    (

    -- For each job get the most recent run (this will be the one where Rnk=1)

    SELECT job_id,run_status,run_date,run_time

    ,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk

    FROM sysjobhistory

    WHERE step_id=0

    )

    SELECT

    name  AS [Job Name]

    ,CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */

    +((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */

    + (run_time - (run_time/100) * 100)  /* secs */,

    CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [Time Run]

    ,CASE WHEN enabled=1 THEN 'Enabled'

    ELSE 'Disabled'

    END [Job Status]

    FROM     CTE_MostRecentJobRun MRJR

    JOIN     sysjobs SJ

    ON       MRJR.job_id=sj.job_id

    WHERE    Rnk=1 AND name LIKE '%Backup%'

    AND      run_status=0 -- i.e. failed

    ORDER BY name

  • Phil Parkin

    SSC Guru

    Points: 243862

    Add a

    UNION ALL SELECT NULL, NULL

    maybe?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply