AnsweredAssumed Answered

Query against PI Linked Server in OLEDB 2016 fails

Question asked by jorgewong on Mar 6, 2017
Latest reply on Mar 7, 2017 by kduffy

I have some custom web services that call PI through some linked severs that leverage the PI OLEDB and they were working fine in 2012 but when I upgraded to PI OLEDB 2016 R2 they no longer work.  The query result just gives an unknown error.  Is there some config that needs to change or is this a bug?  Below is the error message and the query I tried to execute against the linked server.

 

Error:

OLE DB provider "PIOLEDB" for linked server "PI_PRD" returned message "Unknown error.".

Msg 7320, Level 16, State 2, Line 1

Cannot execute the query "select count('*') as StaleCount

FROM [piarchive].[pisnapshot] WHERE tag IN

(SELECT tag FROM [pipoint].[pipoint] WHERE pointsource = 'XYZ' AND location1 = 1 and scan=1)AND

((DATE('*') - time) BETWEEN RELDATE('1440h') AND RELDATE('365d'))AND tag not like '*sy.st*'  " against OLE DB provider "PIOLEDB" for linked server "PI_PRD".

 

 

Query:

Select *

FROM OPENQUERY(PI_PRD, 'select count(''*'') as StaleCount

FROM [piarchive].[pisnapshot] WHERE tag IN

(SELECT tag FROM [pipoint].[pipoint] WHERE pointsource = ''XYZ'' AND location1 = 1 and scan=1)AND

((DATE(''*'') - time) BETWEEN RELDATE(''1440h'') AND RELDATE(''365d''))AND tag not like ''*sy.st*''  ')

Outcomes