Historian DB Thing failed to get the Historian data

[GY_10982725]

I am using ThingWorx Platform Release 9.6

I have a service in my Historian DB Thing to get the Historian data for a PiPoint for a day period from the historian database, when I manually run this service, it always return correct data. this service contains following code: select tag, FORMAT([time],'dd-MMM-yyyy hh:mm:ss.fff tt') time, value, status from piarchive..piinterp2 where tag=[[PIPoint]] AND time >= [[st]] AND time < [[st]] + '+1d' AND time < '*' AND timestep = [[step]] Then I have another service which loop through all PiPoint and load the data for each one and save the data into a SqlServcer database. This service runs every two hours on a schedule and calls the service above to retrieve PiPoint data. The isssue is when the above serivce is called in this service, it works one or two times in a day, while other times it returns 0 rows which I confirmed using logging (there is data in the historian db). The code was never changed since it was implemented last year and it start to fail since early this year.

Here are the errors that I faced
No error message in the script log I can see.

[Rocko]

There are no errors attached.

If it stopped working and you didn't change anything in TWX, my best guess something changed on the database, either in column definition or column content/format.

[GY_10982725]

Thanks Rocko,

There should no database change in behind, as the query was wrapped in a service in historian db thing, when I manually run that query service it works and returns data, it just failed to return data when called in another service.

Cheers

[Rocko]

In that case you might be using the wrong user or connect to the wrong database.

[slangley]

Hi @GY_10982725 

If any of the responses were helpful, please mark the appropriate one as the Accepted Solution for the benefit of others in the community.

Regards.

--Sharon

[Constantine]

Hello @GY_10982725,

You use parameters like "PIPoint", "st" and "step" -- log those, too. Just to make sure you use exactly the same values when you run this service manually.

Also, a quick google search suggests that piinterp2 contains interpolated data, so the number of returned rows depends on the value change frequency. If you want to do a clean experiment, try to query picomp2 instead, which contains raw data.

/ Constantine