Query AF Data with PI SYS OLEDB 4.0

Discussion created by francois_ruel on Dec 5, 2009
Latest reply on Mar 3, 2010 by francois_ruel



I just tried to query the data of an actual element using PI SYS OLEDB in PI System SQL Commander.


If I reduce the query to the simplest version, I can easily list the Elements and their attributes.  So, it connects and seem to work at this level.


If I take the example in the documentation and link that to the data tables, I get strange stuff.


If I start with the current table, I see the attributes, current time, status=0 and null value in the values columns.


The attributes are all PI Point or Formula giving a static value.


If I take the recorded table, then I'm getting this error:  [OSIsoft.AF.Asset.DataReference.PIPoint] Unable to open a session on a server. [-10758] Failed to create remote connection.


In PI System Explorer I can see the actual value in the preview of the attributes for all element.  PI connection is working.  I tried to the the PI Point to formula giving a fixed value and same thing for the current table.


There is the query for the recorded table query:

SELECT eh.Path + eh.Name + ea.Path + ea.Name Path, r.* 
FROM CascadesGPS.Asset.ElementHierarchy eh
  INNER JOIN CascadesGPS.Asset.ElementVersion ev ON ev.ID = eh.ElementVersionID
  INNER JOIN CascadesGPS.Asset.ElementAttribute ea ON ea.ElementVersionID = ev.ID
  INNER JOIN CascadesGPS.Data.Recorded r ON r.ElementAttributeID = ea.ID
WHERE eh.Path LIKE N'\%'
  AND eh.PathEffective <= N'*' /*query date*/
  AND eh.PathIneffective > N'*' /*query date*/
  AND ea.Name LIKE N'%'
  AND r.Time BETWEEN N'01-Aug-2009' AND N'02-Aug-2009'

Also, I tried the plot and interpolated table and getting error message about limiting time, and timestep.  I tried different things and always getting this message, how timestep should formated? Somtehing like TimeStep = N'00:15:00' or TimeStep = N'15m'