Exporting WinCC 7.0 SP3 Alarm Logging Data to Excel via OLE DB Provider

Overview

Siemens WinCC 7.0 SP3 stores runtime alarms and events in a Microsoft SQL Server database (default SQL Server 2008 R2 with WinCC V7.0). The alarm data is exposed for read-only access through the WinCC OLE DB Provider (WinCCOLEDBProvider.1), which acts as a thin OLE DB layer on top of the segment-compressed archive tables. Engineers can query the archive directly from any OLE DB consumer — Excel (Data tab → Get Data → From Other Sources → From OLE DB), SQL Server Management Studio via a Linked Server, or VBA scripts — without requiring a licensed WinCC editor on the target machine. This reference documents the connection topology, catalog names, schema fields, and verified SQL/VBA procedures for exporting runtime alarms into an Excel workbook suitable for shift handover, regulatory reporting, or post-event analysis.

Architectural Topology

The data flow follows a classic three-tier SCADA pattern:

1. WinCC Runtime / Alarm Logging — The Alarm Logging service writes incoming messages to the SQL Server archive (MsgArchive, ActMessage) and compressed segment tables (MsgArchive$Sxx) in real time.
2. WinCC OLE DB Provider — A registered in-process OLE DB provider (WinCCOLEDBProvider.1) that resolves the runtime catalog, performs segment decompression, and exposes the logical archive table.
3. OLE DB Consumer — Excel Query, MS Query, SSMS Linked Server, Power BI, or any custom ADO application that consumes rowsets.

Catalog Naming Convention

Each WinCC project installs a pair of databases. Use the correct catalog name or the connection will return E_FAIL.

The catalog name appears in SQL Server Management Studio under Databases once WinCC has been started in Runtime at least once. To verify, open SSMS and look for a database starting with CC_.

Prerequisites

• WinCC 7.0 SP3 installed with the Alarm Logging component. The runtime must have been started at least once so the SQL archive databases exist.
• SQL Server 2008 R2 / 2012 (matches the WinCC 7.0 SP3 installation). The instance is typically a named instance .\WinCC.
• WinCC OLE DB Provider installed on the consumer machine. The provider is registered automatically by the WinCC setup; verify with regedit under HKCR\CLSID\{...}\WinCCOLEDBProvider.1.
• Microsoft Excel 2010 or higher (32-bit recommended for legacy WinCC 7.0 OPC/OLE DB stack; 64-bit works on WinCC 7.4+, but WinCC 7.0 SP3 is typically run on 32-bit Office).
• SQL permissions: The user connecting must be a member of SQLUserRole or Windows-authenticated with at least db_datareader on the runtime catalog.
• Firewall exception for SQL Server (default TCP 1433, or the dynamic port assigned to the named instance — check with SQL Server Configuration Manager).

WinCC OLE DB Provider — Technical Specifications

Step-by-Step — Configuring the OLE DB Connection in Excel

1. Open Excel and create a new workbook. Place the cursor in cell A1 of the target sheet.
2. On the Data ribbon, select From Other Sources → From OLE DB (Excel 2010) or Get Data → From Other Sources → From OLE DB (Excel 2016+).
3. In the Data Link Properties dialog, select the Provider tab and choose WinCCOLEDBProvider.1. If the provider does not appear, the WinCC runtime / OLE DB component is not installed on this machine.
4. Switch to the Connection tab and enter:
   • Data Source: .\WinCC (or the actual named instance, e.g. MYSRV\WINCC)
   • Initial Catalog: CC_<YourProjectName>_R — this is the runtime database name
   • User name: WinCCAdmin
   • Password: the password configured during WinCC installation
5. Click Test Connection. A successful response confirms that the OLE DB stack can reach the runtime database. If the test fails with error 0x80004005, verify the catalog name and that WinCC runtime has been started.
6. On the Advanced tab, optionally set Connect timeout = 30 seconds and General timeout = 0 (no limit) for large archive queries.
7. Click OK. The query wizard opens.

Alarm Archive Schema — MsgArchive Table

The archive view exposed by the OLE DB provider merges compressed segments into a single logical table named MsgArchive. The schema below is verified against WinCC V7.0 SP3.

Step-by-Step — Querying Alarms via MS Query

After the OLE DB connection is validated, Excel offers a graphical query builder. Use the following steps to retrieve a filtered alarm history:

1. From the Microsoft Query dialog, select the MsgArchive table (it appears as the only logical archive table in the catalog list).
2. Drag the following columns to the output grid: DateTime, MsgNumber, MsgClass, MsgState, MsgText, ACKNTime, ClearTime, ComputerName, UserName.
3. Apply a filter on DateTime with the operator Between and supply 2024-01-01 00:00:00 and 2024-01-02 00:00:00 for a 24-hour shift.
4. Sort by DateTime descending so the most recent alarms appear first.
5. Click Return Data to Microsoft Excel. Excel creates an external data range.
6. Right-click the data range → Properties → enable Refresh data when opening the file and Enable background refresh for periodic re-querying.

Step-by-Step — Exporting Alarms via SQL Linked Server

For power users running ad-hoc T-SQL queries from SSMS, register the WinCC OLE DB provider as a Linked Server. This pattern is documented in the Siemens WinCC V7.0 Information System and in the entry Export of WinCC / CAS Archive Data using the WinCC OLE DB Provider.

Registering the Linked Server

USE master;
GO
EXEC sp_addlinkedserver
     @server     = N'WINCC_RT',
     @srvproduct = N'',
     @provider   = N'WinCCOLEDBProvider.1',
     @datasrc    = N'.\WinCC',
     @catalog    = N'CC_MyProject_R';
GO

EXEC sp_addlinkedsrvlogin
     @rmtsrvname = N'WINCC_RT',
     @useself    = N'False',
     @locallogin = NULL,
     @rmtuser    = N'WinCCAdmin',
     @rmtpassword = N'<password>';
GO

Ad-Hoc T-SQL Query — Last 24 Hours of Alarms

SELECT
    DateTime,
    MsgNumber,
    MsgClass,
    MsgState,
    MsgText,
    ACKNTime,
    ClearTime,
    ComputerName,
    UserName
FROM OPENQUERY(WINCC_RT, '
    SELECT DateTime, MsgNumber, MsgClass, MsgState, MsgText,
           ACKNTime, ClearTime, ComputerName, UserName
    FROM MsgArchive
    WHERE DateTime >= ''2024-01-15 06:00:00.000''
      AND DateTime <  ''2024-01-16 06:00:00.000''
    ORDER BY DateTime DESC
');

OPENQUERY is mandatory: the WinCC OLE DB provider does not support distributed transaction push-down or four-part-name syntax. The query inside the string is executed by the OLE DB provider, so T-SQL functions (e.g. GETDATE()) are not available inside the OPENQUERY body. Substitute time constants from the host T-SQL.

Filtering by Message Class

Message class IDs in WinCC 7.0 SP3 follow the convention:

SELECT *
FROM OPENQUERY(WINCC_RT, '
    SELECT DateTime, MsgNumber, MsgText
    FROM MsgArchive
    WHERE MsgClass IN (1, 2)
      AND DateTime >= ''2024-01-15 00:00:00.000''
');

Excel VBA Automation — Macro for Periodic Export

To eliminate manual clicks, embed a VBA macro in the Excel workbook. The macro uses ADO to pull a fresh alarm history each time the file opens or a button is clicked. Reference the Microsoft ActiveX Data Objects 2.8 Library in the VBA editor (Tools → References).

Option Explicit

Const CATALOG_NAME As String = "CC_MyProject_R"
Const DATA_SOURCE  As String = ".\WinCC"
Const SQL_USER     As String = "WinCCAdmin"
Const SQL_PASS     As String = "ChangeMe!"

Sub RefreshAlarmExport()
    Dim conn As ADODB.Connection
    Dim rs   As ADODB.Recordset
    Dim ws   As Worksheet
    Dim tFrom As Date, tTo As Date
    Dim sql   As String

    Set ws = ThisWorkbook.Worksheets("Alarms")
    ws.Cells.Clear

    tTo = Now
    tFrom = DateAdd("d", -1, tTo)   ' last 24 h

    Set conn = New ADODB.Connection
    conn.ConnectionString = _
        "Provider=WinCCOLEDBProvider.1;" & _
        "Catalog=" & CATALOG_NAME & ";" & _
        "Data Source=" & DATA_SOURCE
    conn.Properties("User ID").Value = SQL_USER
    conn.Properties("Password").Value = SQL_PASS
    conn.Open

    sql = "SELECT DateTime, MsgNumber, MsgClass, MsgState, MsgText, " & _
          "ACKNTime, ClearTime, ComputerName, UserName " & _
          "FROM MsgArchive " & _
          "WHERE DateTime >= '" & Format(tFrom, "yyyy-mm-dd hh:nn:ss") & "' " & _
          "ORDER BY DateTime DESC"

    Set rs = New ADODB.Recordset
    rs.Open sql, conn, adOpenStatic, adLockReadOnly

    ' Header row
    ws.Range("A1:I1").Value = Array("DateTime", "MsgNumber", "MsgClass", _
        "MsgState", "MsgText", "ACKNTime", "ClearTime", "ComputerName", _
        "UserName")

    ' Copy rows
    ws.Range("A2").CopyFromRecordset rs

    rs.Close: conn.Close

    MsgBox "Imported " & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row - 1 & " alarm rows."
End Sub

Private Sub Workbook_Open()
    On Error Resume Next
    RefreshAlarmExport
End Sub

Connection-String Reference

Performance Considerations

• Time-range filters are mandatory. Unfiltered SELECTs against MsgArchive can return millions of rows; the OLE DB provider performs segment-by-segment decompression which is CPU-bound.
• Use indexed columns in the WHERE clause. DateTime and MsgNumber are indexed. Avoid LIKE '%text%' on MsgText for large ranges.
• Use a CAS / Central Archive Server for long-term archives. Direct OLE DB queries against the local runtime DB should be limited to the live window (typically 30 days). Older data should be moved to a CAS to keep runtime archives small.
• Connection pooling: ADO opens a new connection per call. For a high-frequency export, consider a persistent ADODB.Connection in a long-lived Excel Add-In.
• Excel external data ranges hold a snapshot; clicking Refresh All on the Data tab re-queries. Use Properties → Refresh every N minutes for near-real-time dashboards.

Troubleshooting Matrix

Verification Steps

1. Confirm the OLE DB test connection succeeds in the Data Link Properties dialog.
2. Run the ad-hoc SSMS query (SELECT TOP 10 * FROM MsgArchive) via OPENQUERY and verify rows return within 5 seconds.
3. Open the Excel workbook, click Refresh All, and verify the row count matches the SSMS result.
4. Trigger a test alarm in WinCC Runtime (e.g. acknowledge the message). Reload the workbook and confirm the new row appears with correct MsgState, ACKNTime, and UserName.
5. Inspect the bottom-right of the Excel status bar during refresh — a healthy import shows the row count; an error halts with a dialog.
6. Compare alarm counts against the WinCC Alarm Control on a runtime screen for the same time range to confirm parity.

Alternate Methods — Field-Proven Variations

When direct OLE DB access is unavailable (locked-down corporate desktop without WinCC components), use one of the following field-proven alternates:

Export via WinCC CSV/XLS Button

The WinCC Alarm Control supports a configurable export button (toolbar). Configure an Export Data operation targeting the format CSV (text) or XLS via the WinCC Graphics Designer. The export includes user-defined columns; a typical location is C:\WinCC\ProjectName\Export\Alarms_<timestamp>.csv. Scheduled via the WinCC scheduler, this method needs no OLE DB consumer at all.

Power BI / SSRS via OLE DB

Power BI Desktop and SQL Server Reporting Services share the same OLE DB driver stack as Excel. Register the data source once via Power Query and schedule refresh in the Power BI Service. SSRS requires a Linked Server as shown earlier, then build a report on a stored procedure that runs the OPENQUERY on demand.

PowerShell Export

PowerShell on Windows can instantiate the same COM provider via New-Object -ComObject, though a simpler field-tested method uses the System.Data.OleDb .NET provider:

$conn = New-Object System.Data.OleDb.OleDbConnection
$conn.ConnectionString = "Provider=WinCCOLEDBProvider.1;Catalog=CC_MyProject_R;Data Source=.\WinCC"
$conn.Open()
$cmd  = $conn.CreateCommand()
$cmd.CommandText = "SELECT DateTime, MsgNumber, MsgText FROM MsgArchive WHERE DateTime >= '2024-01-15 00:00:00'"
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $cmd
$dt = New-Object System.Data.DataTable
$adapter.Fill($dt) | Out-Null
$dt | Export-Csv -Path "C:\Reports\Alarms_$(Get-Date -Format yyyyMMdd).csv" -NoTypeInformation
$conn.Close()

Compliance, Audit & Archival

For FDA 21 CFR Part 11 / EU GMP Annex 11 installations, ensure the exported alarm records include the audit trail fields (UserName, ComputerName, DateTime). The OLE DB read does not alter the archive — it provides a non-destructive export suitable for permanent retention. Sign the resulting Excel workbook with a digital certificate or print to PDF/A for tamper-evident archival.

Frequently Asked Questions