Can't execute SQL query

[Former Member]

Hi All,

I set up the MII connection with PCo to OSISoft PI and tested the notification process, and it works perfectly. But when I am trying to execute the SQL query in SAP MII Workbench, error message is thrown, like "OLEDB Query error [com.sap.xmii.Illuminator.logging.LHException: Exception [QueryTypeNotSupportedException] - Exception of type 'SAP.Manufacturing.Connectivity.Query.QueryTypeNotSupportedException' ]. BTW, the tag query is working perfectly.

Would you please advice? Thanks a lot.

Regards,

Natallia

[Former Member]

Hi

The OSI-Pi queries to historian via ODBC are written in a different way than the regular T-SQL queries to a regular database.

Count Tags by Point Source Attribute

SELECT pointsource, COUNT(*)
FROM pipoint..classic GROUP BY pointsource

How Many Events since Beginning of This Month

SELECT COUNT(*) FROM piarchive..picomp2
WHERE tag = 'sinusoid' AND time >= BOM('*')

Get Annotated Events

SELECT time, value, annotations
FROM piarchive..picomp2
WHERE tag = 'mytag' AND time >= 't' AND annotated = TRUE

  More example queries can be found in the PI OLEDB manual, Compendium of SQL Statements.

[former_member211944]

Hi Natallia,

You could create PCo Query in MII when you have already connected the PI server to PCo.

Just create a PCo Connector pointing to the agent that is running on PI system in PCo.

Regards,

Rohit Negi

[Former Member]

Hello Rohit,

Thanks for quick response. Yes, I did create PCo Query in MII and connect the PI server to PCo, but still I get the error. Any other ideas about what is wrong?

[former_member211944]

Hi Natallia,

Can you check the logs and tell the error you are getting ?

Are you getting the error while creating the PCo data server or while running the query?

Regards,

Rohit Negi.

[Former Member]

Rohit,

I am getting my error while I am running the sql query in SAP MII. 

I checked the logs in PCo, here is what I got:

Thanks,

Natallia

[former_member196557]

Hi Natallia,

1. Which Versions of PCo and MII?

2. Can you confirm that you are able to browse Groups and Tags from the PCo Agent Instance?

3. Are you using a UDS Data Server and TagQuery template in MII, or a PCoConnector Data Server and PCoQuery template in MII?

Regards, Steve

[Former Member]

Hi Steve,

1. PCo - 2.300.1406.1651

    MII - 12.1.9 Build(103)

2. Yes, I am able to browse Tags from the PCo Agent Instance

3. I am using OLEDB Data Server and SQL Query Template in MII

I can run TagQuery in MII (UDC Data Server) but I can't run SQL Query in MII.

Best regards,

Natallia

[former_member196557]

Hi Natallia,

Can you post the exact SQL Query that you are having issues with?

Regards, Steve

[Former Member]

Thanks Steve, I figured it out.

Natallia

[agentry_src]

Hi Natallia,

Can you post your solution?  Just to satisfy Steve's curiosity?

Thanks, Mike (Moderator)