WinCC User Archive Filter and Write: ANSI-C and VBS Reference

Overview

Siemens WinCC User Archives (UA) provide a structured, tag-like data store inside the WinCC runtime database. They are commonly used for recipe sets, batch logs, equipment parameter lists, alarm acknowledgement text, and any tabular data the operator or upstream PLC must persist between sessions. A user archive behaves like a single table: it has typed columns, a primary key, and a row index that runtime tags can address directly.

The engineering question most often raised on UA scripting projects is the same: how do I filter the archive, count the result set, and write a row back to a variable — and should I do that in ANSI-C or in VBS? This reference documents the official WinCC UA C-function set, the SQL-backed VBS path, the contract for filtered record counts, and the single-versus-multiple-record write pattern. It also covers the most important production constraint: redundant WinCC Server pairs only synchronize UA changes that flow through the documented UA C API. Direct UPDATE/INSERT through VBS bypasses that mechanism and silently breaks high availability.

WinCC User Archive Architecture

A user archive in WinCC is a real SQL table inside the project runtime database. WinCC Professional / WinCC Runtime Professional and WinCC V7 both store UA tables in the project database; redundant configurations replicate that database to the partner server. From the scripting point of view there are two distinct access layers:

Both layers target the same rows. The crucial difference is that only the documented UA C API raises the internal change events that the WinCC Server redundancy module uses to mirror rows to the standby server. A VBS UPDATE statement issued through ODBC does not raise those events, so a redundant pair drifts the moment a script writes through the SQL path.

Siemens documents this restriction in the WinCC Information System under Options > User Archive > User Archive Functions and in the runtime help shipped with WinCC V7.x and WinCC TIA Professional.

ANSI-C UA Function Set

The WinCC UA C API is a flat function set compiled into the WinCC global C project. The function prototypes are defined in the header apdefap.h and the standard WinCC function catalog exposes them under the internal functions and standard functions folders in the C editor.

Core functions for a filter-then-write workflow:

• UAArchiveOpen(LPCTSTR archiveName) — returns a handle for the named archive.
• UAArchiveClose(HUA hArchive) — releases the handle. Always call on exit.
• UAArchiveSelect(HUA hArchive, LPCTSTR lpszWhere) — applies a SQL WHERE filter, returns the number of rows in the result set.
• UAArchiveGetFieldValue(HUA hArchive, int iRow, LPCTSTR lpszField, ...) — reads a single field at row iRow of the filter result.
• UAArchiveSetFieldValue(HUA hArchive, int iRow, LPCTSTR lpszField, ...) — writes a single field at row iRow.
• UAArchiveInsert(HUA hArchive) — appends a new row, returns its index.
• UAArchiveDelete(HUA hArchive, LPCTSTR lpszWhere) — removes matching rows.
• UAArchiveGetCount(HUA hArchive) — returns the row count of the current selection.
• UAArchiveMoveFirst / MoveNext / MovePrev / MoveLast — cursors over the selection.
• UAArchiveSort(HUA hArchive, LPCTSTR lpszOrderBy) — applies SQL ORDER BY to the selection.
• UAArchiveExport(HUA hArchive, LPCTSTR lpszFile, int iFormat) — CSV / XML export of the current selection.

Every successful call returns 0 on success. Non-zero return values are UA-specific error codes that must be checked, particularly UA_ERROR_NO_ARCHIVE, UA_ERROR_ARCHIVE_OPEN, UA_ERROR_INVALID_HANDLE, UA_ERROR_NO_DATA, UA_ERROR_INVALID_FIELD, and UA_ERROR_DB.

VBS Direct Access Path

VBS inside WinCC Graphics (faceplate scripts, button events, scheduler actions) can reach UA rows through two mechanisms:

1. Tag binding — connect a WinCC tag to CC_UA_<ArchiveName>_<FieldName>. This is a read/write link maintained by the WinCC tag manager and is the only VBS path that participates in redundancy.
2. Direct SQL — open a connection to the WinCC runtime database and execute SELECT/INSERT/UPDATE against the UA table. The Microsoft Scripting Runtime and ADODB are typically used. This path is fast and flexible but bypasses the redundancy event layer.

Microsoft ADODB example, reading the entire archive into a recordset:

Dim conn, rs, sql
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLNCLI11"
conn.Properties("Data Source").Value = ".\WinCC"
conn.Properties("Initial Catalog").Value = "CC_<ProjectName>_<HH>_RT"
conn.Properties("Integrated Security").Value = "SSPI"
conn.Open

sql = "SELECT * FROM UA#<ArchiveName> WHERE Field1 = '" & sFilter & "'"
Set rs = conn.Execute(sql)
WScript.Echo "Rows returned: " & rs.RecordCount

The UA#<ArchiveName> name is the physical table; the CC_UA_<ArchiveName>_<FieldName> tag is the WinCC-managed view. Do not mix the two for write operations in a redundant project.

Filtering Records

Filter syntax for both the UA C API and direct SQL is standard SQL WHERE. The UAArchiveSelect function applies a WHERE clause to the archive table and returns the row count of the selection. The selection remains active until the handle is closed or another UAArchiveSelect overwrites it. Filtered rows are addressed by their 1-based index inside the selection, not by their physical row number.

Examples of valid lpszWhere expressions:

"OrderNo = 'A-1001'"
"BatchID = 'B-2024-08-12-01' AND Status = 2"
"Timestamp >= '2024-08-01' AND Timestamp < '2024-09-01'"
"Value > 85.0 OR AlarmAck = 1"

The Siemens support article 10095491 — How is the data from a user archive filtered, sorted and exported in runtime? walks through the same flow with screenshots from the WinCC Information System.

Counting the Returned Records

The number of rows in the current filter selection is the gate for every single-versus-multiple decision downstream. In ANSI-C:

int iCount = UAArchiveGetCount(hArchive);
if (iCount == 0) { /* nothing matched */ }
else if (iCount == 1) { /* safe to write directly */ }
else { /* ambiguous, return error or loop */ }

In VBS over ADODB:

If rs.BOF And rs.EOF Then
  ' no rows
ElseIf rs.RecordCount = 1 Then
  ' single row, safe to write back
Else
  ' ambiguous result, do not auto-write
End If

rs.RecordCount requires a client-side cursor. Open the recordset with conn.Execute(sql) after setting conn.CursorLocation = adUseClient if you need a stable RecordCount on large selections. The default forward-only, read-only cursor only reports -1 for RecordCount in some drivers.

Writing a Single Record to a Variable

The pattern below is the canonical answer to the source question: filter, count, and on a unique match read the target field into a WinCC tag. ANSI-C implementation in a WinCC global action:

#include "apdefap.h"

void FilterAndWrite(void)
{
  HUA hArchive = UAArchiveOpen("RecipeArchive");
  if (hArchive == NULL) { printf("Archive open failed\n"); return; }

  int iCount = UAArchiveSelect(hArchive, "RecipeID = 'R-2024-001'");
  if (iCount == 1)
  {
    char szValue[64] = {0};
    if (UAArchiveGetFieldValue(hArchive, 1, "Setpoint", szValue, sizeof(szValue)) == 0)
    {
      SetTagFloat("@RecipeSetpoint", (float)atof(szValue));
    }
  }
  else if (iCount > 1)
  {
    SetTagBit("@ArchiveError", 1);
    SetTagChar("@ArchiveErrorText", "Multiple records match filter");
  }
  else
  {
    SetTagBit("@ArchiveError", 1);
    SetTagChar("@ArchiveErrorText", "No record found");
  }

  UAArchiveClose(hArchive);
}

The same pattern in VBS, bound to a button event in WinCC Graphics:

Dim conn, rs, sql, sValue
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLNCLI11"
conn.Properties("Data Source").Value = ".\WinCC"
conn.Properties("Initial Catalog").Value = "CC_MyProject_RT"
conn.Properties("Integrated Security").Value = "SSPI"
conn.Open

sql = "SELECT Setpoint FROM UA#RecipeArchive WHERE RecipeID = 'R-2024-001'"
Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 ' adUseClient
rs.Open sql, conn

If rs.RecordCount = 1 Then
  HMIRuntime.Tags("RecipeSetpoint").Write rs.Fields("Setpoint").Value
  HMIRuntime.Tags("ArchiveError").Write 0
ElseIf rs.RecordCount > 1 Then
  HMIRuntime.Tags("ArchiveError").Write 1
  HMIRuntime.Tags("ArchiveErrorText").Write "Multiple records match"
Else
  HMIRuntime.Tags("ArchiveError").Write 1
  HMIRuntime.Tags("ArchiveErrorText").Write "No record found"
End If

rs.Close
conn.Close

Note the VBS code is suitable for single-server installations. For redundant pairs, replace the SQL write with a tag write to CC_UA_RecipeArchive_Setpoint so the redundancy module mirrors the change.

Single vs Multiple Record Decision Matrix

Redundant Server Synchronization

On a WinCC Server redundant pair, the standby server receives UA changes through a built-in replication channel that monitors the documented UA API. The replication channel reads the change events raised by the C API and replays them on the partner. The channel does not see direct ODBC writes performed on the active server.

Consequences:

• A VBS script that issues UPDATE UA#RecipeArchive SET Setpoint = 12.5 WHERE RecipeID = 'R-2024-001' on Server A leaves Server B with the old value. After a failover, the operator sees stale data and any tag written from the new active server overwrites the change permanently.
• A C action that calls UAArchiveSetFieldValue(...) is replicated automatically to the partner. The standby applies the same write through its own UA API path, so the change survives failover without operator action.

Operational rule for redundant WinCC projects: all UA writes from automation scripts must go through the documented UA C API or through the CC_UA_<Archive>_<Field> tag binding. Direct SQL is reserved for read-only diagnostic tools and for one-time data migration done while redundancy is suspended.

Sort, Export, and Lifecycle Functions

Beyond filtering, the UA C API also handles ordering and externalization:

• UAArchiveSort(hArchive, "Timestamp DESC, RecipeID ASC") applies SQL ORDER BY to the current selection. Sort always runs after UAArchiveSelect.
• UAArchiveExport(hArchive, "D:\\Logs\\Recipes.csv", 1) writes the current selection to disk. Format 1 is CSV, format 2 is XML. The export runs on the active server; the file is local to that server unless a shared path is configured.
• UAArchiveGetFieldLength, UAArchiveGetFieldType, and UAArchiveGetFieldName return schema metadata, useful for generic tools that operate on any archive.
• UAArchiveSetUserChange allows a script to mark a row as modified even when the field value is unchanged. Required when downstream consumers use change detection rather than value comparison.

Error Codes Returned by the UA API

Codes are stable across WinCC V7.0 through V7.5 SP2 and across WinCC Runtime Professional V16 through V19. For environments that include older V6.x archives, consult the upgrade guide in the WinCC Information System because several codes were reassigned in the V7.0 release.

Tag Binding vs Direct API vs Direct SQL

Use the C API for ordinary read/write traffic. Reserve direct SQL for read-only diagnostic views, for one-time migrations done offline, and for engineering tools that need to inspect rows that are otherwise hidden behind tag binding.

Common Pitfalls

• Confusing row index with selection index. After UAArchiveSelect, indexes passed to UAArchiveGetFieldValue refer to the filtered result set, not the physical row. Reading row 1 after a filter always returns the first matching row, even if the physical row is 17.
• Forgetting to close the handle. A handle leak blocks subsequent UAArchiveOpen calls on the same archive for up to the WinCC transaction timeout. Always close handles on every exit path, including error returns.
• Unicode/ANSI mismatch. LPCTSTR resolves to wchar_t* on the WinCC default build. Wide-character string literals must be prefixed with L: UAArchiveSelect(hArchive, L"RecipeID = 'R-2024-001'").
• Tag initialization order. Reading a UA value into a tag during picture startup before the tag manager has finished its initial load yields a stale or default value. Defer UA reads until at least one full trigger cycle has elapsed.
• Threading. The UA C API is single-threaded per archive handle. Do not share one handle between two global actions that run in different scheduler threads; open, use, and close the handle within a single function call.
• Connection string in VBS. A backslash in the WinCC database name must be doubled inside the VBS string. The provider SQLNCLI11 requires SQL Native Client 11 or newer on the runtime machine.
• Operator-side editing. The WinCC User Archive Control (table view) does not raise UA C API events for cell edits on every runtime build. For row-level auditing, replace the control with a custom dialog that calls the C API through a global action.

Verification Steps

After implementing a filter-and-write routine, verify the following on the running project:

1. Open the WinCC diagnostics window Apdiag and confirm the UA channel shows no error entries during the test.
2. Trigger the filter routine with a known unique key and read the target tag in the tag simulator. Confirm the value matches the field value in the archive.
3. Repeat the test with a non-unique filter and confirm the ArchiveError tag is set with the text "Multiple records match". The target tag must not change.
4. Repeat the test with a non-existent key and confirm the ArchiveError tag is set with the text "No record found".
5. On a redundant pair, perform the same three tests on the active server. Force a failover (Stop WinCC on the active partner) and confirm the target tag still holds the value the last successful read produced. Direct-SQL implementations will fail this check.
6. Export the archive with UAArchiveExport from the C API and compare the row count with UAArchiveGetCount after each filter to ensure no rows are dropped or duplicated.

Performance Notes

For archives up to 10,000 rows, the documented UA C API is fast enough for one-second polling. Beyond that, switch to incremental filtering using a primary key range and UAArchiveMoveNext rather than selecting the whole table on every cycle. The C API caches the selection internally; calling UAArchiveGetFieldValue for several fields of the same row in sequence is significantly faster than re-running the filter for each field.

Direct SQL is faster for bulk loads but holds a long-lived ODBC connection during the load. On a runtime machine with limited SQL Server memory, prefer BULK INSERT through a SQL Server-side stored procedure that the C API calls, instead of looping UAArchiveInsert from a VBS script.

Cross-Reference to Manufacturer Documentation

• Siemens Support entry 10095491 — How is the data from a user archive filtered, sorted and exported in runtime? covers the filter/sort/export flow with the standard functions.
• WinCC Information System, section Options > User Archive > User Archive Functions in the help shipped with the WinCC installation media, lists the full C function reference.
• WinCC V7.5 SP2 System Manual, chapter Redundancy > User Archive Synchronization, documents the replication model and its interaction with the documented UA API.
• WinCC TIA Portal Engineering V19, manual WinCC Professional — Working with User Archives, covers the same API surface in the TIA Portal project tree.

FAQ