Create a SQL Server Database and Table with WinCC VBScript (ADODB)

• You cannot initially connect to a database that does not exist.
• Unlike pyodbc's autocommit flag, ADODB.Connection runs outside a user transaction by default, so CREATE DATABASE works without extra setup.
• Existence checks make reruns non-destructive, but they do not upgrade an old or incorrect schema.

WinCC VBScript Database-Creation Workflow

WinCC talks to SQL Server through ADODB, the COM automation library used by classic VB/VBScript, rather than a Python driver like pyodbc. The logic is identical in spirit but the objects and syntax differ. Database creation and table creation require separate connections because the target database is unavailable until the first operation finishes.

DDL—Data Definition Language—creates or changes database objects. CREATE DATABASE defines a database; CREATE TABLE defines a table and its columns. This script changes server state and should be executed only by an authorized user, from a WinCC Global Script action, against the intended SQL Server instance.

2. Requirements and Permissions

• WinCC project with Global Script (VBS) enabled.
• Microsoft ODBC Driver installed on the WinCC PC (e.g. "SQL Server" or "ODBC Driver 17 for SQL Server").
• Network access to SOFTWELL\WINCC.
• Windows authentication (Trusted Connection) enabled for the WinCC runtime identity.
• Server-level permission to create a database and database-level permission to create a table.

3. Build a Reusable Connection Helper

Function ConnectDB(databaseName)
    Dim conn, connString
    connString = "Driver={" & DRIVER_NAME & "};" & _
                 "Server=" & SERVER & ";" & _
                 "Database=" & databaseName & ";" & _
                 "Trusted_Connection=Yes;"

    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionTimeout = 15
    conn.Open connString
    Set ConnectDB = conn
End Function

On Error Resume Next
Dim ServerName, Conn
ServerName = InputBox("Enter SQL Server\Instance", "Easy Test 1 — InputBox + MsgBox", "SOFTWELL\WINCC")

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=MSOLEDBSQL;Server=" & ServerName & ";Database=SQF_DB;Trusted_Connection=Yes;"

If Err.Number <> 0 Then
    MsgBox "CONNECTION FAILED" & vbCrLf & Err.Description, vbCritical, "SQL Quick Test"
    Err.Clear
Else
    MsgBox "CONNECTION OK" & vbCrLf & "Database = SQF_DB", vbInformation, "SQL Quick Test"
End If

If Not Conn Is Nothing Then
    If Conn.State = 1 Then Conn.Close
End If
Set Conn = Nothing
On Error GoTo 0

The helper accepts the database name so the same connection settings can serve both stages. The raw server string preserves the named-instance backslash. Unlike pyodbc, ADODB has no separate autocommit argument — a plain connection is not inside a user transaction unless you explicitly call BeginTrans.

Create a SQL Server Database from master with VBScript

Sub CreateDatabase()
    Dim conn, sql
    Set conn = ConnectDB("master")

    sql = "IF DB_ID(N'" & DATABASE_NAME & "') IS NULL " & _
          "BEGIN " & _
          "  CREATE DATABASE [" & DATABASE_NAME & "]; " & _
          "END;"

    conn.Execute sql
    conn.Close
    Set conn = Nothing
End Sub

Dim ChargeNo, TempAct, SQLPreview
ChargeNo = InputBox("Enter Charge Number", "Easy Test 2 — InputBox + MsgBox", "CHG-001")
TempAct = InputBox("Enter actual temperature", "Easy Test 2 — InputBox + MsgBox", "825.5")

SQLPreview = "Target: SQF_DB.dbo.tblEvent" & vbCrLf & _
             "ChargeNo = " & ChargeNo & vbCrLf & _
             "Temp_Act = " & TempAct

MsgBox SQLPreview & vbCrLf & vbCrLf & _
       "SAFE TEST: review these values before running the database command above.", _
       vbInformation, "ADO / SQL Quick Test"

DB_ID(N'SQF_DB') returns the database ID when the database exists and NULL otherwise. The conditional prevents a second run from raising "database already exists" — the same guard used in the Python version.

Because ADODB is not inside an explicit transaction by default, CREATE DATABASE completes immediately without any special connection mode.

5. Reconnect to the New Database

Set conn = ConnectDB(DATABASE_NAME)
' CREATE TABLE runs here

On Error Resume Next
Dim ServerName, Conn
ServerName = InputBox("Enter SQL Server\Instance", "Easy Test 3 — InputBox + MsgBox", "SOFTWELL\WINCC")

Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=MSOLEDBSQL;Server=" & ServerName & ";Database=SQF_DB;Trusted_Connection=Yes;"

If Err.Number <> 0 Then
    MsgBox "CONNECTION FAILED" & vbCrLf & Err.Description, vbCritical, "SQL Quick Test"
    Err.Clear
Else
    MsgBox "CONNECTION OK" & vbCrLf & "Database = SQF_DB", vbInformation, "SQL Quick Test"
End If

If Not Conn Is Nothing Then
    If Conn.State = 1 Then Conn.Close
End If
Set Conn = Nothing
On Error GoTo 0

A connection stays tied to the database it was opened against. After creating SQF_DB from master, open a fresh ADODB.Connection whose Database= value is SQF_DB. This also gives a clean failure point if database creation was not successful or access was not granted.

Create a SQL Server Table with VBScript

Sub CreateEventTable()
    Dim conn, sql
    Set conn = ConnectDB(DATABASE_NAME)

    sql = "IF OBJECT_ID(N'" & TABLE_NAME & "', N'U') IS NULL " & _
          "BEGIN " & _
          "  CREATE TABLE " & TABLE_NAME & " (" & _
          "    DT          datetime      NOT NULL," & _
          "    TM          varchar(10)   NULL," & _
          "    SQF_No      int           NULL," & _
          "    ChargeNo    varchar(50)   NULL," & _
          "    Event_From  varchar(100)  NULL," & _
          "    Event_To    varchar(100)  NULL," & _
          "    Temp_Set    float         NULL," & _
          "    Temp_Act    float         NULL," & _
          "    Cp_Set      float         NULL," & _
          "    Cp_Act      float         NULL," & _
          "    Oil_Set     float         NULL," & _
          "    Oil_Act     float         NULL," & _
          "    Jacket_Set  float         NULL," & _
          "    Jacket_Act  float         NULL," & _
          "    Fan_Status  varchar(10)   NULL" & _
          "  );" & _
          "END;"

    conn.Execute sql
    conn.Close
    Set conn = Nothing
End Sub

Dim ChargeNo, TempAct, SQLPreview
ChargeNo = InputBox("Enter Charge Number", "Easy Test 4 — InputBox + MsgBox", "CHG-001")
TempAct = InputBox("Enter actual temperature", "Easy Test 4 — InputBox + MsgBox", "825.5")

SQLPreview = "Target: SQF_DB.dbo.tblEvent" & vbCrLf & _
             "ChargeNo = " & ChargeNo & vbCrLf & _
             "Temp_Act = " & TempAct

MsgBox SQLPreview & vbCrLf & vbCrLf & _
       "SAFE TEST: review these values before running the database command above.", _
       vbInformation, "ADO / SQL Quick Test"

OBJECT_ID(..., N'U') asks for an object ID specifically for a user table. The condition protects an existing table from being overwritten. It does not compare the existing table's columns or types; schema migration must be handled separately.

7. Understand the Furnace Event Columns

For a production design, consider datetime2 instead of legacy datetime, decimal(p,s) instead of approximate float where exact decimal behavior matters, a primary key, defaults/check constraints, and a single timestamp rather than duplicated DT/TM representations.

8. Verify Objects and Insert a Row

Function VerifyObjects()
    Dim conn, rs, sql
    Set conn = ConnectDB(DATABASE_NAME)

    sql = "SELECT DB_NAME() AS DatabaseName, " & _
          "OBJECT_ID(N'" & TABLE_NAME & "', N'U') AS TableObjectId;"

    Set rs = conn.Execute(sql)
    VerifyObjects = Not IsNull(rs.Fields("TableObjectId").Value)

    rs.Close
    conn.Close
End Function

Dim ChargeNo, TempAct, SQLPreview
ChargeNo = InputBox("Enter Charge Number", "Easy Test 5 — InputBox + MsgBox", "CHG-001")
TempAct = InputBox("Enter actual temperature", "Easy Test 5 — InputBox + MsgBox", "825.5")

SQLPreview = "Target: SQF_DB.dbo.tblEvent" & vbCrLf & _
             "ChargeNo = " & ChargeNo & vbCrLf & _
             "Temp_Act = " & TempAct

MsgBox SQLPreview & vbCrLf & vbCrLf & _
       "SAFE TEST: review these values before running the database command above.", _
       vbInformation, "ADO / SQL Quick Test"

Verification confirms both the active database context and the table object ID. Once verified, insert rows with a parameterized ADODB.Command — never by concatenating live tag values into SQL text:

Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO " & TABLE_NAME & _
    " (DT, TM, SQF_No, ChargeNo, ...) VALUES (GETDATE(), CONVERT(varchar(10), GETDATE(), 108), ?, ?, ...);"
cmd.Parameters.Append cmd.CreateParameter("SQF_No", 3, 1, , sqfNo)
' ... more parameters ...
cmd.Execute

Dim ObjectName
ObjectName = InputBox("Enter the object/tag name you want to test", "Easy Test 6 — InputBox + MsgBox", "Temp_Act")
MsgBox "Object reference concept:" & vbCrLf & _
       "Set obj = HMIRuntime.Tags("" & ObjectName & "")" & vbCrLf & _
       "After use: Set obj = Nothing", vbInformation, "Object Quick Test"

A complete deployment check should additionally query sys.columns for types, lengths, nullability and constraints.

9. Identifier Safety and Idempotent Reruns

Parameters (?) can represent data values, but not database/table identifiers — true in ADODB exactly as in pyodbc. The database name is therefore interpolated into DDL. This is safe only because DATABASE_NAME is a trusted script constant. Never accept an unchecked database name from an operator screen, recipe file or external input and insert it into SQL text.

The checks make this script create-if-missing: rerunning it on every WinCC startup leaves existing objects in place. That is useful, but not the same as schema versioning. If an existing tblEvent lacks a column or uses the wrong type, this script reports success without correcting it. Use reviewed migration scripts for schema changes.

10. Complete Corrected WinCC VBScript Template

Option Explicit

Dim SERVER, DATABASE_NAME, DRIVER_NAME, TABLE_NAME
SERVER        = "SOFTWELL\WINCC"
DATABASE_NAME = "SQF_DB"
DRIVER_NAME   = "SQL Server"
TABLE_NAME    = "dbo.tblEvent"

Function ConnectDB(databaseName)
    Dim conn, connString
    connString = "Driver={" & DRIVER_NAME & "};Server=" & SERVER & _
                 ";Database=" & databaseName & ";Trusted_Connection=Yes;"
    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionTimeout = 15
    conn.Open connString
    Set ConnectDB = conn
End Function

Sub CreateDatabase()
    Dim conn : Set conn = ConnectDB("master")
    conn.Execute "IF DB_ID(N'" & DATABASE_NAME & "') IS NULL BEGIN CREATE DATABASE [" & DATABASE_NAME & "]; END;"
    conn.Close : Set conn = Nothing
End Sub

Sub CreateEventTable()
    Dim conn : Set conn = ConnectDB(DATABASE_NAME)
    conn.Execute "IF OBJECT_ID(N'" & TABLE_NAME & "', N'U') IS NULL BEGIN CREATE TABLE " & TABLE_NAME & _
        " (DT datetime NOT NULL, TM varchar(10) NULL, SQF_No int NULL, ChargeNo varchar(50) NULL, " & _
        "Event_From varchar(100) NULL, Event_To varchar(100) NULL, Temp_Set float NULL, Temp_Act float NULL, " & _
        "Cp_Set float NULL, Cp_Act float NULL, Oil_Set float NULL, Oil_Act float NULL, " & _
        "Jacket_Set float NULL, Jacket_Act float NULL, Fan_Status varchar(10) NULL); END;"
    conn.Close : Set conn = Nothing
End Sub

Function VerifyObjects()
    Dim conn, rs
    Set conn = ConnectDB(DATABASE_NAME)
    Set rs = conn.Execute("SELECT DB_NAME() AS DatabaseName, OBJECT_ID(N'" & TABLE_NAME & "', N'U') AS TableObjectId;")
    VerifyObjects = Not IsNull(rs.Fields("TableObjectId").Value)
    rs.Close : conn.Close
End Function

Sub Main()
    On Error Resume Next
    CreateDatabase()
    CreateEventTable()
    If VerifyObjects() Then
        MsgBox "Database and table verified successfully.", vbInformation
    Else
        MsgBox TABLE_NAME & " was not created.", vbCritical
    End If
    On Error Goto 0
End Sub

Dim ChargeNo, TempAct, SQLPreview
ChargeNo = InputBox("Enter Charge Number", "Easy Test 7 — InputBox + MsgBox", "CHG-001")
TempAct = InputBox("Enter actual temperature", "Easy Test 7 — InputBox + MsgBox", "825.5")

SQLPreview = "Target: SQF_DB.dbo.tblEvent" & vbCrLf & _
             "ChargeNo = " & ChargeNo & vbCrLf & _
             "Temp_Act = " & TempAct

MsgBox SQLPreview & vbCrLf & vbCrLf & _
       "SAFE TEST: review these values before running the database command above.", _
       vbInformation, "ADO / SQL Quick Test"

11. Troubleshooting and Design Improvements

Recommended production upgrades

• Use a DBA-managed deployment account separate from the WinCC runtime account.
• Add a primary key such as Event_ID bigint IDENTITY.
• Use datetime2 and carefully selected decimal(p,s) precision/scale.
• Add check constraints or reference tables for valid fan and process-stage values.
• Create useful indexes only after studying read/write patterns.
• Store the connection string securely and validate every identifier against an allowlist.
• Use a migration tool or versioned SQL scripts for future schema changes.

USE [SQF_DB];
GO

IF OBJECT_ID(N'dbo.tblEvent', N'U') IS NULL
BEGIN
    CREATE TABLE dbo.tblEvent
    (
        ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
        DT datetime2(3) NOT NULL,
        TM varchar(10) NULL,
        SQF_No int NULL,
        ChargeNo varchar(50) NULL,
        Event_From varchar(100) NULL,
        Event_To varchar(100) NULL,
        Temp_Set decimal(9,3) NULL, Temp_Act decimal(9,3) NULL,
        Cp_Set decimal(9,3) NULL, Cp_Act decimal(9,3) NULL,
        Oil_Set decimal(9,3) NULL, Oil_Act decimal(9,3) NULL,
        Jacket_Set decimal(9,3) NULL, Jacket_Act decimal(9,3) NULL,
        Fan_Status bit NULL
    );
END;
GO

Dim DbName, Answer
DbName = InputBox("Enter database name for this lab", "Easy Test 8 — InputBox + MsgBox", "SQF_DB")
Answer = MsgBox("DDL PREVIEW ONLY" & vbCrLf & _
                "Database: " & DbName & vbCrLf & _
                "Review the SQL above in SSMS before executing it." & vbCrLf & vbCrLf & _
                "Do you understand what object will be created?", _
                vbYesNo + vbQuestion, "SQL DDL Quick Test")

If Answer = vbYes Then
    MsgBox "Good. Next, run the original SQL in SSMS on the training database.", vbInformation, "Quick Test"
Else
    MsgBox "Re-read the CREATE DATABASE / CREATE TABLE section first.", vbExclamation, "Quick Test"
End If