Hi PRTG Support,

We have a SQL sensor setup for monitoring one of our scheduled jobs. The sql script returns either '0' or ''1' based on the result of the job and then checks against the lookup setup for these boolean values (0 or 1).

0 = Failure 1 = Success

The script runs fine when I run it on the sql database and returns '1'. However, when I try to run it on SQLv2 (PRTG) it returns "No Valid Datatable found".

I have already read few KBs on the website and it suggested to remove the semicolons (;) from the sql script. I have already done it but no luck with it. The script is as follows:

---
DECLARE @date date=getdate(),@type varchar(50)='Upload',@Typename varchar(50)='Account',@status INT
set @status =0
 IF (SELECT COUNT(*)
 FROM
 (SELECT TS as RunTime FROM [adminlog]
    WHERE
        [Type] = @type
        AND Typename = @Typename
        AND ts > @date
        AND ( Action like 'Job started'
        OR Action  like 'Finished: Uploads finished:%'
        OR Action like 'job Completed')
        ) rows) = 3
        SET @status=1
SELECT @status
---

The sensor id is: 265648 and the result is as follows:
---------------
<prtg>
<error>1</error>
<text>No valid datatable was returned</text>
</prtg>
---------------

Could you please advise as what has to be done to fix it? Thanks.

Article Comments

Hi Leon,

Could you provide the actual query result from a SQL client?

Kind regards,
Stephan Linke, Tech Support Team


Aug, 2017 - Permalink

Hi Stephan,

The actual query from SQL client returns "1". Please let me know what additional information is required to fix it. Thanks.

Regards, Apurva


Aug, 2017 - Permalink

Hi Apurva,

Could you make the last line look like this: SELECT @status as "Status" Then it should at least work in the sensor. In order to evaluate the result, configure it like this:

Data ProcessingProcess Data Table
Select Channel Value byColumn Number
Sensor Channel #1 NameStatus
Sensor Channel #1 Column Number1
Sensor Channel #1 UnitCount

..and then create it. This cannot be changed for existing sensors, so you may need to recreate it.
You may also need a lookup. This one should do the trick: 

<?xml version="1.0" encoding="UTF-8"?>
  <ValueLookup id="oid.database.scheduled.jobs" desiredValue="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd">
    <Lookups>
      <SingleInt state="Error" value="0">
        Unknown
      </SingleInt>
      <SingleInt state="Ok" value="1">
        Good
      </SingleInt>
    </Lookups>
  </ValueLookup>

Just save it under C:\Program Files (x86)\PRTG Network Monitor\Lookups\custom as oid.db.scheduled.jobs.ovl. Navigate to Setup | System Administration | Administrative Tools and click "Go" where it says "Load Lookups". It will then be available as a lookup within the sensor.

Kind regards,
Stephan Linke, Tech Support Team


Aug, 2017 - Permalink

Hi Stephan,

The mentioned trick does not work.

It still returns "No valid datatable was returned" in PRTG sensor. I just amended the last line to read as: SELECT @status as "Status" in my sql script. The lookup was already existing.

Just one quick question, if the senor unit is set to: Sensor Channel #1 Unit: Count how will the lookup be used in this case?

Could you please advise quickly and help me fix this issue. This needs to be fixed urgently. Thanks for your support.

Regards, Apurva


Aug, 2017 - Permalink

What actual PRTG version are you using? When you open up C:\Program Files (x86)\PRTG Network Monitor\Sensor System\SQLv2\ and enter the credentials and query there, what results do you get?

Kind regards,
Stephan Linke, Tech Support Team


Aug, 2017 - Permalink