IMD SolutionComputing LibraryShop main store

How-to

Writing timestamped history back into a SCADA server

Edge computing and IIoT Published 7 min read

Short answer

Write timestamped results into the SCADA historic database through the vendor's .NET client API, under a dedicated account that holds the right to edit history, running as a daily scheduled task or a file-watching service. Test one ODBC insert under that account first: if the historic table still refuses writes, ODBC is read-only on your build and the API is the route.

On this page
  1. Why the write goes through the client API
  2. Test the ODBC write path first
  3. Prepare the points, the account and the time base
  4. Build the scheduled loader
  5. Worked example: how many records a load moves
  6. Backfill and verify
  7. Where each case lands
  8. Questions asked

If report results must land in the SCADA server's historic database with the times they actually occurred — a 14:00 result loaded at 20:00 recorded as 14:00 — write them through the vendor's .NET client API, under a dedicated account that holds the right to edit history, run as a daily scheduled task or a resident file-watching service. That is the only route that keeps caller-supplied timestamps, runs unattended and loops over months of backfill. Start with one test: a single-row insert over ODBC under a history-edit account. If the historic table still refuses the write, as reported on a ClearSCADA 2017 R3 server, ODBC is a read surface on that build and the API is the working path. The sequence below is the test, the preparation, the loader, the record counts and the backfill.

Why the write goes through the client API #

ODBC on these platforms is documented as a read surface. The Geo SCADA Expert guide (ClearSCADA continues under that name) describes using ODBC to access the server database's historic pseudo tables, and AVEVA's Plant SCADA documentation describes ODBC server support as letting third-party applications access data. An INSERT against the historic table — CDBHistoric on a ClearSCADA server — that comes back with a message that the write is not permitted, even when the client is logged in as a user with history-edit rights, means the table is read-only to ODBC on that server and version. Stop adjusting permissions at that point.

The route the vendor documents for writes is different. The Geo SCADA Expert server administration guide describes importing historic data into point and accumulator histories through a set of Historic aggregate methods, and the vendor's public utilities-and-examples repository ships an InsertHistoricData sample that inserts into a historic stream through the .NET API, with the caller supplying value, timestamp and quality.

The direction of the data matters too: backfilling trend history from a plant historian and backfilling PI from an existing plant historian move existing records between historian systems, while this job writes calculated results into the SCADA server's own historic store, so it depends on the server's own write interfaces.

Route Original timestamp kept Unattended Months of backfill Decision
ODBC INSERT into the historic table Would, if permitted Yes Yes Closed when the history-edit test still refuses the write
.NET client API historic insert Yes, caller supplies the time Yes Yes Use this
Vendor web services Only if the exposed operation takes a caller time Yes Same check applies Fallback for a non-.NET loader, verified on your version first
Driver import into separate points No, arrival time unless the driver mapping supplies one Yes Poor fit Only when new point tags are acceptable downstream
Manual import tool Yes No, needs an operator Reported limit of roughly two weeks of data age Not for an unattended job
Write to live points No, time of write Yes No Fails the requirement

Test the ODBC write path first #

  1. In the server's user and security configuration, create a test account and grant it the right that permits editing historic data. Keep it separate from operator and reader logins.
  2. Configure an ODBC data source on a client machine, connect as that account and run a plain SELECT against the historic table. Rows coming back prove the read side before you judge the write side.
  3. Run a single-row INSERT for one test point at one timestamp.
  4. Read the result. A success means your earlier refusal was a permissions gap, but weigh the API before building on ODBC inserts, because the API gives typed point access and per-batch status. A refusal means the historic table is read-only to ODBC on this server and version, and the API is the path.
  5. Leave the existing ODBC read queries in place. A closed write path does not affect extracting source data.

Prepare the points, the account and the time base #

  • Destination points exist before the first load: one non-field point per report metric, named to match the tags the downstream system expects, with historic recording enabled so inserted records are retained and visible to later ODBC queries.
  • The loader account is dedicated, with the history-edit right and access scoped to those destination points. A history-edit right lets its holder rewrite records that reports and head-office feeds depend on, so treat it as a privileged service account, not a shared login.
  • The time base is decided once: the insert call's documentation states which time base it expects; treat it as UTC unless stated otherwise, convert the report timestamps exactly once at the loader boundary, and prove the conversion with a test record planted either side of a daylight-saving change.

Each record carries value, timestamp and quality — the same value-time-quality triple the historian normally receives with each update — so set the quality the source data actually had instead of defaulting every record to good.

Build the scheduled loader #

  1. Take the insert call from the SDK documentation installed with your server version and from the vendor's samples: the Geo SCADA Expert utilities-and-examples repository includes InsertHistoricData, which inserts into a historic stream through the .NET API. Use the call names your version shows.
  2. Connect with the loader account, read the report output from its file, table or query, and build one record per point per timestamp.
  3. Resolve each report tag to its destination point once at startup and cache the reference; log and skip an unknown tag instead of failing the whole batch.
  4. Submit batches per point, check each batch's return status and log failures with the point name and timestamp range.
  5. Persist a watermark — the last successfully loaded timestamp per point — after each good batch, and resume from it after a restart.
  6. Register the trigger. A fixed report finish time suits a scheduled task with a daily 06:00 trigger set to run whether or not a user is logged on. A variable finish time suits a resident service that watches the report file's timestamp and loads when it changes, because a fixed trigger reads a half-written or stale file whenever the report runs late. Both patterns call the same load routine. Host the loader on a machine that stays powered, for the same reasons given in running a compact industrial PC as an always-on server.
  7. Test duplicate behaviour on a scratch point before production: insert the same point and timestamp twice, query the result and let the answer decide whether a rerun after a crash is safe or has to be steered by the watermark.

Worked example: how many records a load moves #

Illustrative inputs: 12 report metrics at hourly resolution, batches of 200 records, and a backfill from 1 January to 30 June 2025.

  • One day holds 24 values per metric × 12 metrics = 288 records, so the daily load is 288 records. At 200 per batch, 288 ÷ 200 = 1.44, rounded up to 2 batches.
  • The backfill window is 31 + 28 + 31 + 30 + 31 + 30 = 181 days, giving 288 × 181 = 52,128 records, or 52,128 ÷ 200 = 260.64, rounded up to 261 batches.
  • A 5-minute report interval instead of hourly multiplies both totals by 12: 3,456 records per day and 3,456 × 181 = 625,536 records over the same window.

Scale these inputs to your own metric count and interval. No universal insert rate exists for these APIs, so time the one-day load and measure your records per minute before promising a completion time for a range.

Backfill and verify #

Run the backfill as a separate command-line mode of the same program over an explicit date range, so the daily job only ever loads since the watermark. Take a database backup before the first bulk load, because the run writes to history. Load one point for one day first, then verify through the existing ODBC read path: correct timestamps, values and qualities, plus a spot check either side of midnight. Scale to the full range only after that check passes, and run large ranges off-peak.

Where each case lands #

  • ODBC insert refused under a history-edit account: the historic table is read-only to ODBC on your build; build on the .NET API historic insert under a dedicated history-edit account.
  • ODBC insert succeeds: you had a permissions gap; still prefer the API loader for per-batch status and typed point access.
  • Report finishes at a fixed time: daily scheduled task. Finish time varies: resident file-watching service.
  • Months to backfill: date-range mode of the same loader, backup first, one point for one day, verify, then the full range.
  • The downstream system can read a separate SQL database instead of the historian's own tags: export there rather than writing back — logging production data from a panel PC to SQL Server covers that pattern.

Questions asked

Does the closed ODBC write path affect reading data back out?

No. The refusal applies to insert statements against the historic table only. SELECT queries through the same ODBC data source keep working, which is why verification reads the inserted records back through ODBC: the load goes in through the API and is confirmed through the read path that already existed.

Why not write the results to the live points and let the historian record them?

A live write is stamped when it arrives, not when the value applies. Push a 14:00 result at 20:00 and the historian records 20:00, which shifts every value into the wrong reporting hour and distorts daily and monthly summaries. Only records written through the historic insert path carry the caller's timestamp.

How far back can the API backfill reach?

No documented age window exists on the API route: the loader walks whatever date range you give it. The practical limits are your historic retention setting and disk space, and records older than your retention window will not survive. By contrast, the manual import tool was reported to accept only about two weeks of data age.

What makes the loader's timestamps trustworthy?

The clock on the loader host. An inserted record is stored at face value, so a drifting clock or a wrong time zone writes shifted history that no later edit fully repairs. Sync the host over NTP, convert once to the API's expected time base, and keep the daylight-saving test record as a standing check after OS updates.

Keep reading

  • How-to

    Log production data from a panel PC to SQL Server

    Run Node-RED on the panel PC, read the production values straight from the controller, and insert one row per event into SQL Server with the mssql-plus node. Keep the panel's CSV-plus-FTP file relay only as a fallback, block duplicates with a last-record check and a unique key, and expect about 86,400 rows, roughly 8.6 MB, per day at one record per second.

    Edge computing and IIoT

  • How-to

    Backfilling trend history from a plant historian

    Yes, you can backfill trend history from a plant historian into a SCADA server. Extract the stored records through the historian's native export or ODBC interface, map each source point to its destination object with original timestamps and quality, insert the batches through the destination's documented history methods, and prove the import by reading the interval back from the destination. A saved CSV is a bridge, not proof of import.

    Edge computing and IIoT

  • How-to

    Deploying Edge Data Store in Docker on a controller PC

    Run Edge Data Store as a Docker container on the controller-class edge PC and let its OPC UA adapter ingest the local server over localhost. Define egress targets, buffering and forwarding schedules in the JSON configuration, size the buffer from tag count, scan rate and outage target—50 tags at 1 event/s across an 8-hour outage is about 288 MB—and sign off only after a live pulled-cable test.

    Edge computing and IIoT