Configuring a MSSQL Database Connection with Vbscript in wincc

Published on Dec26, 2024 | Category: wincc tia

Share this article:

Microsoft SQL Server (MSSQL) is a relational database management system (RDBMS). it stores and retrieves data as request. in MSSQL data organized in table each table have row and column. MSSQL is easily connect with third party software by Data Source Name (DSN).A DSN is often used with ODBC (Open Database Connectivity).in an automation industries we use MSSQL to store tag value and application data which we required for many application like report generation, trend analyses, alarm history, data logging etc. in this article step by step guide to configure database connection and how to read and write tag value. vbscript is a programming language which also support database connection and read and write tagvalue.

what is Microsoft SQL server Management studio

Microsoft SQL Server Management Studio (SSMS) is an integrated environment for managing, configuring, and interacting with SQL Server instances. It is a powerful tool provided by Microsoft for database administrators (DBAs), developers, and analysts to manage databases, write queries, and perform administrative tasks. SSMS is free and can be downloaded from the official Microsoft website.

OPEN sql server management studio (SSMS) and connect to your SQL Server instance.
Use your credentials to log in (either Windows Authentication or SQL Server Authentication).

img/wincc vbscript for sql/what is Microsoft SQL server Management studio.webp

how to create tag database in MSSQL

first step to create tag database to store tag value so we can read and write these tag value. a tag database an organized collection of tag values that can be easily accessed, managed, and updated. You can create database by Using the GUI (Graphical User Interface) or Using SQL Query. follow these steps to create a database in MSSQL. Using the GUI (Graphical User Interface):

In the Object Explorer, expand the Server node.
Right-click on the Databases folder.
Select New Database from the context menu.
In the New Database window:
- Enter the Database Name (tagdatabase).
- Review or modify settings like file locations or sizes if needed (optional).
Click OK to create the database.

img/wincc vbscript for sql/how to create tag database in MSSQL.webp

how to create database Using SQL Query:

Open a new query window by clicking New Query in the toolbar.
Write the following SQL code: "CREATE DATABASE tagdatabase2;"
Execute the query by pressing F5 or clicking the Execute button.

how to configure dsn for wincc sql server

A Data Source Name (DSN) is a configuration that provides the necessary information for an application to connect to a database, such as the database driver, server address, and authentication details. DSN commonly stands for Data Source Name, a configuration used in computing to define how an application connects to a database. Step 1: Open ODBC Data Source Administrator

open control panel
open windows tools
Choose the appropriate version of ODBC:
- Use the 32-bit version if your application is 32-bit.
- Use the 64-bit version if your application is 64-bit.

here we select 32 bit version for this article.

img/wincc vbscript for sql/how to configure dsn for sql server.webp

Step 2: Add a New DSN

In the ODBC Data Source Administrator, go to the System DSN or User DSN tab:
- System DSN: Available to all users on the system.
- User DSN: Available only to the current user.
Click the Add button.

select driver for DSN

Select SQL Server or ODBC Driver for SQL Server from the list of drivers and click Finish.

img/wincc vbscript for sql/configure the dsn.webp

Step 3: Configure the DSN

In the Create a New Data Source to SQL Server window:
- Enter a name for the DSN (e.g., MySql).
- Provide an optional description.
- Select the SQL Server instance to connect.
Click Next.

img/wincc vbscript for sql/enter name of DSN.webp

Step 4: Configure Authentication

Choose the authentication method:
- Windows Authentication: Uses the current Windows user's credentials.
- SQL Server Authentication: Requires a username and password.
Click Next.

img/wincc vbscript for sql/Configure Authentication.webp

Step 5: Configure the Default Database

Select the default database for the DSN from the dropdown menu.
Click Next.

img/wincc vbscript for sql/Configure the Default Database.webp

Step 6: Test the DSN

Click the Test Data Source button to ensure the DSN is working correctly.
If the test is successful, click Finish.

img/wincc vbscript for sql/Test the DSN.webp

open tia portal and create a project

open tia portal and create a new project enter details of project or add hmi in your project in hmi screen add following component

button 1 is to check database connection
textfield 1 is used to show status of data base

img/wincc vbscript for sql/open tia portal and create a project.webp

vbscript for sql database connection in wincc tia portal

add a script and rename to check_connection. assign vbscript to button1 press event property. when button is pressed if database is connected than text field changed to database connection successfully otherwise text field changed database not connected.

Step-by-Step Explanation

Step 1: Declare necessary variables for the text field, database connection, connection string, and DSN (Data Source Name).
Step 2: Set the reference for the text field (used to display the connection status) on a specific screen.
Step 3: Define the ODBC DSN and connection string. The example uses Windows Authentication for SQL Server.
Step 4: Create an ADODB. Connection object for establishing the connection.
Step 5: Attempt to open the connection to the database using the provided connection string.
Step 6: If the connection is successful, update the text field with a success message. If it fails, display an error message with the description of the error.
Step 7: If the connection was successful, close the connection and clean up resources by setting objects to Nothing.