IMD SolutionComputing LibraryShop main store

Troubleshooting

Why Citect alarm logging never reaches SQL Server

Edge computing and IIoT Published 7 min read

Short answer

Alarms that populate the runtime page but never reach the SQL table mean the display path works and the logging path does not. Test three links in order: the alarm category's Log Device binding, the ODBC DSN and device connection including DSN bitness, then the INSERT format string. Read the syslog first and trace with SQL Profiler second — no traced statement puts the fault on the SCADA side, a failing statement puts it in the table or format.

On this page
  1. Why the alarm page works while the table stays empty
  2. The three links, in the order to test them
  3. The syslog check on the alarm server
  4. The SQL Profiler trace that splits the fault
  5. Fix the category-to-device binding
  6. Fix the DSN and the device connection
  7. Fix the INSERT against the table
  8. Worked example: one alarm on Plant SCADA 2023 R2, from trigger to first row
  9. Confirm the rows and survive a restart
  10. Start from the Example project
  11. Questions asked

Alarms that populate the runtime alarm page but never appear in the SQL Server table are not an alarm problem. The display path — alarm detection, categories and the in-memory alarm server — is working; the logging path is not. Only the log device named in the alarm category's Log Device field writes rows, so the fault sits in one of three links: the category-to-device binding, the ODBC DSN and device connection, or the INSERT format string. Test them in that order, reading the syslog first and a SQL Profiler trace second. No traced statement proves the fault is on the SCADA side; a statement that arrives and fails proves the fault is in the table schema or the format string.

Why the alarm page works while the table stays empty #

The runtime alarm, summary and SOE pages are served from the alarm server in memory. Whether an alarm appears on those pages is a separate setting from where it is logged, and alarm log devices can be SQL databases, dbf files or text files. The SQL table is fed only by the device named in the category's Log Device field, so a working alarm page beside an empty table simply means two independent paths with one broken. The same symptom on Citect SCADA 2018 and AVEVA Plant SCADA 2023 R2 points at configuration or connectivity, not a version defect. This ODBC log-device path is also separate from the connector that feeds Citect Historian; if that connector is your route to history as well, start with a Citect SCADA connector to Historian that shows disconnected.

  1. Category to device. Configured in the alarm categories editor, in the Log Device field. Failure signature: no SQL traffic at all and no syslog error. A blank or mismatched field produces exactly the reported symptom — alarms on screen, nothing sent.
  2. Device to database. The SQL device added in Plant SCADA Studio, its connection string and the ODBC DSN it names. Failure signature: ODBC, driver or device errors in the syslog, and no session reaches SQL Server so the trace stays empty.
  3. Format string to INSERT. The device's format string and INSERT command measured against the table definition. Failure signature: the statement is visible in a trace and fails, with SQL Server naming the column, type or conversion it rejected.

Test them in this order because each link silently blocks everything downstream of it.

The syslog check on the alarm server #

Open the syslog on the machine running the alarm server and filter it around the time of a triggered alarm. Look for ODBC, driver and device errors — a device that cannot open its connection leaves a record here. This check costs nothing, so it comes first. A clean syslog beside an empty table does not clear the SCADA side; it says the connection did not fail loudly, which moves suspicion to the binding. Do the triggering on a development project or a quiet period, because forcing a real process alarm on a running plant to test logging is not an acceptable test method.

The SQL Profiler trace that splits the fault #

Start a trace in SSMS against the target database, trigger an alarm in runtime, acknowledge it, let it return to normal, then stop the trace. It needs SQL permissions on the server, and it answers the decisive question: did any statement leave the SCADA system?

  • No INSERT in the trace: SQL Server never saw the request. The fault is the category binding or the device and DSN — links one and two.
  • An INSERT present but failing: the request arrived. The fault is the format string or the table schema — link three — and the error text names the column or conversion that failed.

Fix the category-to-device binding #

Check the Log Device field on every category used by the alarm tags, not only the first one you open. The field must contain the exact device name, and the alarm tag must belong to that category. A blank field is the classic cause of this symptom: alarms still display in runtime, but no statement is ever sent, so the table and the trace both stay empty.

Fix the DSN and the device connection #

The 32-bit and 64-bit ODBC administrators on Windows keep separate DSN lists. The DSN named in the connection string must exist in the list that matches the bitness of the SCADA process, so create it in the matching administrator and test the connection from there. The SQL login in the connection string needs INSERT permission on the target table. If the DSN points at a named instance, spell the instance name exactly — the same trap is what surfaces when Citect Historian database creation fails on a named instance.

Fix the INSERT against the table #

Give the table one column per logged field. The five fields requested here — alarm message, normal time, acknowledge time, reset time and logged-in user name — mean five columns, and the INSERT must carry five values in the same order, with types that accept the text the format string produces, including its date and time format. Records are written for alarms being on, off and acknowledged, so a state time column fills only when the category logs that state change; confirm which events each category logs before expecting all three time columns in one row.

Worked example: one alarm on Plant SCADA 2023 R2, from trigger to first row #

Inputs, each with its source:

  • The alarm displays on the runtime alarm page — the starting observation in runtime.
  • The SQL table queried in SSMS holds 0 rows after the alarm triggers, is acknowledged and returns to normal — this case's query.
  • The syslog on the alarm server, filtered to the trigger time, shows no ODBC or device error — this case's first test.
  • A SQL Profiler trace against the target database during the trigger records no INSERT from the SCADA process — this case's second test.
  • The target record has 5 fields: message, normal time, acknowledge time, reset time and user name.

Rule in words: the alarm page proves the alarm server works, and only the Log Device path writes to SQL. If the trace shows no statement, SQL Server never saw the request, so the fault sits upstream of the database.

Arithmetic: the isolation table starts with 1 text column carrying the alarm message only. The remaining fields are 5 − 1 = 4, added one column at a time, so the finished table has 1 + 4 = 5 columns and the INSERT must list 5 values.

Result: no statement in the trace, with a clean syslog, puts the fault at the binding — a failed connection would have errored in the syslog — so the items to correct are the Log Device entry, the exact device name and the tag's category membership.

Decision: with the binding corrected, rerun the trace. The first one-column row proves the device and the DSN. Add the remaining 4 columns one at a time and stop at the first INSERT SQL Server rejects — that column's type or date format is the fault. When all 5 columns land, restart the runtime and trigger once more to prove the device opens cleanly at startup.

Confirm the rows and survive a restart #

  1. Trigger an alarm, acknowledge it and let it return to normal.
  2. Query the table in SSMS ordered newest first and confirm rows for the on, off and acknowledge events.
  3. Check that the syslog shows no new device or ODBC errors during the test.
  4. Restart the runtime and repeat once, to prove the DSN and the device open cleanly at startup.

Rows that stop after a network or SQL Server restart point at the connection rather than the configuration, and the steps for reconnecting an edge PC to SQL Server after connection loss apply to this server too.

Start from the Example project #

The Example project included with Plant SCADA contains a DatabaseAccess page and the DBAccess.ci cicode file that demonstrate working SQL access, so build the device against that known-good pattern instead of from scratch. The configuration that survives startup is a dedicated SQL device on a system DSN, a minimal INSERT, and that device named in the category's Log Device field. The alarm log parameters documented for Plant SCADA control what each log record carries; check them when a field you expect in the record is missing.

Questions asked

Is this SQL log device the same path that feeds Citect Historian?

No. The ODBC SQL device writes only the table its INSERT command names, driven by the alarm category's Log Device field. The connector that feeds Citect Historian is a separate path with its own configuration and its own failure modes, so confirm which route your project actually uses before debugging either one.

Why do some rows show acknowledge or reset times and others do not?

The device only writes the state changes the category is set to log. Records are created for alarms being on, off and acknowledged, so a row carries a time only for a state that was actually logged. Confirm which events each category logs before expecting all three time columns to fill in a single row.

Can I keep an alarm record while the SQL path is down?

Yes. Alarm log devices can also be dbf files or text files. Point the category's Log Device field at a file device to keep a record while you repair the ODBC path, then switch the field back to the SQL device once a Profiler trace shows INSERT statements arriving and succeeding.

Can another application read the alarm log without querying SQL?

The CtAPI can access alarm logs and search them for specific records, so a report can read the log directly instead of depending on the SQL table. The alarm log parameters documented for Plant SCADA control what each log record carries, which is where to look when an expected field is missing.

What if the syslog is clean but the trace still shows nothing?

A failed connection usually leaves an ODBC or device error in the syslog, so a clean log points upstream: the Log Device field is blank or does not match the device name exactly, or the alarm tag is not in the category you checked. Verify all three, then re-create the DSN in the ODBC administrator matching the SCADA process bitness.

Keep reading

  • How-to

    Logging PLC values from an OPC server into SQL Server

    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.

    Edge computing and IIoT

  • Explainer

    Connect on-premise OPC UA and SQL servers to a cloud PI System

    Yes. Install the AVEVA adapters on-premise, on a small host next to the OPC UA and SQL servers, and point egress at PI Web API in the cloud; the PI Data Archive port is not a supported egress destination. Only the adapter-to-Web API direction needs an outbound route.

    Edge computing and IIoT

  • Explainer

    Do you need a historian or just an OPC tag server?

    No. An OPC tag server (OPC DA) supplies the current value, quality and timestamp of each item and stores nothing, so monthly figures need a historian or a SQL logger collecting from that same server. Keep a reporting client like ActiveFactory only if you also run the historian it expects.

    Edge computing and IIoT