IMD SolutionComputing LibraryShop main store

Troubleshooting

Edge trend shows no historian data: fix the doubled SELECT

Edge computing and IIoT Published 4 min read

Short answer

The trend stays blank because a complete SELECT statement was pasted into the Table field, so the gateway wraps it in its own SELECT ... FROM and SQL Server rejects the result as incorrect syntax near the keyword select. Put only Runtime.dbo.History in Table, carry DateTime, TagName and Value in the column controls, and move TagName = 'SysTimeSec' into the filter control.

On this page
  1. What the gateway log proves
  2. Why the doubled SELECT cannot parse
  3. The fix: one field, one role
  4. Worked example: rebuilding the SysTimeSec trend on Runtime.dbo.History
  5. When the SQL is clean but the trend stays blank
  6. Questions asked

The trend is blank because the Table field of its database configuration holds a complete SELECT statement. The gateway builds its own query around whatever sits in that field, so the SQL it sends reads ... FROM select DateTime, TagName, Value ..., and SQL Server rejects it with Incorrect syntax near the keyword 'select' before one row is read. The same statement works in SQL Manager and Excel because those clients send your text as typed. The fix is a field-role correction: only Runtime.dbo.History belongs in Table, the columns belong in the column controls, and TagName = 'SysTimeSec' belongs in the filter control.

What the gateway log proves #

The gateway logged three things in one line: ConStr, the connection it used; Table, what you entered in the Table section's Name field; and SQL, the statement it actually sent. Comparing them settles where the fault lives:

  • The SQL line begins SELECT DateTime, Tagname, Value FROM and then repeats, word for word, everything you put in Table. The gateway prepended its own clause; nothing in your pasted text produced the outer SELECT.
  • The generated half spells the column Tagname while your text spells it TagName: two different sources wrote this one line. The server's complaint names the keyword select, not the column, so the spelling difference is evidence, not the fault.
  • The error appears beside the connection string Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=Runtime; Data Source=SRV-ENERGIE. A statement that fails parsing was still submitted over an open connection, so the login, the provider and the server name are not the problem.

Why the doubled SELECT cannot parse #

A FROM clause accepts a table name, a view name, or a query wrapped in parentheses and given an alias as a derived table. The logged statement offers none of these: after FROM comes the bare keyword select, no parentheses, no alias, so the server stops parsing at that token and raises Incorrect syntax near the keyword 'select'. That message class means the statement's shape is wrong before the engine ever looks at data, permissions or indexes.

The token the builder wanted after FROM is a three-part name: Runtime.dbo.History is database Runtime, schema dbo, table History, one identifier. That is the entire content the Table field was built to hold. A complete statement belongs only where the product documents a custom-query mode that sends your text as-is; a query-builder field is not that mode.

The fix: one field, one role #

  1. Open the trend's Database Configuration dialog and go to the section called Table.
  2. In the field named Name, delete the whole statement and enter Runtime.dbo.History.
  3. Put DateTime, TagName and Value in the column configuration, one column per role: time, tag, value.
  4. Put TagName = 'SysTimeSec' in the filter control. If the dialog has no filter control, use the documented custom-query mode or a database view, never SQL in the Name field.
  5. Apply the configuration, reopen the trend, and read the next SQL line in the gateway log: it must show FROM Runtime.dbo.History and a WHERE clause, with exactly one select in the statement.

Worked example: rebuilding the SysTimeSec trend on Runtime.dbo.History #

Inputs, each with its source:

  • Connection, copied unchanged from the gateway log: Provider=SQLOLEDB.1; Integrated Security=SSPI; Initial Catalog=Runtime; Data Source=SRV-ENERGIE. It already reached the server.
  • Table field: Runtime.dbo.History, a three-part name standing for database, schema and table.
  • Columns: DateTime, TagName, Value, as they appear in the logged query.
  • Filter: TagName = 'SysTimeSec', the one tag this trend tracks.

The rule in words: the builder assembles SELECT <columns> FROM <table> [WHERE <filter>], taking each piece from its own control and passing the table field through verbatim.

The assembly, step by step:

  • The columns control holds DateTime, TagName, Value, so the builder starts the statement SELECT DateTime, Tagname, Value FROM .
  • The table field holds Runtime.dbo.History, so the statement becomes SELECT DateTime, Tagname, Value FROM Runtime.dbo.History.
  • The filter control holds TagName = 'SysTimeSec', so the finished statement is:
SELECT DateTime, Tagname, Value
FROM Runtime.dbo.History
WHERE TagName = 'SysTimeSec'

The result carries one select keyword, a three-part name after FROM, and the tag condition in a WHERE clause.

The decision: read the next gateway log entry the same way you read the failing one. Anything after FROM that is a keyword rather than a table or view name means the field roles are still wrong, and that is what to fix next. The trend cannot plot until the logged SQL line has this shape; once it does, a still-blank trend is a data, range or mapping question, not SQL construction.

When the SQL is clean but the trend stays blank #

  • Run the exact statement from the new SQL log line in SQL Manager. Rows come back means the database side is done and the fault is in the trend's field mapping: which returned column is treated as time and which as value.
  • Check the trend's display range against the stored records. A correct query over a window with no samples for SysTimeSec returns zero rows without any error.
  • No rows for the tag in any window moves the fault upstream to collection, the layer handled in historian trend empty: PLC tag collection troubleshooting.
  • A gateway that cannot open the connection at all fails before any SQL is composed; that different fault class is covered in troubleshooting historian trend client connection failures.

Questions asked

Why does the trend flicker instead of just staying blank?

The gateway log holds the same failed statement twice in a row, which means the trend re-issues the query on its refresh cycle. Every attempt dies at parse, the display redraws with nothing, and the next retry produces the flicker. Stop the retries by fixing the statement; the flicker itself carries no extra diagnostic information.

Where is it legitimate to paste a complete SELECT statement?

Only into a mode the product documents as a custom query, where the software sends your text unchanged and maps the returned columns itself. The Table field of a query builder is the opposite: it exists so the software can write the SELECT for you. If your trend offers no custom-query mode, a filtered database view gives the builder a plain table-like source instead.

The trimmed text Runtime.dbo.History Where TagName = 'SysTimeSec' would parse in the Table field. Why not use it?

It parses only by accident, because this gateway passes the field through verbatim. The filter then lives inside a field the trend cannot see, so its column and range controls overlap text they do not control, and the next configuration edit re-breaks the query. Keep each clause in the control built for it.

Is adding a database view an acceptable substitute for a missing filter control?

Yes, when database administration allows new objects on that server. A view that applies the tag condition presents the builder a single table-like source, and the generated query wraps it cleanly. The cost is one more object to maintain and document, so name it for the tag it filters and record why it exists.

Keep reading

  • Troubleshooting

    Historian trend empty: PLC tag collection troubleshooting

    A live PLC value proves nothing about what the historian stored. Work one known, changing tag through four boundaries — controller to data server, server to collector, collector to point, point to chart — and with a 2 s update rate, a 1% deadband on a 200 in span and a 24 in/h fill, a 20 min window must show about 4 stored events. Zero events means the collector; four events with a flat chart means the display.

    Edge computing and IIoT

  • Troubleshooting

    Troubleshooting historian trend client connection failures

    A trend client connection failure has two first checks. If trend pens or Historian Client still load data, compare the failing client's connection definition against a working one instead of restarting the server. If every client fails, verify the historian and SQL services, then test TCP reachability from the failing node to the configured endpoint, port 1433 only if that is the configured port.

    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