Writing WinCC 7.0 Tag Values to MS SQL Server via VBScript

Writing WinCC 7.0 Tag Values to MS SQL Server via VBScript

1. Overview

WinCC 7.0 ships with Microsoft SQL Server 2005 as its embedded archive database. The runtime databases CC_Alarms_<project>_<date>_<time>_<R>, CC_TagLogging_<project>_<date>_<time>_<R> and CC_Environment_<project>_<date>_<time>_<R> are managed by WinCC and are not the target of this guide. The objective here is to read live process tag values from the WinCC data manager and persist them into a user-defined SQL Server table so that an external application (reporting, MES, ERP, OPC UA bridge, analytics) can consume them independently of WinCC runtime state.

The recommended approach uses the WinCC VBScript runtime API (HMIRuntime.Tags) combined with ADODB (ActiveX Data Objects). The technique is supported across WinCC V7.0 through V7.5 and is documented in the WinCC Information System under Working with WinCC > VBS for Creating Procedures and Actions > Accessing the WinCC Runtime Database via OLE DB.

Reference: Siemens WinCC V7.5 SP3 Manual Collection (scripting chapters apply verbatim to V7.0). Microsoft ADO fundamentals: Microsoft ActiveX Data Objects (ADO).

2. Prerequisites

3. WinCC Tag Access via HMIRuntime

The HMIRuntime.Tags object exposes a tag-set container that reads multiple internal or external tags in a single round-trip to the data manager. This is preferred over reading tags one at a time because it reduces process image locking on large projects.

WinCC 7.0 tag type reference (from the WinCC Information System, Configuration > Tags > Tag Types):

4. SQL Connection Setup

ADODB connection strings for the three supported OLE DB providers:

Full connection string template:

For Windows authentication replace the credentials with Integrated Security=SSPI;:

5. Reading Tags and Building the INSERT Statement

The canonical pattern (extended from the original forum snippet with field-name placeholders, error handling, and provider configurability):

' WinCC 7.0 Global Script Action - write current tag values to MS SQL Server
Option Explicit

Dim sDataBaseName, sUname, sDBPassword, sSQLServer, sTableName
Dim sProvider, sConnString
Dim oConn, oCmd, oTagSet
Dim sFields, sValues, sSQL
Dim dStamp, i

' === CONFIGURATION ===
sProvider      = "SQLNCLI11"
sDataBaseName  = "TagArchive"
sUname         = "wincc_user"
sDBPassword    = "S3curePwd!"
sSQLServer     = "PRODDB01\WINCC"
sTableName     = "dbo.LiveTagSnapshot"

' === STAMP ===
dStamp = Now

' === READ TAGS ===
Set oTagSet = HMIRuntime.Tags.CreateTagSet
oTagSet.Add "Plant1_Temp"
oTagSet.Add "Plant1_Pressure"
oTagSet.Add "Plant1_Flow"
oTagSet.Add "Plant1_ValvePos"
oTagSet.Add "Plant1_RunFlag"
oTagSet.Read 1   ' 1 = synchronous read (wait for results)

' === BUILD INSERT ===
sFields = "(D_Date,Plant1_Temp,Plant1_Pressure,Plant1_Flow,Plant1_ValvePos,Plant1_RunFlag)"
sValues = "Values('" & FormatDateTime(dStamp, vbISO) & "'," & _
          "'" & Replace(CStr(oTagSet("Plant1_Temp").Value), ",", ".") & "'," & _
          "'" & Replace(CStr(oTagSet("Plant1_Pressure").Value), ",", ".") & "'," & _
          "'" & Replace(CStr(oTagSet("Plant1_Flow").Value), ",", ".") & "'," & _
          "'" & Replace(CStr(oTagSet("Plant1_ValvePos").Value), ",", ".") & "'," & _
          "'" & Replace(CStr(oTagSet("Plant1_RunFlag").Value), ",", ".") & "')"
sSQL = "INSERT INTO " & sTableName & " " & sFields & " " & sValues

' === EXECUTE ===
On Error Resume Next
sConnString = "Provider=" & sProvider & ";Persist Security Info=False;" & _
              "User ID=" & sUname & ";Password=" & sDBPassword & ";" & _
              "Initial Catalog=" & sDataBaseName & ";Data Source=" & sSQLServer & ";" & _
              "Application Name=WinCC_VBScript;Connect Timeout=5;"

Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = sConnString
oConn.Open
If Err.Number <> 0 Then
    HMIRuntime.Trace "SQL connect failed: " & Err.Number & " - " & Err.Description & vbCrLf
    Err.Clear
    Exit Sub
End If

Set oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
oCmd.CommandTimeout   = 10
oCmd.CommandText      = sSQL
oCmd.Execute
If Err.Number <> 0 Then
    HMIRuntime.Trace "SQL insert failed: " & Err.Number & " - " & Err.Description & vbCrLf & sSQL & vbCrLf
    Err.Clear
End If

Set oCmd = Nothing
oConn.Close
Set oConn = Nothing

6. Date Format Handling

The original snippet used D_Date = Now and concatenated the raw value into the SQL string. This fails on non-US locale Windows installations where the system separator is a period or the order is DD.MM.YYYY. SQL Server expects 'YYYY-MM-DD HH:MM:SS.sss' in DATETIME literals regardless of SET LANGUAGE.

Recommended WinCC-side formatting (works for all regional settings):

' Locale-independent ISO 8601 timestamp
Function IsoStamp(dt)
    IsoStamp = Year(dt) & "-" & Right("0" & Month(dt), 2) & "-" & Right("0" & Day(dt), 2) & " " & _
               Right("0" & Hour(dt), 2) & ":" & Right("0" & Minute(dt), 2) & ":" & Right("0" & Second(dt), 2)
End Function

Alternative - use SQL Server parameter binding instead of string concatenation to avoid any date-conversion ambiguity:

oCmd.CommandText = "INSERT INTO dbo.LiveTagSnapshot (D_Date, Plant1_Temp) VALUES (?, ?)"
oCmd.Parameters.Append oCmd.CreateParameter("pDate", 135, 1, 23, Now)   ' 135 = adDBTimeStamp
Set prm = oCmd.CreateParameter("pTemp", 5, 1, 0)                       ' 5   = adDouble
prm.Value = CDbl(oTagSet("Plant1_Temp").Value)
oCmd.Parameters.Append prm
oCmd.Execute

ADODB constant reference: DataTypeEnum (ADO).

7. Decimal / Float Value Handling

The reported failure mode is that integer-valued tags insert successfully while tags containing a decimal separator fail silently or raise Error 2147217913 (0x80040E07) "String data, right truncation" or "Syntax error converting the varchar value ...". Root cause: the German/French/Spanish/Italian locales (and others) emit 12,34 while SQL Server expects 12.34.

Two robust fixes:

1. Force the decimal separator at conversion time (preferred, lowest overhead): sValues = sValues & "'" & Replace(CStr(CDbl(oTagSet("Plant1_Temp").Value)), ",", ".") & "'," CDbl canonicalizes the value to a Double using the active locale; CStr then renders with the locale separator, which Replace swaps to the period that SQL Server requires.
2. Switch the process locale temporarily:

   SetLocale 1033  ' en-US; SQL Server always uses period
   ' do all CStr conversions here
   SetLocale 1031  ' restore de-DE etc.

   Less invasive in large projects because every CStr is affected globally.
3. Use parameterized queries (best practice) - ADODB passes Double as FLOAT directly without string conversion.

8. Authentication Options

9. SQL Server Instance Name Stability

One observation from the source: "the SQLServer names keep changing with time". WinCC automatically generates CC_* runtime databases whose physical file names encode the timestamp of project activation (CC_TagLogging_ProjectA_2018_03_15_14_22_07_R.mdf). When the project is reactivated these names change. For external user databases (like TagArchive) this is not a problem - the logical name remains stable. Always reference the logical database name, never the file name.

To avoid losing the connection on project reactivation, keep the user database in the default SQL instance (MSSQLSERVER) and not in the WINCC named instance that WinCC manages. The WINCC instance can also be re-installed/re-attached during SP upgrades, dropping ad-hoc tables. Use a separate SQL Server or a separate user instance for archival tables.

10. Connection Lifecycle and Performance

Opening an ADODB connection on every trigger is expensive (~30-80 ms). For high-frequency inserts use one of the following patterns:

Connection pooling reference: ADO Connection Pooling.

11. Error Handling and Diagnostics

Wrap all ADODB calls with On Error Resume Next and log to HMIRuntime.Trace:

Sub LogAdoError(stage)
    If Err.Number <> 0 Then
        HMIRuntime.Trace "[WINCC-SQL] " & stage & ": #" & Err.Number & " - " & Err.Description & vbCrLf
        Err.Clear
    End If
End Sub

Common ADODB error codes when writing to SQL Server from WinCC 7.0:

WinCC tracing destination: C:\Program Files (x86)\Siemens\WinCC\Diagnose\WinCC_Sys___.log. Open with WinCC Explorer > Tools > ApDiag.

12. Reading PDE#TAGs (WinCC Project Database)

The PDE#TAGs table is the master table of every tag configured in the WinCC project (name, type, limits, address). It does not contain current values - it is the engineering metadata. To enumerate tags from VBScript, query it via the same ADODB approach but point the connection string at the project database:

Set oProjConn = CreateObject("ADODB.Connection")
oProjConn.Open "Provider=SQLNCLI11;Integrated Security=SSPI;Initial Catalog=CC_Project_<projectname>;Data Source=(local)\WINCC;"
Set oRs = CreateObject("ADODB.Recordset")
oRs.Open "SELECT Tag, Typ FROM PDE#TAGs WHERE Typ = 4", oProjConn  ' 4 = 32-bit float
Do While Not oRs.EOF
    HMIRuntime.Trace oRs("Tag") & vbCrLf
    oRs.MoveNext
Loop
oRs.Close

For current values, the project database only contains them at runtime via the PDE#VALUE / archive tables. Use HMIRuntime.Tags for live data and the archive database (with the UserArchive or Connectivity Pack) for historical data.

13. Alternative Approaches Without VBScript

1. WinCC User Archives - configurable table inside the WinCC project with field-by-tag mapping. No scripting needed but limited to 32767 fields and one archive per project.
2. WinCC Connectivity Pack / OLE DB-A - read-side consumption from external apps; uses the @DatasourceNameRT runtime namespace you already reference.
3. OPC DA / OPC UA server - external SQL can subscribe via a third-party OPC client that persists into SQL.
4. Tag Logging to SQL directly - WinCC TagLogging writes to CC_TagLogging_* archives; external consumers read those archives via Connectivity Pack instead of duplicating tables.
5. PowerShell over WinRM - if the consumer is itself a Windows host, push tags via remote script. See PowerShell documentation.

14. Verification Procedure

After deploying the VBScript, validate end-to-end:

1. Open SQL Server Management Studio on the target server and run: SELECT TOP 10 * FROM TagArchive.dbo.LiveTagSnapshot ORDER BY D_Date DESC; Verify timestamps are within the last minute.
2. Confirm column types match the WinCC tag type (Section 3 table) - especially FLOAT vs REAL for 32-bit floats.
3. Trigger the script manually from a WinCC button and watch WinCC_Sys_*.log for [WINCC-SQL] trace lines.
4. Disconnect the SQL Server network cable and confirm the On Error handler logs gracefully without taking down the WinCC runtime.
5. Re-connect and verify that the next scheduled trigger writes a row successfully - confirms reconnect logic.
6. Inspect sys.dm_exec_connections on SQL Server for connections with program_name = 'WinCC_VBScript' and ensure pooled connections release on WinCC deactivation.

15. Troubleshooting Matrix

16. Field-Proven Caveats

• ADO versioning: On 64-bit WinCC installations, VBScript still runs in 32-bit mode (cscript/wscript). The 64-bit SQL Server requires the 32-bit Native Client installed side-by-side with the 64-bit one. Verify with C:\Windows\SysWOW64\sqlncli11.dll.
• Trigger storms: A global script scheduled at 1 Hz that opens a connection per tick can exhaust the SQL Server worker pool (max 32k connections on Standard, unlimited on Enterprise) within hours. Pool or batch.
• Tag Quality Codes: HMIRuntime.Tags(...).Quality returns 0=Good, 1=Bad, 2=Uncertain. Skip inserts when Quality > 0 to avoid corrupting trend analysis.
• Null handling: If a tag has never been written by the PLC, its Value is Empty in VBScript. CStr(Empty) returns "" which SQL will reject on NOT NULL columns. Use Nz() or explicit IsEmpty() check before insert.
• Time zone: Now in WinCC VBScript uses the server's local time. If the SQL Server is in UTC, subtract the offset or use DateAdd("h", -offset, Now) to avoid 1-hour jumps at DST transitions.

FAQ