Creating a Microsoft SQL Tag

After creating a tag on a Microsoft SQL device, you need to provide the SQL query that Koios will execute to read this tag's value. The create form does not include the query — set it on the tag's Configuration tab once the tag exists.

No device browser available

Unlike other protocols, Microsoft SQL tags don't have a browse function. You'll need to know the database schema and write the query yourself.

Protocol Fields

SQL Query

The SQL query that Koios executes against the database on each scan to read this tag's value.

Required for the tag to collect data
The query should return a single numeric value
Maximum length: 1,024 characters

SELECT TOP 1 temperature FROM sensor_readings
WHERE sensor_id = 'AHU-01'
ORDER BY timestamp DESC

Query Guidelines

Return a single value. Koios reads the first column of the first row of the result set. If your query returns multiple rows or columns, only the first value is used.

Keep queries lightweight. The query runs on every scan cycle, so avoid expensive joins, full table scans, or complex aggregations that could slow down the database.

Use parameterized values where possible. If you need the most recent reading, use TOP 1 with an ORDER BY on the timestamp column rather than scanning the entire table.

Query runs repeatedly at the scan rate

This query executes every time the device scans, typically every few seconds. Make sure it performs well under repeated execution. Add appropriate indexes to the source table if needed.

Query Restrictions

Only read queries are accepted. Koios rejects a query containing any of these keywords:

DELETE · CREATE · ALTER · DEFAULT · DROP · DATABASE · SET · TRUNCATE TABLE · UPDATE

A rejected query puts the tag into a configuration error — "Query contains a disallowed keyword" — and it never reads.

The match is a plain substring, not a whole word

The check looks for these letters anywhere in the query text, so an ordinary SELECT can be rejected because of a name that happens to contain one. SELECT setpoint FROM ... and SELECT * FROM offset_log both contain SET; a column called default_temp contains DEFAULT.

Work around it by aliasing the offending identifier in a database view, or by referencing a renamed column, so the keyword never appears in the query text Koios sees.

Example Queries

Latest sensor reading:

SELECT TOP 1 value FROM readings
WHERE tag_name = 'Supply_Temp'
ORDER BY timestamp DESC

Aggregated value:

SELECT AVG(temperature) FROM hourly_averages
WHERE zone = 'Warehouse-1'
AND timestamp > DATEADD(HOUR, -1, GETDATE())

Count of active alarms:

SELECT COUNT(*) FROM active_alarms
WHERE severity >= 3

After Configuration

Save the tag configuration
Test the tag: click
Test
to verify the query returns a valid value (see Testing a Tag)
Enable the tag: flip the enable switch to start collecting data

Enable the device first

Tags only collect data when their parent device is also enabled and running. Make sure the SQL device's connection credentials and database are correctly configured.

Creating an EtherNet/IP Tag Creating a REST Tag