Configuring the Linked Server

1. Install and configure the ODBC Client if it is not already
2. Open Microsoft SQL Server Management Studio and right-click 'Linked Servers' to create a 'New Linked Server'.                                         
3. Name the linked server. In this example, we are using 'CANARY'.
4. Select 'Microsoft OLE DB Provider for ODBC Drivers' from the Provider drop-down list.
5. Set the Data source to 'CanaryODBCClient'.
6. Click 'OK'.

Querying the Historian

Canary offers 9 tables to query against: aggregates, assetinstances, assettypes, assettypetags, currentdata, data, tagproperties, tags, and views.

For a simple test of connectivity use the following query to return a list of all available aggregates and their description:

select * from openquery(CANARY, 'select * from aggregates')

The ODBC connector was built so that the machine pulling the data from the historian would not be overloaded with data. In most instances you may need to make a query using the 'openquery' function. This function will help define the parameters and keep the system from bogging down. 

Here is an example requesting an hourly average over a 1-day period using openquery where 'CanaryHistorian' is the machine name, 'Data Generation' is the dataset, and 'Random' is the beginning of the tag name:

select * from openquery(CANARY,
          'select * from data
          where tag_name like ''HistorianMachineName.Data Generation.Random%''
          and aggregate_interval=''01:00:00''
          and aggregate_id=''TimeAverage2''
          and time_stamp>=''9/26/2021''
          and time_stamp<=''9/26/2021''')