IMD SolutionComputing LibraryShop main store

How-to

Logging PLC values from an OPC server into SQL Server

Edge computing and IIoT Published 9 min read

Short answer

SQL Server has no OPC client, so it cannot read OFS items by itself. Put an OPC client component between the server and the database: a packaged gateway or a small application that reads tags and writes rows. Prove the PLC read through an OPC test client first, then prove one database write under the runtime account before logging production values.

On this page
  1. Why SQL Server cannot subscribe to OFS
  2. Prove the OFS read before writing any database code
  3. Choose the bridge: gateway, application, or software you already own
  4. Define when a value becomes a row
  5. Worked example: one TSX Micro temperature logged every 5 s
  6. Prove one database write under the runtime account
  7. Rehearse the failure paths before go-live
  8. Which path to take
  9. Questions asked

Your temperature values sit in TSX Micro memory, OFS exposes them as OPC items, and the rows you want end up in a SQL Server table. SQL Server cannot make that hop on its own: nothing inside the database engine speaks OPC, so neither a query nor a stored procedure can subscribe to the server. The missing piece is an OPC client that reads the items and writes rows — a packaged gateway or a small application. Build the path in this order: prove one OPC read with a test client, prove one database write under the account that will run the logger, then connect the two and rehearse the failure paths before the system logs unattended.

Why SQL Server cannot subscribe to OFS #

OFS — OPC Factory Server — is an OPC server: it publishes the controller's data as items that a client reads on request or by subscription. SQL Server is a database: it accepts rows from a connection that something else opens. No component of the database engine contains an OPC client, and a procedure that calls out to external code has not removed the missing piece — it has only relocated it into the hardest place to maintain. Keep acquisition outside the database engine.

The data path is fixed:

TSX Micro memory → OFS → OPC client/logger → database connection → SQL Server table

Your job is to choose and prove the middle of that path. Keep it read-only toward the controller: the OPC link exists to read, and anything that writes values back into PLC memory belongs to the control design, not to a logging job.

Prove the OFS read before writing any database code #

A working database connection cannot repair a wrong PLC address, so prove the read first. OFS ships with an example OPC client and an OPC server simulator, so this step needs no code:

  1. Open an OPC test client on the PC that runs OFS — the example client that ships with the server works, as does any generic OPC test browser.
  2. Browse to the temperature item and record its item identifier exactly as the server shows it.
  3. Read the item and write down value, quality and timestamp together; OPC reports all three, and the logger will need all three later.
  4. Compare the value against the same memory word read in the PLC programming tool or an existing plant display.
  5. Let the process run and confirm the value follows a real temperature change with good quality.

If the item will not read or reports bad quality, stay on this side of the path: check the controller link, the item addressing and the server diagnostics before any database work.

Confirm what the number means, too. A readable memory word often holds a raw integer, so a display showing 75.5 °C may come from a stored 755 with one implied decimal place; apply the same scaling in the logger before the insert or the table fills with values that are off by a factor of ten.

One compatibility check belongs here: classic OPC and OPC UA are different protocols that share only a name, so a UA-only client will not connect to a classic server. Confirm the interface your OFS version exposes — its own manual documents that — before you pick a client library.

Choose the bridge: gateway, application, or software you already own #

Three bridges work, and the choice decides who maintains the job.

A packaged gateway — an OPC-to-database logger such as OPC Router — subscribes to the items and writes to a table you define, driven by periodic or event triggers, with no code to own. Before you commit, match it to your installed OFS interface and SQL Server version, and get its buffering and restart behavior in writing.

A small application of your own acts as the OPC client and writes through ODBC or the SQL Server provider. Pick a client library that matches the OFS interface, use parameterized commands, and choose a language your maintenance team can support. If your team already runs small scripts on the plant floor, the pattern in running Python and Node-RED beside PLC code on an edge PC carries straight over to this logging service.

Check what you already own before deploying anything: SCADA and HMI packages often include both an OPC client role and a database writer, and one documented pattern is a WinCC script that reads tag values and writes them to a SQL Server database. A second logger for records the first one already writes is a future dispute waiting to happen.

Wherever it runs, the logger is a service that must stay up — on the OFS machine or its own host. Classic OPC rides on DCOM, so when client and server sit on different PCs their DCOM security settings must match, and the OFS manual documents the server side. If the logger gets a dedicated machine, a fanless industrial mini PC is the typical data-collection host.

Define when a value becomes a row #

Take the logging behavior from the process requirement, not from the fastest rate the software can request. Three patterns cover the field:

  • Periodic: a row every interval, unchanged values included. A steady temperature then proves acquisition stayed alive, which change-only logging cannot show.
  • Change-driven: a row when the value moves beyond a deadband. Cheap on storage, but silence is ambiguous — it can mean a stable process or a dead link.
  • PLC-triggered: a row on an event. Catch the trigger with a persistent request or a handshake, not a poll that can miss a short pulse between reads.

Every row should carry the measurement identity, the value in engineering units, the acquisition timestamp OFS reports, and quality. Keep the acquisition timestamp separate from the database insert time: when a backlog writes minutes late, insert-time stamping makes an old reading look current.

Decide the bad-quality policy before go-live and write it into the table design. And if your collector is an HMI data log rather than a gateway, learn its behavior first — one documented failure mode is a data log writing NULL values to the database when the source value is missing.

Worked example: one TSX Micro temperature logged every 5 s #

Inputs: one temperature tag — the value your control loop works from, read from TSX Micro memory through OFS; a 5 s collection interval, illustrative and typical for a slow thermal loop; a row of tag identity (4 bytes), value (4 bytes, single precision), quality (1 byte) and millisecond timestamp (7 bytes) — 16 bytes of data, carried at 20 bytes per row to include overhead; a 365-day year.

The rule in words: rows in a day equal the seconds in a day divided by the interval, and storage for the year equals rows in 365 days times bytes per row.

Arithmetic:

86,400 s ÷ 5 s = 17,280 rows/day

17,280 ÷ 24 = 720 rows/hour

17,280 × 365 = 6,307,200 rows in 365 days

6,307,200 × 20 B = 126,144,000 B ≈ 126 MB over the full year

1 row ÷ 5 s = 0.2 rows/s sustained insert rate

Result: one temperature logged every 5 s produces 17,280 rows a day and about 126 MB over the year.

Decision: at 0.2 inserts/s the write load is trivial, so log every sample — do not add change-only logic to save space you will not miss. Spend the effort on buffering and retention instead: a 1 h database outage at this rate is 720 rows to hold, so the logger must queue across restarts and flush with its original timestamps. The same interval scales with tag count: 10 temperatures at 5 s reach 172,800 rows a day and about 1.26 GB over the year, where partitioning and a scheduled purge start to pay; a single tag stays comfortable in one simple table with a yearly purge.

Prove one database write under the runtime account #

With a proven read behind you, test the database side independently — same PC, same connection configuration, same Windows account the logger will run under:

  1. Create the destination table with types that fit: an integer or short code for the tag identity, floating point for engineering units, a small integer for quality, a millisecond timestamp column.
  2. Create a database login used only by the logger, with insert and read rights on that one table and nothing more.
  3. Write one known test row through the driver or ODBC connection the logger will use.
  4. Read the row back and compare value, timestamp and identity against what you sent.
  5. Only then point one proven OFS item at the write and watch the first real rows land.

Build inserts from parameterized commands rather than strings that concatenate formatted numbers: decimal separators, quoting and timestamp formats are where hand-built SQL breaks. If the test write fails, read the actual database error and separate connectivity from authentication, permission and conversion faults — no OPC setting will fix a permission failure.

Rehearse the failure paths before go-live #

Unattended logging fails differently from a bench test, so walk the table deliberately:

What you see Where the fault sits What to test
OFS item unreadable or bad quality PLC to OFS controller link, item addressing, server diagnostics
Client values change, no rows appear logger to database logger task state, connection string, database error text
Rows arrive with wrong values scaling or data type raw PLC value against the stored value, implied decimals
Manual test works, service run fails runtime identity repeat the write under the service account

Then pull the database out from under the live logger: stop the SQL Server service for a few minutes with logging running, restart it, and confirm the buffered rows arrive carrying their original acquisition timestamps rather than the recovery time. Repeat across a logger restart and a PC reboot, because a logger that drops its queue on restart silently thins your history exactly when the plant had something worth recording.

Which path to take #

For an OFS-to-SQL Server logging job: if periodic or event logging of a handful of values covers it, take the packaged gateway and put your time into triggers, quality policy and recovery tests instead of code. If the values feed custom validation or downstream software, build the small OPC client application — prove the read, prove the write, connect them. If an installed SCADA package already reads those tags, use its database writer and skip the second logger. If the real goal is operators watching trends rather than a table you own, plant historian selection by scale and support needs is the comparison to make first. And when the destination is a broker instead of a table, bridging OPC UA PLC data to MQTT with an edge PC applies the same prove-the-read-first order.

Questions asked

Can a SQL Server Integration Services package or a SQL Server Agent job read OPC items?

No. Integration Services, Agent jobs and stored procedures all run in or beside the database engine, and none of them contains an OPC client. They earn their place after the bridge exists: an Agent job is a good way to enforce retention, summarize old rows or purge the logging table on a schedule. The OPC read itself always happens in a separate client process.

Does this only work with SQL Server, or can the same path feed MySQL or another database?

The OPC side does not care what the database is. The same OFS read path feeds MySQL, PostgreSQL or any store for which your bridge documents a connector, because what changes is the write component, not the read. Check that the packaged gateway or client library you pick explicitly supports your database and its version before you commit to it.

What should the logger store when the PLC link fails?

Pick one policy and make it visible in the data. The workable choices are to reject bad-quality values outright, store them with an explicit quality or status column, or keep the last valid value but flag it as held and preserve its original timestamp. Never let the logger write the last good reading as if it were a fresh, valid measurement, because that is how false histories get built.

How do I keep the logging table from growing without bound?

Set a retention period and enforce it with a scheduled deletion job on the database side rather than manual trims. Size the period from your real rate: one temperature at a 5 s interval produces about 126 MB over a full year, so a single tag tolerates years of history in one table, while ten tags at the same interval cross about 1.26 GB in a year and should get a purge job from day one.

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

    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

  • Troubleshooting

    Why a migrated historian shows no PLC connection options

    A migrated historian shows no PLC connection options because the Allen-Bradley communication driver package was never installed on the new server; the OPC entries in the tree are not that driver. Install the ABCIP package that matches your historian release, confirm the OI Server accepts your license format (.xml or .loc, not .lic), and treat a closed DAServer branch that keeps collecting as an interface fault a restart clears.

    Edge computing and IIoT

Log Modbus data on a Windows PC without programming