Selecting SQL Database Rows in WinCC TIA V14 for S7-1500

Overview

WinCC Professional V14 (TIA Portal V14, build 14.0 / 14.1) is the Siemens HMI/SCADA runtime that supports external SQL Server queries from compiled VBScripts. When paired with a SIMATIC S7-1500 CPU on the integrated PROFINET/Industrial Ethernet connection, the runtime can pull, sort, and display rows from an existing SQL Server table, let the operator highlight a row, and forward the selected record's values to the PLC for use in the control program. Two engineering paths exist: (a) a custom VBScript using the Windows ADODB layer with an OLE DB provider against an external SQL Server, and (b) the built-in Recipe view that persists its dataset inside a SQL Server Compact (.sdf) or SQL Server database owned by the WinCC project. This reference documents path (a) for an externally owned table that cannot be migrated.

Prerequisites

• TIA Portal V14 (14.0.0.0) or V14 SP1 (14.1.0.0) with WinCC Professional V14 installed and licensed.
• WinCC Runtime Professional V14 RT license on the HMI device, or a WinCC Professional panel (Comfort series or later) with the matching RT license.
• SQL Server 2008 R2 / 2012 / 2014 on the same network; SQL Server Express 2014 is supported for read-only query loads.
• SQL Server 2012 Native Client (SQLNCLI11) or Microsoft OLE DB Driver (MSOLEDBSQL) installed on the runtime PC; SQLNCLI11 is the most documented baseline for TIA V14.
• A primary key (single-column IDENTITY preferred) on the target table to guarantee deterministic ORDER BY and stable row identification.
• Network reachability between HMI runtime PC and SQL Server on TCP/1433 (or named-instance port via SQL Browser on UDP/1434).
• Configured S7 HMI connection in TIA Portal pointing at the S7-1500 CPU, with sufficient free HMI connection resources (S7-1515-2 supports up to 16, S7-1518-4 supports up to 64).

Database Schema Requirements

The requirement is to display rows ordered by two columns (sort key A, then sort key B) and let the operator select one row whose values are then read by the S7-1500 program. For the row order to be stable across refreshes, the table must carry a single-column primary key. Without one, ORDER BY plus LIMIT/OFFSET becomes non-deterministic and the highlighted row can shift when two rows tie on the sort columns. The minimal schema for a parts catalog example looks like:

CREATE TABLE dbo.PartsCatalog (
    PartID    INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Category  NVARCHAR(64)  NOT NULL,   -- sort column A
    Name      NVARCHAR(128) NOT NULL,   -- sort column B
    Value1    REAL          NULL,
    Value2    REAL          NULL,
    Value3    REAL          NULL
);
CREATE NONCLUSTERED INDEX IX_PartsCatalog_CatName
    ON dbo.PartsCatalog (Category ASC, Name ASC);

If the existing table already uses a composite primary key that cannot be changed, add an IDENTITY column with an index and use it as the selection handle. Microsoft's T-SQL guidance for ORDER BY recommends always pairing a deterministic sort key with a tie-breaker column such as the primary key; otherwise ties are returned in plan-dependent order. See the SELECT - ORDER BY Clause (Transact-SQL) reference for the canonical pattern.

Configuring the SQL Connection from WinCC

WinCC Professional scripts use the OLE DB Automation layer (ADO) that ships with Windows. The connection string is constructed inside the VBScript; WinCC does not expose a project-level ODBC DSN editor for runtime scripts. The OLE DB provider must be installed on every runtime PC, because the WinCC runtime does not bundle SQL Server client libraries.

OLE DB Connection Strings

Server-Side Configuration Checklist

• Enable TCP/IP protocol in SQL Server Configuration Manager and restart the SQL service.
• Open inbound port 1433 (or the named-instance port) on Windows Firewall.
• Grant the runtime PC's account db_datareader on the target database; never grant db_owner to a runtime account.
• If using SQL Express, configure the SQL service account as a domain user (or LocalSystem plus SPN registration) for SSPI delegation.
• Validate connectivity from the runtime PC with sqlcmd -S DB-SRV\SQLEXPRESS -E -Q "SELECT TOP 1 1 AS ping" before commissioning the WinCC runtime.

VBScript: Querying the Sorted Rowset

The query below opens the connection, executes a parameterized SELECT ordered by Category then Name, and writes each row into a WinCC internal tag array sized to the expected maximum row count (50 in the example). The tags are then bound to a WinCC Table View or List View on the screen. The WinCC V7.3 scripting manual's "Example: Configuring a Database Connection with VBS" is the canonical reference for this technique and applies unchanged to WinCC Professional V14; see the official WinCC V7.3 Scripting: VBS, ANSI-C, VBA manual, section 1.15.3.2.

' WinCC Professional V14 - VBScript (event-driven, e.g. "Load rows" button Click)
Option Explicit

Dim conn, rs, sql
Dim i, maxRows
Const MAX_ROWS = 50

Set conn = CreateObject("ADODB.Connection")
Set rs   = CreateObject("ADODB.Recordset")

conn.ConnectionString = _
    "Provider=SQLNCLI11;" & _
    "Data Source=DB-SRV\SQLEXPRESS;" & _
    "Initial Catalog=ProductionDB;" & _
    "Integrated Security=SSPI;" & _
    "Application Name=WinCC_TIA_V14"

conn.Open

sql = "SELECT PartID, Category, Name, Value1, Value2, Value3 " & _
      "FROM dbo.PartsCatalog " & _
      "ORDER BY Category ASC, Name ASC, PartID ASC"

rs.CursorType = 3          ' adOpenStatic
rs.LockType   = 1          ' adLockReadOnly
rs.Open sql, conn

HMIRuntime.Tags("RowCount").Write 0

For i = 0 To MAX_ROWS - 1
    If rs.EOF Or rs.BOF Then
        HMIRuntime.Tags("Row_PartID_"  & i).Write 0
        HMIRuntime.Tags("Row_Category_"& i).Write ""
        HMIRuntime.Tags("Row_Name_"    & i).Write ""
        HMIRuntime.Tags("Row_Value1_"  & i).Write 0.0
        HMIRuntime.Tags("Row_Value2_"  & i).Write 0.0
        HMIRuntime.Tags("Row_Value3_"  & i).Write 0.0
    Else
        HMIRuntime.Tags("Row_PartID_"  & i).Write CLng(rs.Fields("PartID").Value)
        HMIRuntime.Tags("Row_Category_"& i).Write CStr(rs.Fields("Category").Value)
        HMIRuntime.Tags("Row_Name_"    & i).Write CStr(rs.Fields("Name").Value)
        HMIRuntime.Tags("Row_Value1_"  & i).Write CDbl(rs.Fields("Value1").Value)
        HMIRuntime.Tags("Row_Value2_"  & i).Write CDbl(rs.Fields("Value2").Value)
        HMIRuntime.Tags("Row_Value3_"  & i).Write CDbl(rs.Fields("Value3").Value)
        HMIRuntime.Tags("RowCount").Write CLng(HMIRuntime.Tags("RowCount").Read) + 1
        rs.MoveNext
    End If
Next

rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing

Displaying Rows in a WinCC Control

Three built-in controls can render the array. Selection is exposed as a numeric index in each control's event.

Bind each column of the Table View to the corresponding tag-array element using the WinCC control's ColumnAssignment property or a Dynamic dialog with index "i". The Table View's SelectedItem index is the same index used to look up the source tag array, so the VBScript that handles the click event can stay generic.

Handling Row Selection and Writing to the S7-1500

When the operator highlights a row, the selection event captures the index and copies the values into dedicated "Sel_*" tags. The S7-1500 polls those tags via the configured HMI connection (PUT/GET or symbol-absolute over the integrated connection). A simple request/ack handshake avoids race conditions on slow HMI cycles.

' Event: WinCC Table View "OnSelectionChanged"
Sub OnSelectionChanged(ByVal ItemIndex)
    Dim idx
    idx = ItemIndex
    If idx < 0 Or idx >= CLng(HMIRuntime.Tags("RowCount").Read) Then Exit Sub

    HMIRuntime.Tags("Sel_RowIndex").Write idx
    HMIRuntime.Tags("Sel_PartID").Write HMIRuntime.Tags("Row_PartID_" & idx).Read
    HMIRuntime.Tags("Sel_Category").Write HMIRuntime.Tags("Row_Category_" & idx).Read
    HMIRuntime.Tags("Sel_Name").Write HMIRuntime.Tags("Row_Name_" & idx).Read
    HMIRuntime.Tags("Sel_Value1").Write HMIRuntime.Tags("Row_Value1_" & idx).Read
    HMIRuntime.Tags("Sel_Value2").Write HMIRuntime.Tags("Row_Value2_" & idx).Read
    HMIRuntime.Tags("Sel_Value3").Write HMIRuntime.Tags("Row_Value3_" & idx).Read
    HMIRuntime.Tags("Sel_Request").Write 1   ' rising-edge pulse to S7-1500
End Sub

On the S7-1500, latch the rising edge of Sel_Request and copy Sel_PartID into a static variable of type DInt. Use a one-shot acknowledgement tag (Sel_Ack) that the HMI watches to clear the request bit:

// SCL (S7-1500, TIA V14)
IF "HMI".Sel_Request AND NOT "HMI".Sel_Ack THEN
    "DB_Selection".SelectedPartID := "HMI".Sel_PartID;
    "DB_Selection".SelectedValue1 := "HMI".Sel_Value1;
    "DB_Selection".SelectedValue2 := "HMI".Sel_Value2;
    "DB_Selection".SelectedValue3 := "HMI".Sel_Value3;
    "DB_Selection".NewSelection := TRUE;
    "HMI".Sel_Ack := 1;
END_IF;

IF NOT "HMI".Sel_Request AND "HMI".Sel_Ack THEN
    "HMI".Sel_Ack := 0;
    "DB_Selection".NewSelection := FALSE;
END_IF;

Alternative: Recipe View With Embedded SQL Backend

If the SQL table is owned by the WinCC project rather than an external application, the built-in Recipe view removes the need for any custom VBS-ADO code. Configure the recipe in TIA Portal V14 with a SQL Server database connection (or a SQL Server Compact .sdf file path), bind the recipe elements directly to PLC tags, and let the runtime manage persistence. The recipe records physically live in SQL Server, but the engineering effort drops from custom scripting plus control binding down to a configured Recipe view. Selection writes flow over the configured HMI tag connection without any ADODB code path.

Tag Array Sizing and Naming

WinCC tag names are limited to 32 characters. With the prefix "Row_PartID_" the maximum usable suffix length is 32 - 12 = 20, so 99 rows is the practical ceiling (indices 0-99). For larger datasets, shorten the prefix (e.g., "R_PID_") or use a two-dimensional scheme that encodes the row index in a separate tag and binds it to the table view through a Dynamic dialog. Real-time tag count also drives the WinCC RT license: each tag consumed by the runtime counts against the licensed point count, so an array of 50 rows x 7 tags = 350 HMI tags just for the catalog browse.

Performance and Sizing

For a 50-row, 6-column dataset, expect the VBS query-and-fill cycle to take 200-500 ms on a Core i5 panel running WinCC RT Professional, including network round-trip to the SQL Server. To bound worst case in production:

• Move sort and filter into SQL (ORDER BY, TOP n, WHERE); never sort in VBS.
• Open one persistent connection at runtime startup (Application.Start event) and reuse it; close it in Application.Stop to release the pool.
• For > 100 rows, bind the ADODB.Recordset directly to a custom OCX that implements WinCC's IDataSource interface instead of using tag arrays.
• Always SELECT only the columns actually displayed; never SELECT *.
• Use TOP n at the SQL layer when the operator only ever browses the first page; lazy-load the next page on scroll.

Troubleshooting Matrix

Verification

1. From the runtime PC, run sqlcmd -S DB-SRV\SQLEXPRESS -E -Q "SELECT TOP 5 PartID, Name FROM ProductionDB.dbo.PartsCatalog ORDER BY Category, Name, PartID" and confirm row order matches what WinCC will display.
2. In TIA Portal, start the WinCC RT simulation and load the screen; click the Load button and confirm the RowCount tag equals the SQL row count.
3. Select a row; verify Sel_Request pulses to 1 and Sel_Ack latches true in the S7-1500's online watch table within one HMI cycle.
4. Stop the runtime, restart, and re-trigger the query; row order and selection must be identical (determinism check for ORDER BY plus primary key).
5. Force a failover by stopping the SQL service briefly; confirm the VBScript logs the error to a status tag instead of leaving tags in a half-filled state.