WinCC Flexible SQL Archive: Buffered PLC Data Timestamps via GPRS

Problem Definition: Time-Shifted Archive Rows After GPRS Recovery

Remote SIMATIC stations connected over GSM/GPRS frequently lose the radio link for minutes to hours. To prevent data loss, the PLC keeps a ring buffer of process values with the original event time stamped in the PLC clock. When the GPRS link is re-established and the WinCC Flexible Runtime finally receives the buffered records, the HMI/SCADA must write them to the SQL archive using the PLC event time, not the Runtime receive time.

By default, the WinCC Flexible Data Logs feature (Configure > Historical data > Data Logs) writes the internal Runtime timestamp into the archive row. For buffered records this produces a flat block of rows all stamped with the reconnection instant, which destroys the value of the archive for trend analysis, batch reporting, and process forensics. The same problem exists when the same tag is logged through the standard Tag Logging path because the trigger comes from the Runtime, not from the PLC.

The official Siemens FAQ How are tags and alarms logged in WinCC flexible? documents the standard tag logging and alarm logging path, but it does not by itself preserve the PLC event time for records that arrived late. The remainder of this article shows how to combine PLC-side timestamping, CSV intermediate logging, and a VBScript-based insert into the SQL archive to guarantee the original timestamp survives end to end.

System Architecture and Prerequisites

Prerequisites for the procedure in this reference:

1. WinCC Flexible 2008 SP5 (or WinCC Flexible 2008 SP5 with the latest HSP) installed on the engineering station and on the Runtime PC.
2. An ODBC data source pointing at the target SQL database, configured on the Runtime PC with the same credentials used by the project.
3. A free data block on the PLC with sufficient retentive memory to hold the worst-case outage (size = expected outage hours × records per hour × record size in bytes).
4. VBScript runtime enabled in the WinCC Flexible project (Project > Runtime settings > Scripts).
5. An always-on, battery-backed PLC clock (RTC module on S7-300, integrated on S7-1200/1500) synchronized via NTP or via the WinCC Flexible time synchronization job.

WinCC Flexible Data Logging Model

WinCC Flexible offers three independent logging paths that all hit the same archive database:

For the buffered-replay use case, the third path (Data Logs triggered from VBScript) is the only one that lets the application push an explicit Date_And_Time into the archive row. The first two paths are convenient for live acquisition but cannot preserve the PLC event time of records that arrive late.

Timestamp Semantics: PLC Clock vs. Runtime Clock

Each archive row has, conceptually, three candidate timestamps:

1. PLC event time — the moment the value was sampled in the PLC. This is the only correct choice for the buffered case.
2. PLC transmission time — the moment the record was added to the GPRS send queue. Useful for diagnostics of the link, not for the process record.
3. Runtime receive time — the moment the WinCC Flexible Runtime read the tag from the PLC after the link came back. This is what the standard logging paths stamp by default.

To keep the PLC event time, encode it inside the payload itself. A 64-bit DTL (DATE_AND_TIME long, 8 bytes: year, month, day, hour, minute, second, nanoseconds) or a 56-bit legacy Date_And_Time is read from the PLC into a string tag and concatenated with the process value. The HMI never re-interprets the timestamp; it just copies the string into the archive column.

PLC-Side Buffer Strategy for GPRS Outages

A robust buffer is a FIFO over a retentive DB with the following structure. Replace the addresses for S7-1200/1500 as needed.

DATA_BLOCK "dbBuffer" // S7-300/400
STRUCT
   bLocked  : BOOL;          // TRUE while a record is being written by OB1
   wHead    : INT;           // next write index
   wTail    : INT;           // next read index (consumed by HMI)
   wCount   : INT;           // number of pending records
   aRecords : ARRAY[0..999] OF STRUCT
      dtStamp : DATE_AND_TIME; // 8 bytes - original event time
      rValue  : REAL;          // 4 bytes - process value
   END_STRUCT;
END_STRUCT
BEGIN
   bLocked  := FALSE;
   wHead    := 0;
   wTail    := 0;
   wCount   := 0;
END_DATA_BLOCK

The OB35 cyclic interrupt (or a process-event OB) writes one record per scan. The HMI reads records by addressing dbBuffer.aRecords[dbBuffer.wTail] and then increments wTail. Because the HMI never has to reach the PLC during the outage, the ring buffer survives any radio loss up to its depth (1000 records in the example above).

CSV Intermediate Log Strategy

Even when the SQL link is up, it is good practice to write every record first to a CSV file on the Runtime station, then to the SQL table. The CSV carries the PLC stamp as a string column and the SQL insert copies it verbatim. The original Siemens FAQ 26283062 documents the CSV log file mechanism in WinCC Flexible and confirms that a CSV log can be used as a source in a trend view.

CSV layout (one record per line):

PLC_STAMP;TAGNAME;VALUE;QUALITY
2024-08-12T07:14:22.123456Z;LEVEL_TANK_1;3.472;GOOD
2024-08-12T07:14:23.123456Z;LEVEL_TANK_1;3.474;GOOD

The timestamp is built from the DTL value the PLC published. Conversion in the HMI is done with a small VBScript that calls DateSerial / TimeSerial on the numeric DTL fields rather than on Now(), which would otherwise re-stamp the record.

Step-by-Step: Configuring the Archive Path

1. Define the Data Log. In the WinCC Flexible project tree open Historical data > Data Logs, add a log called logBufferedArchive with columns PLC_STAMP (String, 32), TAG (String, 32), VALUE (Real), QUALITY (String, 8). Configure storage location as the local CSV directory, e.g. C:\Archive\logBufferedArchive.csv, with a circular size of 200 MB.
2. Wire a Data Log function. In the screen or in a global scheduler, call the system function LogTag (or ArchiveLogTag) with the explicit timestamp built from the PLC DTL.
3. Configure the ODBC connection. On the Runtime PC, open the ODBC Data Source Administrator and create a System DSN pointing at the SQL Server instance. Match the driver bitness to the WinCC Flexible Runtime (32-bit Runtime → 32-bit ODBC).
4. Create the target table. Use the schema below; the primary key is the combination of the PLC stamp and the tag name to allow duplicates across different stations.

CREATE TABLE dbo.BufferedArchive (
   Id          BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
   PLC_STAMP   DATETIME2(6)    NOT NULL,
   TAGNAME     NVARCHAR(64)    NOT NULL,
   VAL         FLOAT           NOT NULL,
   QUALITY     NVARCHAR(8)     NOT NULL,
   SourcePLC   NVARCHAR(32)    NOT NULL,
   CONSTRAINT UX_Stamp_Tag UNIQUE (PLC_STAMP, TAGNAME)
);

5. Enable VBScript in the project. Project > Runtime settings > Scripts → tick Enable VBScript. Save and re-transfer.
6. Insert the inserter script from the next section into a global module.
7. Schedule a flush job that calls the inserter every 60 s while the GPRS link is up, plus a trigger from the connection-watching tag that flushes immediately after a reconnect event.

VBScript for Lossless SQL Insert

The script reads the CSV produced by the Data Log, parses each line, and inserts a row into the SQL table using the explicit PLC stamp. It never uses Now() or any Runtime-internal clock as the archive timestamp.

' --- Module: modArchiveInserter ---
Option Explicit

Sub InsertBufferedCSV(sCsvPath As String, sConn As String)
   Dim oFSO, oFile, sLine, aParts
   Dim sSql, oConn, oRS
   Set oFSO = CreateObject("Scripting.FileSystemObject")
   If Not oFSO.FileExists(sCsvPath) Then Exit Sub
   Set oFile = oFSO.OpenTextFile(sCsvPath, 1)  ' ForReading
   Set oConn = CreateObject("ADODB.Connection")
   oConn.Open sConn
   Do While Not oFile.AtEndOfStream
      sLine = oFile.ReadLine
      If Left(sLine, 9) = "PLC_STAMP" Then GoTo NextLine  ' skip header
      aParts = Split(sLine, ";")
      If UBound(aParts) < 3 Then GoTo NextLine
      ' aParts(0) = PLC_STAMP (ISO 8601), (1)=TAG, (2)=VAL, (3)=QUALITY
      sSql = "INSERT INTO BufferedArchive (PLC_STAMP, TAGNAME, VAL, QUALITY, SourcePLC) " & _
             "VALUES (?, ?, ?, ?, ?)"
      Set oRS = CreateObject("ADODB.Recordset")
      oConn.Execute sSql, , , aParts(0), aParts(1), CDbl(aParts(2)), aParts(3), "ST_01"
NextLine:
   Loop
   oFile.Close
   oConn.Close
   Set oConn = Nothing
End Sub

' --- Trigger on GPRS reconnect ---
Sub OnLinkRestored(item)
   If item.Value = 1 Then
      Call InsertBufferedCSV("C:\Archive\logBufferedArchive.csv", _
         "Provider=SQLNCLI11;Server=SCADA01;Database=Process;Trusted_Connection=Yes;")
   End If
End Sub

Trend View Reading the Archived CSV

As documented in WinCC Flexible FAQ 26283062, a CSV log file can be configured as a data source for the trend view. Configure Trends > Trend view > Data source > Log and point it at the same logBufferedArchive.csv. The trend view will then render the buffered records at the correct time on the X axis, even if the SQL insert is still queued.

Verification and Commissioning Checks

1. PLC clock check. Read SFC 1 / SFC 0 (or the S7-1500 WR_SYS_T) on the HMI and confirm the seconds tick. A dead RTC will produce a frozen stamp column in the archive.
2. Drift check. Compare the PLC clock to the SQL Server GETDATE() over 24 h; drift larger than 2 s invalidates the timestamp contract.
3. Outage simulation. Pull the antenna for 30 minutes, restore, and verify that all buffered rows appear in the SQL table with monotonically increasing PLC_STAMP, not with a single reconnection instant.
4. Duplicate test. Re-trigger the inserter; the UX_Stamp_Tag unique index must reject the duplicates. If duplicates land in the table, the inserter is using Now() somewhere — search and remove it.
5. Trend view test. Open the trend view while the SQL link is down; the X axis must show the buffered records at the correct historical position.

Troubleshooting Matrix

Field-Proven Edge Cases and Caveats

• Daylight saving transition. If the SQL Server is on UTC but the PLC is on local time, the archive will show a one-hour shift twice a year. Store all stamps in UTC on the PLC (most S7-1500 / S7-1200 firmwares can be set to UTC) and convert at the reporting layer.
• Modem renumbering. GPRS often hands out a new IP address; the WinCC Flexible connection should use the device name, not the IP, to avoid the Runtime reporting a fault on every reconnect.
• Battery-backed RTC failure. A dead RTC will show the PLC_STAMP as 1990-01-01 or freeze at a random date. Treat any record older than 20 years as a hardware alarm.
• SQL row backpressure. If the SQL insert is slow, the inserter loop will block the VBScript scheduler. Run the inserter in a separate scheduled task on the Runtime PC, not inline with the HMI screen cycle.
• GDPR / data retention. Buffered archives typically contain process values only, but if user identifiers leak in, apply the same retention policy as the rest of the SCADA tier.
• Multi-station aggregation. The SourcePLC column lets the same table hold records from many remote stations; index it before going to production.

Frequently Asked Questions