0

Canary ODBC Client

Forum / Questions & Answers

I'm having a problem querying Canary from ODBC (even though the plant in question has a license for this). Testing the connection in ODBC Data Sources works AND I can connect and see tables in Excel or Power BI (fyi: understood that I could also use the Excel Add-In or the Web-API for Power BI).

Based on this: https://help.canarylabs.com/hc/en-us/articles/360045029794-How-to-Write-Queries-Against-the-ODBC-Connector

I think the problem may be that I need to use "openquery". I've tried that in Excel using Microsoft Query and the Canary ODBC source in Excel but I'm not sure how if I'm structuring it right or if there is another problem. Are there some more details about how to use the ODBC connection in Excel? 

I tried to use something like the query in the link to get the values of a tag for a data range but didn't get that to work. The specific query I'm after today is to get the current values for all the "Machine1" tags that I have (which I am doing with the Excel Add-In fine but want to get working with ODBC as well). For example:

TSTCanary1.Machine1.ChangeProgram    3/7/2022 8:56:26 AM    10    Good
TSTCanary1.Machine1.CurrentUser    3/7/2022 8:56:26 AM        Good
TSTCanary1.Machine1.ErrorExist    3/7/2022 8:56:26 AM    FALSE    Good

5 replies

null
    • smason
    • 2 yrs ago
    • Reported - view

    Hi James Wise

    The openquery syntax is solely for Micrsosoft SQL if you were to create a Linked Server. More details on that here. If you're wanting to pull data into Excel via ODBC, I would use a statement like this: 

    select * from currentdata where tag_name like 'TSTCanary1.Machine1.%'

    You can drop this statement into the Advanced Options menu.

    Hope that works for you!

      • wise_james
      • 2 yrs ago
      • Reported - view

      Steve Mason Thank you but unfortunately that doesn't return any data. It doesn't give an error, just no data similar to my screenshot at the top.

    • smason
    • 2 yrs ago
    • Reported - view

    I would check two things:

    1. Is the machine name 'TSTCanary1'? I assumed it was, but if not, you would need the fully qualified tagpath including the machine name and dataset if you're pulling data from the raw historian view. If you're pulling data from a virtual view you have created, you would want the full path leading up to Machine1. The '%' acts as a wildcard and will match on anything that starts with the prepended path.
      • MachineName.DataSetName.BranchName.TagName
      • VirtualViewName.BranchName1.BranchName2.TagName
    2. Is security enabled? If the tagpath is correct, it may be a security issue that is not allowing the connection. The ODBC connector makes an anonymous connection to the Views service for pulling data.
      • wise_james
      • 2 yrs ago
      • Reported - view

      Steve Mason Thanks! I anonymized the name but I took it from the tag name that the Excel plugin gave so I think it's correct. The fact that no data is returned for any table I pick though without making a specific query makes me think it isn't that.

      When I go to Canary admin for my historian server and go to the views tile, security isn't enabled. Is that where I should look or somewhere else? I can move this to a support ticket if preferred. 

      • smason
      • 2 yrs ago
      • Reported - view

      James Wise 

      Let's move it to a support ticket. Maybe we can do a screen share and get to the bottom of it quicker.

Login to reply

Content aside

print this pagePrint this page
  • 2 yrs agoLast active
  • 5Replies
  • 423Views
  • 2 Following