Archiving WinCC Flexible Data to SQL Server: Configuration Guide

Overview

WinCC Flexible 2008 stores process values such as temperature, pressure, and flow in a tag logging archive. By default, the runtime writes archives to a file-based Microsoft Access-compatible database on the HMI panel's local storage. For multi-station reporting, batch traceability, or historian integration, the same archive can be redirected to a Microsoft SQL Server relational database through a 32-bit ODBC connection. The runtime then creates the archive tables, appends rows at the configured logging cycle, and exposes the data to any OLE DB or ODBC consumer — including the WinCC runtime itself, a custom VBScript, Microsoft Excel, Power BI, or a .NET application.

This reference walks through SQL Server setup, 32-bit ODBC configuration, the WinCC Flexible project wiring, archive schema details, read/write scripting, the resolution of the two most common connection errors (ODBC error 2147467259 and -2147217900), and a migration path to the TIA Portal WinCC Archive Connector for projects that have moved to WinCC Runtime Professional.

Prerequisites

• WinCC Flexible 2008 (any service pack) installed on a Windows 7, Windows Server 2008 R2, or later engineering and runtime station. WinCC Flexible is a 32-bit application and must remain on a 32-bit or 64-bit OS with the WoW64 subsystem enabled.
• Microsoft SQL Server 2005, 2008, or 2012 installed either locally on the HMI station or on a separate server in the same domain/workgroup. Express editions are supported for small installations up to the SQL Server Express storage limit (currently 10 GB per database for SQL Server 2012 Express).
• SQL Server Management Studio for creating the target database, login, and schema.
• SQL Server authentication set to mixed mode (Windows + SQL). The WinCC runtime service account typically authenticates as a SQL login rather than a Windows account.
• The matching SQL Server Native Client ODBC driver installed on the runtime station (10.0 for SQL Server 2008, 11.0 for SQL Server 2012). Install via the SQL Server installation media or download from the Microsoft Download Center.
• 32-bit ODBC Data Source Administrator accessible at %windir%\SysWOW64\odbcad32.exe on 64-bit Windows. This is the only ODBC administration tool that WinCC Flexible 2008 will read.
• For projects migrating to TIA Portal: WinCC Runtime Professional V14 or later, plus the current WinCC Archive Connector documentation set.

SQL Server and Database Configuration

1. Open SQL Server Management Studio and connect to the instance that will hold the archive. Right-click Databases → New Database and create a database with a meaningful name such as WinCC_Archive.
2. Set the recovery model to SIMPLE in Database Properties → Options. Full recovery on a continuously written archive database causes uncontrolled transaction-log growth and degrades commit latency.
3. Set Auto Close to OFF. Auto Close shuts down the database after the last connection exits, which forces a 30-60 second re-initialization on every WinCC Flexible runtime start.
4. Right-click Security → Logins → New Login. Create a SQL login (e.g., wincc_user) with a strong password. On the User Mapping page, map the login to WinCC_Archive and grant the roles db_datareader, db_datawriter, and db_ddladmin. The db_ddladmin role is required because WinCC Flexible creates the archive tables on first connection.
5. Open Server Properties → Security and set Server Authentication to SQL Server and Windows Authentication mode. Restart the SQL Server service to apply.
6. Confirm TCP/IP is enabled in SQL Server Configuration Manager → Network Configuration → Protocols for MSSQLSERVER. The default instance listens on TCP 1433; named instances use a dynamic port and require the SQL Server Browser service to be running.
7. Open port 1433 (or the configured dynamic port) in Windows Firewall for the WinCC runtime subnet.

ODBC Data Source Configuration

1. On the WinCC Flexible runtime station, open the 32-bit ODBC Data Source Administrator. On 64-bit Windows: %windir%\SysWOW64\odbcad32.exe. On 32-bit Windows: %windir%\System32\odbcad32.exe.
2. Select the System DSN tab. System DSN is visible to all users and to Windows services, which is what the WinCC runtime requires. Click Add.
3. Select SQL Server Native Client 11.0 (or SQL Server if the native client is not listed) as the driver. Click Finish.
4. Enter a descriptive name (e.g., WinCC_Archive_DSN) and a short description. In the Server dropdown, select the SQL Server instance or type SERVERNAME\INSTANCENAME. Click Next.
5. Choose With SQL Server authentication using a login ID and password entered by the user. Enter the wincc_user login and password created in the previous section. Click Next.
6. Change the default database to WinCC_Archive. Leave the default settings for ANSI options unless you have a documented reason to change them. Click Next.
7. On the final page, click Test Data Source. The dialog must return Tests Completed Successfully!. If it fails, verify the SQL Server service is running, TCP 1433 is reachable, and the login credentials are correct.
8. Click OK to save the DSN. It now appears in the System DSN list with the configured driver and target database.

WinCC Flexible Project Configuration

1. Open the project in WinCC Flexible ES. In the project tree, expand Tag Logging. A default archive is created automatically when the project is generated; right-click Archive and confirm the archive exists.
2. Open the archive and add the tags to be logged (e.g., Temperature_PV, Pressure_PV). For each tag, configure:
   • Acquisition cycle: how often WinCC reads the process value from the PLC (typical: 500 ms for pressure, 1 s for temperature).
   • Archiving cycle: how often the value is written to the database. Decouple acquisition from archiving — archive at 1 s for high-speed, 5 s or 10 s for slow-changing temperatures to reduce database load.
   • Limit values: optional high/low thresholds for compression.
3. Open Project → Runtime Settings → Tag Logging. Locate the option for the archive database type. Select Relational database (ODBC) and enter the exact DSN name from the previous section (WinCC_Archive_DSN).
4. Enter the SQL login credentials (wincc_user and password) in the runtime settings dialog, or configure them in a WinCC user administration entry and reference it from the script.
5. Compile the project and transfer it to the HMI runtime. On the first start of runtime, WinCC Flexible executes CREATE TABLE statements to create the archive tables (default name ARCHIVE_1, with one row per archive entry). The runtime then appends one row per archive cycle.
6. Confirm in SQL Server Management Studio that the tables exist and contain rows with current timestamps. Run: SELECT TOP 10 * FROM WinCC_Archive.dbo.ARCHIVE_1 ORDER BY Timestamp DESC.

Database Schema Generated by WinCC Flexible

WinCC Flexible creates the archive schema on first connection. The exact column set depends on the service pack and the tag data type, but the canonical schema for an analog tag archive is:

Additional columns are generated for limit violations, comments, and trend references. Always inspect the actual schema with sp_help 'ARCHIVE_1' in SSMS before writing queries against it; the column set is project-specific.

Reading Archived Data with VBScript

The WinCC Flexible runtime supports VBScript in screen events, scheduler tasks, and global actions. Use the ADODB.Connection and ADODB.Recordset COM objects to query the SQL archive directly.

Connection String Variants

Read the Last N Records for a Single Tag

Sub ReadArchiveLastN(sTagName As String, lCount As Long)
    Dim conn, rs, sql
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=YOURSERVER;Initial Catalog=WinCC_Archive;User ID=wincc_user;Password=YourPassword;"
    conn.Open
    
    sql = "SELECT TOP " & lCount & " [Timestamp], [RealValue], [Quality] FROM ARCHIVE_1 WHERE TagName = '" & sTagName & "' ORDER BY [Timestamp] DESC"
    Set rs = CreateObject("ADODB.Recordset")
    rs.CursorType = 3  ' adOpenStatic
    rs.LockType = 1     ' adLockReadOnly
    rs.Open sql, conn
    
    Do While Not rs.EOF
        HMIRuntime.Trace "Tag=" & sTagName & " T=" & rs("Timestamp") & " V=" & rs("RealValue") & " Q=" & rs("Quality") & vbCrLf
        rs.MoveNext
    Loop
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

Read a Time-Range Aggregation

Sub ReadAggregate(sTagName As String, dtFrom As Date, dtTo As Date)
    Dim conn, rs, sql
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=YOURSERVER;Initial Catalog=WinCC_Archive;User ID=wincc_user;Password=YourPassword;"
    conn.Open
    
    sql = "SELECT MIN([RealValue]) AS MinV, MAX([RealValue]) AS MaxV, AVG([RealValue]) AS AvgV, COUNT(*) AS Cnt " & _
          "FROM ARCHIVE_1 WHERE TagName = '" & sTagName & "' AND [Timestamp] BETWEEN '" & Format(dtFrom, "yyyy-mm-dd hh:nn:ss") & "' AND '" & Format(dtTo, "yyyy-mm-dd hh:nn:ss") & "'"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql, conn
    
    If Not rs.EOF Then
        HMIRuntime.Trace "Min=" & rs("MinV") & " Max=" & rs("MaxV") & " Avg=" & rs("AvgV") & " Cnt=" & rs("Cnt") & vbCrLf
    End If
    
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub

Bind the sub to a button Press event. The output appears in the WinCC diagnosis window, which is reachable via Start → Programs → Siemens Automation → WinCC Flexible → Diagnosis or the HMIRuntime.Trace log.

Writing External Data into the WinCC Archive

External systems can append rows directly to the archive tables, but doing so bypasses WinCC's value-change detection and time-correction logic. If you must write external data, follow this pattern:

Sub WriteExternalValue(sTagName As String, vValue As Variant, dtStamp As Date)
    Dim conn, sql
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=YOURSERVER;Initial Catalog=WinCC_Archive;User ID=wincc_user;Password=YourPassword;"
    conn.Open
    
    sql = "INSERT INTO ARCHIVE_1 ([Timestamp], [TagName], [RealValue], [Quality], [Flags]) " & _
          "VALUES ('" & Format(dtStamp, "yyyy-mm-dd hh:nn:ss") & "', '" & sTagName & "', " & CDbl(vValue) & ", 64, 0)"
    conn.Execute sql, , 128  ' adExecuteNoRecords
    
    conn.Close
    Set conn = Nothing
End Sub

Set Quality to 64 (Good) for normal values, 0 (Bad) for missing data, and 128 (Uncertain) for substituted values. Avoid inserting rows with future timestamps — the runtime will reject them during the next read cycle.

WinCC OLE DB Provider

The WinCC OLE DB Provider (WinCC OLE DB Provider 1.0) is a specialized OLE DB data source installed with the WinCC runtime. It exposes both the SQL archive and the in-memory tag image table, allowing read access from VBScript without a custom connection string. Typical use cases are trend views and historian dashboards that need a stable, version-controlled interface.

Connection string: Provider=WinCCOLEDBProvider.1;Catalog=WinCC_Archive;Data Source=YOURSERVER;User ID=wincc_user;Password=YourPassword;

The provider is read-only; use the standard SQLOLEDB provider for writes. Refer to the official Siemens Support FAQ 24677043 for the configuration of the OLE DB provider in WinCC Flexible.

Troubleshooting Matrix

Error #2147467259 Deep Dive: Data Source Name Not Found

This ODBC error (IM002 in the native ODBC error code) fires when the ODBC driver manager cannot match the DSN name passed by the application to a registered system or user DSN. In a WinCC Flexible deployment, the typical chain is:

1. WinCC runtime reads the DSN name from the project settings and passes it to the ODBC driver manager via SQLConnect.
2. The driver manager enumerates the DSNs in HKLM\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\ODBC Data Sources (32-bit) and HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources (64-bit).
3. If the DSN is in the 64-bit hive only, the 32-bit process returns IM002, which WinCC surfaces as error 2147467259.

Resolution steps in order:

1. Open %windir%\SysWOW64\odbcad32.exe on 64-bit Windows. Confirm the DSN is listed under System DSN.
2. If the DSN is missing, run the SQL Server configuration wizard and add the DSN, selecting the SQL Server Native Client 11.0 driver.
3. Confirm the driver listed for the DSN is present on the system. The Drivers tab shows all installed ODBC drivers. A missing entry indicates the SQL Server Native Client is not installed.
4. Verify the DSN name in the WinCC project matches the registered DSN exactly. Windows is case-insensitive, but the names must match character-for-character.
5. Restart the WinCC Flexible runtime service after any DSN change. The DSN cache is populated at service start.

Error #-2147217900 Deep Dive: Incorrect Syntax Near '('

The negative value -2147217900 is the signed 32-bit representation of HRESULT 0x80040E14, which maps to native ODBC error 37000 (syntax error). The error fires from the SQL Server parser, not from ODBC, which means the query was successfully delivered to SQL Server but rejected during parsing.

Common triggers in WinCC Flexible VBScript:

• Reserved keyword used unbracketed — Timestamp, Value, User, Table, Order are reserved in T-SQL. Wrap in [ ]: [Timestamp], [User].
• Parenthesized expression in WHERE broken across lines without continuation. The WinCC script editor does not always handle line-continuation characters consistently.
• TOP with PERCENT on drivers that do not support SELECT TOP n PERCENT. Replace with SELECT TOP (CAST(n AS FLOAT)/COUNT(*)*100) PERCENT subquery, or simplify to SELECT TOP n.
• String concatenation that produces trailing whitespace, then a stray opening parenthesis at the end of the query string.
• Use of JOIN syntax targeting a version that requires the older WHERE-style join.

Resolution pattern:

1. Add HMIRuntime.Trace "SQL=" & sql & vbCrLf immediately before rs.Open to capture the final query string.
2. Open SQL Server Management Studio, paste the logged string into a new query window, and execute it. SSMS will report the exact parser error location.
3. Rewrite the query in a version-safe form, e.g., always use explicit JOIN ... ON syntax, always bracket reserved words, and use SELECT TOP n without PERCENT.
4. Re-run from WinCC. The error should be gone if the SSMS test passes.

WinCC RT Professional: Archive Connector

For projects that have migrated from WinCC Flexible to WinCC Runtime Professional in TIA Portal, the WinCC Archive Connector provides a more modern SQL Server integration. The Archive Connector re-connects already swapped-out WinCC archives to a SQL Server, decoupling the storage backend from the runtime and supporting parallel read access from multiple clients (DataMonitor, WinCC OLE DB Provider, custom .NET applications).

Key differences from the WinCC Flexible ODBC approach:

For new TIA Portal deployments, prefer the Archive Connector. For legacy WinCC Flexible systems that cannot be migrated, the ODBC + VBScript pattern described above remains the supported approach. See the TIA Portal V20 Archive Connector documentation for the current configuration procedure.

Performance and Sizing

SQL Server sizing for a WinCC Flexible archive depends on the number of tags, the archive cycle, the retention period, and the query pattern. Use the following rules of thumb for an Express-class deployment:

• Row size for an analog tag archive: approximately 80 bytes (ID + Timestamp + TagName + RealValue + Quality + Flags + User + row overhead).
• Disk usage per tag per day = (86,400 seconds / archive cycle) × 80 bytes. For a 1-second cycle: 6.9 MB/day per tag. For a 5-second cycle: 1.4 MB/day per tag.
• SQL Server 2012 Express caps the database at 10 GB. With 100 tags at a 5-second cycle, that is roughly 70 days of retention — plan rotation or migration to a licensed SQL Server for longer retention.
• Index the archive table on (TagName, Timestamp) to accelerate time-range queries. WinCC Flexible does not create this index automatically. Add it once with SSMS: CREATE INDEX IX_ARCHIVE_1_Tag_Time ON ARCHIVE_1 (TagName, Timestamp);
• Partition the archive table by month if retention exceeds 90 days, to keep index rebuilds and statistics updates within a maintenance window.

Security and Operational Best Practices

• Use a dedicated SQL login for the WinCC connection. Do not reuse sa. Grant only db_datareader, db_datawriter, and db_ddladmin on the archive database, nothing on the master database.
• Store the SQL password in a WinCC user administration entry with a permission level of at least "Operator" and reference the entry from the script via HMIRuntime.Tags("@Password"). This avoids plaintext passwords in exported projects.
• Restrict the DSN password visibility by marking the ODBC DSN as not saving the password at the Windows user level. The system DSN will then require the password to be supplied by the connecting application on every connection.
• Enable TLS 1.2 on the SQL Server and configure the SQL Server Native Client 11.0 to require encrypted connections. This is mandatory for compliance with most modern cybersecurity standards.
• Schedule a daily DBCC SHRINKFILE on the transaction log only if you have monitored growth and confirmed a backlog of freed space. Shrinking the data file fragments the disk and degrades archive write throughput.
• Back up the archive database nightly using SQL Server native backup or a third-party tool. Test the restore procedure quarterly. WinCC Flexible does not provide an internal backup of the archive database.

Verification Procedure

1. Start the WinCC Flexible runtime on the HMI station. Confirm the runtime status indicator turns green and no alarms fire within the first 60 seconds.
2. Open SQL Server Management Studio on the database server. Run SELECT TOP 5 * FROM WinCC_Archive.dbo.ARCHIVE_1 ORDER BY Timestamp DESC. Confirm recent rows are present with the expected tag names and quality codes.
3. Trigger the VBScript read sub from an HMI button. Confirm the diagnosis window lists the expected number of records with non-zero RealValue for the active tags.
4. Trigger the time-range aggregate sub. Confirm the Min, Max, and Avg values match the actual process readings within engineering tolerance.
5. Open Windows Event Viewer on the runtime station. Filter for ODBC and SQL Server sources over the last hour. Zero error events is the expected outcome.
6. Reboot the HMI station to verify the DSN persists across restarts and the runtime re-creates its connection within the configured retry interval.

Field-Proven Caveats

• Do not point the ES and the runtime at the same SQL database. Design-time metadata writes from the ES will lock the archive tables and stall runtime writes.
• Exclude the SQL Server data and log directories from antivirus on-access scanning. Real-time scanning of .mdf and .ldf files causes 2-5 second stalls that exceed typical 1-second archive cycles and produce gaps in the archive.
• Avoid using the same DSN for read-only consumers and the WinCC runtime. Mixed-purpose DSNs pick up transient locks from reporting tools and surface them as runtime alarms. Create a separate DSN for BI tools with a read-only login.
• The minimum archive cycle in WinCC Flexible 2008 is 1 second. Sub-second logging requires the file-based archive with a custom scheduled script to forward values to SQL.
• The Timestamp column is stored in the SQL Server's local time zone, not UTC, by default. Configure the runtime explicitly to UTC for multi-site deployments, or convert at query time with AT TIME ZONE (SQL Server 2016+) or DATEADD hour offset.
• The User column is only populated for operator-initiated writes via the WinCC runtime UI. Automated script writes leave the column NULL unless the script explicitly sets it.
• Long-running VBScript queries (over 30 seconds) hold the connection open and block runtime writes. Use server-side aggregation queries and limit result sets to under 10,000 rows per call.

FAQ