AnsweredAssumed Answered

OLEDB linked server query returns 'Error converting data type DBTYPE_DBTIME to time'

Question asked by mnigbor on Mar 19, 2015
Latest reply on Mar 19, 2015 by dng

I'm new to the PI OLEDB world. I've installed PI OLEDB 3.3.1.2 and linked it to a SQL server but have discovered many queries that work in PI SQL Commander fail when passed through the linked server (even when using OPENQUERY). 

 

Here's one such example, this query works in PI SQL Commander:

 

SELECT * FROM piarchive..piinterp2
WHERE tag = 'sinusoid' AND time BETWEEN '-7d' AND 't'
AND timestep = '24h'

 

When used in a pass-through query the same query fails with the error 'Error converting data type DBTYPE_DBTIME to time':

 

SELECT * FROM OPENQUERY(PI, 'SELECT * FROM piarchive..piinterp2
WHERE tag = ''sinusoid'' AND time BETWEEN ''-7d'' AND ''t''
AND timestep = ''24h''')

 

What's perhaps even stranger is that changing the timestep to 23 hours fixes the query:

 

SELECT * FROM OPENQUERY(PI, 'SELECT * FROM piarchive..piinterp2
WHERE tag = ''sinusoid'' AND time BETWEEN ''-7d'' AND ''t''
AND timestep = ''23h''')

 

So, what's up with this?

Outcomes