SQL Collector Tile (version 24)

• Connections - number of configured sessions
• Tags - total number of tags being logged
• TVQs - total number of updates logged in last hour
• Version - versioning number of the SQL Collector service

The SQL Collector connects to a MSSQL, Oracle, or MySQL server to log data from a queue table to the Historian. For more information on setting up a connection see, How to Configure the SQL Collector.

Status Screen

The Status screen displays any configured sessions along with their properties. Individual sessions are ENABLED/DISABLED using the button in the far-right column.

• CONNECTION - name of the configured session
• CONNECTED - True or False, if the session is connected to the SQL database
• ENABLED - True or False, if the session is running
• SERVER - server name of the SQL server
• DATABASE - name of the SQL database from which the Collector will read from
• HISTORIAN - server name of where data will be logged to
• DATASET - name of DataSet where data will be stored in the Historian
• TAGS - number of tags that are being logged
• TOTAL TVQS - total number of updates that have been logged since the session started
• LAST HR TVQS -  number of updates that have been logged in the last hour
• RUNNING SINCE - date time when the session was enabled
• ERROR COUNT - number of errors that have occurred since the session was enabled

Configuration Screen

 Connections

• NEW - creates a new connection
• REMOVE - removes the selected connection

Selecting a CONNECTION NAME from the list will display its properties on the right. A session must be disabled to make configuration changes to it.

SETTINGS

• Name - name of the connection
• Historian - name of the server destination(s) where data will be sent. Multiple destinations can be separated with a comma.
• Dataset - name of the DataSet where data will be stored in the Historian. If the DataSet is prepended to the tag name within the Data Table, this can be left blank.
• Max Tags - used in conjunction with a provided Dataset. If this number of tags is exceeded, the Historian will create another DataSet and increment it by one (e.g. DataSetName, DataSetName2, DataSetName3, ...). The Dataset should be appended with an asterisk (*) in that case.

  

• Provider - MSSQL, MySQL, Oracle
• Network Protocol - (only used with the Oracle provider) protocol used to connect to the database. Default value is TCP.
• Port - (only used with the Oracle provider) port number the Collector will connect to. Default value is 1521.
• Server - server name of the SQL server
• Database - database name containing the Data Table Canary will read from
• Is Domain User - indicates if the provided user is a domain user
• User/Password - credentials used to connect to the SQL database
• Data Table - name of the queue table Canary will use to read from
• Key Column - column header name containing the row number the Collector will use to increment
• Tag Column - column header name containing the tag names to be logged
• Timestamp Column - column header name containing the timestamp
• UTC Timestamp - indicates the timestamps are in UTC format
• Value Column - column header name containing the tag values
• Quality Column - (optional) column header name containing the quality values. If no column name is provided, values will default to a Good quality.
• Property Columns - (optional) comma separated list of column header names containing properties to be logged with the data
• Row Limit - max number of rows the Collector can read from the Data Table in a single pass
• Query Interval - (seconds) the frequency at which the Collector checks to see if there is data to be processed from the Data Table
• Seeding Dataset - used to initially ingest historical data. When enabled, the Roll Over option within the Historian>Configuration screen for the particular DataSet should be disabled. Once the DataSet has been seeded with all historical data, this can be unchecked and the Roll Over option reenabled within the Historian.