Writing Rows to SQL Database with Primary Key in WinCC RT Professional

1. Overview

WinCC RT Professional (TIA Portal) exposes VBScript as its primary automation language for runtime actions, schedulers, and faceplate events. When the runtime must persist process values, alarms, or audit data to a relational database, the typical pattern is an ADODB.Connection opened against an ODBC DSN, followed by an INSERT statement. The most common failure mode encountered in the field is the Primary Key constraint: a column declared PRIMARY KEY with no IDENTITY definition or default value will reject the INSERT if the script does not supply a unique value, and will silently fail or raise error -2147217864 (violation of PRIMARY KEY constraint) if a duplicate is supplied.

This tutorial walks through configuring a working write path from WinCC RT Professional into a SQL Server table that uses a Primary Key, including the ODBC configuration, the ADODB connection string, the VBScript insert logic, and the canonical Siemens "Database_1" project pattern. The MySQL equivalent is noted where the syntax diverges.

2. Prerequisites

Confirm scripting is enabled in the runtime project: Project > Runtime settings > Scripts > Allow VBScript. Without this flag, the scheduler and event-driven VBS actions will be ignored at runtime.

3. Configure the ODBC Data Source

On the WinCC RT Professional station, open ODBC Data Sources (64-bit) from the Windows Administrative Tools menu. SQL Server is a 64-bit application; using the 32-bit ODBC panel will produce "Architecture mismatch" errors when the runtime attempts to load the driver.

1. Switch to the System DSN tab and click Add.
2. Select ODBC Driver 17 for SQL Server and click Finish.
3. Name: WinCC_SQL (this is the DSN referenced by the connection string).
4. Description: WinCC RT Professional logging target.
5. Server: enter the SQL Server instance, for example SVR-PROD\SQLEXPRESS or SVR-PROD,1433 for a non-default port.
6. Authentication: choose SQL Server Authentication for service accounts, or Windows Integrated if the WinCC runtime service runs as a domain user with the required SQL login.
7. Set the default database to your target, e.g. ProcessData.
8. Test the data source. A successful test confirms the driver, network, and credentials all align before you touch WinCC.

4. Build the Table with a Primary Key

In SQL Server Management Studio, create a table whose first column is the Primary Key. The two viable strategies for auto-generating unique values are IDENTITY (SQL Server) and AUTO_INCREMENT (MySQL). For SCADA logs, the IDENTITY approach is preferred because the VBS script can omit the PK column from the column list.

CREATE TABLE dbo.ProcessEvents (
    EventID      INT IDENTITY(1,1) NOT NULL,
    TagName      NVARCHAR(128)  NOT NULL,
    TagValue     REAL           NULL,
    QualityCode  SMALLINT       NULL,
    EventTime    DATETIME2(3)   NOT NULL
        CONSTRAINT DF_ProcessEvents_Time DEFAULT (SYSUTCDATETIME()),
    CONSTRAINT PK_ProcessEvents PRIMARY KEY CLUSTERED (EventID)
);

Key points:

• IDENTITY(1,1) causes SQL Server to populate EventID automatically on each INSERT. The script must NOT supply a value for this column, or it will fail with error 544 (Cannot insert explicit value for identity column when IDENTITY_INSERT is OFF).
• SYSUTCDATETIME() as a default removes the need for the script to bind a timestamp; useful for multi-station rollouts where each HMI may be in a different timezone.
• The PRIMARY KEY CLUSTERED clause is what makes the column a real key; without it, a NOT NULL UNIQUE constraint is not a Primary Key and behaves differently in replication and indexing.

For more details on Primary Key semantics and the permissions needed to create one, see the Microsoft Learn reference: Create Primary Keys in SQL Server.

5. Primary Key Strategy Comparison

For nearly all WinCC logging scenarios, IDENTITY(1,1) is the correct choice. Manual PK assignment is only justified when the operator must enter or scan a known identifier (work order number, batch ID).

6. The Standard "Database_1" Example Project

WinCC Professional ships a reference project demonstrating OLE DB connectivity, located at:

C:\Program Files\Siemens\Automation\Portal V18\Data\WinCC\Examples\Database_1

The example uses the VBScript pattern below. Two objects are required: an ADODB.Connection for the session and an ADODB.Recordset for the row. WinCC also supports command-style execution via Connection.Execute(SQL) for pure inserts that do not need to return data.

7. ADODB.Connection and Connection String

The connection string binds the script to the DSN configured in Section 3. Two connection string variants are shown; pick the one that matches the authentication method chosen in the DSN.

' Variant A: SQL Server authentication (preferred for service accounts)
strConn = "Provider=MSDASQL;" & _
          "DSN=WinCC_SQL;" & _
          "UID=scada_writer;" & _
          "PWD=Str0ng!Pass;" & _
          "Database=ProcessData;"

' Variant B: Windows Integrated (service account authenticates)
strConn = "Provider=MSDASQL;" & _
          "DSN=WinCC_SQL;" & _
          "Trusted_Connection=Yes;" & _
          "Database=ProcessData;"

Always declare the connection object with explicit cleanup. A leaked connection will exhaust the SQL Server connection pool after roughly 100 open handles, and WinCC will start logging Error: -2147467259 [DBNETLIB] errors.

Dim conn, rs, strSQL, strConn
Set conn = CreateObject("ADODB.Connection")
Set rs   = CreateObject("ADODB.Recordset")

strConn = "Provider=MSDASQL;DSN=WinCC_SQL;" & _
          "UID=scada_writer;PWD=Str0ng!Pass;Database=ProcessData;"

On Error Resume Next
conn.Open strConn
If Err.Number <> 0 Then
    HMIRuntime.Trace "DB open failed: " & Err.Number & " " & Err.Description & vbCrLf
    Err.Clear
    Exit Sub
End If
On Error Goto 0

8. The INSERT Script for a Table with a Primary Key

This is the canonical insert for a table whose first column is EventID INT IDENTITY(1,1). Note that the column list omits EventID entirely; this is the only correct way to write to an IDENTITY column when IDENTITY_INSERT is OFF.

Dim strSQL
strSQL = "INSERT INTO dbo.ProcessEvents " & _
         "(TagName, TagValue, QualityCode) " & _
         "VALUES ('" & Replace(SmartTags("TagName"),"'","''") & "', " & _
                  CStr(SmartTags("TagValue")) & ", " & _
                  CStr(SmartTags("QualityCode")) & ");"

On Error Resume Next
conn.Execute strSQL, , adExecuteNoRecords
If Err.Number <> 0 Then
    HMIRuntime.Trace "DB insert failed: " & Err.Number & " " & Err.Description & vbCrLf
    Err.Clear
Else
    HMIRuntime.Trace "DB insert OK, rows affected: " & conn.Execute("SELECT @@IDENTITY").Fields(0).Value & vbCrLf
End If
On Error Goto 0

Three details resolve the most common failures from the field:

1. No WHERE clause on an INSERT: INSERT statements never have a WHERE. The original question describes a script that "will not insert a new row" when the WHERE RowNo is X clause is removed; this is a copy/paste artifact from an UPDATE example in the Siemens sample. Delete the WHERE line and the insert will succeed.
2. Single-quote escaping: the Replace(...,"'","''") doubles any single quote inside the tag value. Without it, a tag value of O'Brien would break the SQL and cause a syntax error.
3. Numeric coercion: wrap numeric tags with CStr() to force a string concatenation; unconverted Empty values produce NULL in the resulting text and SQL Server raises error 245 (Conversion failed).

For MySQL targets, the equivalent pattern is documented in the MySQL 9.7 INSERT statement reference. Use INSERT ... ON DUPLICATE KEY UPDATE if the application legitimately needs upsert behavior against a UNIQUE or PRIMARY KEY column.

9. Closing the Connection and Lifecycle

Open the connection at the start of an action and close it at the end. For long-running schedulers that fire every second, cache the connection object at module level and reopen on error. The classic mistake is opening a new connection on every tag change, which leaks handles within minutes.

' Cleanup at end of action
If rs.State = adStateOpen Then rs.Close
If conn.State = adStateOpen Then conn.Close
Set rs = Nothing
Set conn = Nothing

For 24/7 logging, the recommended pattern is a global ADODB.Connection in a runtime module that:

• Opens on RT startup via a "Start" event.
• Reconnects automatically if conn.State <> adStateOpen at the start of each write.
• Closes only on RT shutdown.

10. Verification Procedure

1. In SQL Server Management Studio, expand the target database, then Tables > dbo.ProcessEvents. Right-click and choose Select Top 1000 Rows to confirm the table exists with the expected columns and Primary Key icon.
2. In TIA Portal, open the WinCC RT Professional project and call the insert action from a button or scheduler.
3. Refresh the SSMS view. A new row should appear with an auto-generated EventID one greater than the previous row.
4. Check the WinCC runtime trace window (ApDiag.exe or Runtime > Trace) for the line DB insert OK. Any error number starting with -2147 indicates an ADODB-layer failure; anything else is a SQL Server-side error.
5. To test the Primary Key path, manually insert a row with a known EventID via SSMS using SET IDENTITY_INSERT dbo.ProcessEvents ON, then attempt a script insert with the same EventID. The script should fail with error 2627 (Violation of PRIMARY KEY constraint). This confirms your error-handling path is wired correctly.

11. Troubleshooting Matrix

12. Field-Proven Tips and Caveats

• Do not leave a connection open "to SQL2008 R2 on boot up" as a permanent pattern. While some legacy SCADA packages do this, in WinCC RT Professional it is safer to use a single cached connection that reconnects on error. A permanently open connection that is severed by a network blip will not auto-recover unless the script watches conn.State.
• Do not modify the bundled "Database_1" project files in place. Copy the project, rename the folder, then change the DSN and SQL inside the copy. This keeps the example intact as a known-good reference.
• Use the OLE DB provider MSOLEDBSQL for new deployments on SQL Server 2019+. The older SQLOLEDB provider is deprecated and removed from the OS in recent Windows builds. The Provider=MSDASQL; string used above is a thin wrapper over the ODBC driver and is the most compatible across WinCC versions.
• Parameterize when possible. While this tutorial uses string concatenation for clarity, a production deployment should use ADODB.Command with parameters to eliminate SQL injection risk and quote-handling bugs entirely.
• Set a query timeout. conn.CommandTimeout = 10 (seconds) prevents a hung connection from blocking the scheduler for minutes.
• Trace to file, not just to the diagnostic buffer. Use HMIRuntime.Trace with a configured trace path under Computer > Properties > Runtime > Trace for post-incident analysis.

13. Frequently Asked Questions