Advanced OPC Data Logger

Trust In Confidence!

For Windows 2000 - Windows 11 (2022) (incl. Server, x86 and x64). Latest version: 3.10.0 build 306. March 6, 2024.


OPC to MSSQL: Writing a lot of OPC tags to the MS SQL 2008 database

Suppose you need to write a lot of OPC tags to an MSSQL database. In that case, it is not adequate to create a table with a separate column for each item. The values of OPC tags can be modified non at the same time, which will result in entries with a lot of empty columns generated in the database.

It will be more useful to create a table consisting of three columns.


CREATE TABLE [dbo].[opc_data](
   [REC_ID] [int] IDENTITY(1,1) NOT NULL,
   [ITEM_NAME] [nchar](25) NOT NULL,
   [ITEM_VALUE] [nchar](255) NULL,
   [TIMESTAMP] [datetime] NULL
) ON [PRIMARY]

ITEM_NAME - will be the name of the item;

ITEM_VALUE - will contain the string value of the item;

TIMESTAMP - will contain the date and time when the value was modified.

1. Create a new user in the database or give permissions to write and read data from the created table to an existing user.

2. Create a group of OPC tags in the program (fig. 1) with the properties shown in the picture.

Group properties
Fig. 1 Group properties

3. Add the necessary items to the group.

Item list
Fig. 2 Item list

4. Click OK. Data like that should appear in the main window of the program:

Data

Fig. 3 Data

Every new value of OPC tags appears on a new line here. Every line contains the additional "ITEM_NAME" and "UPDATE_DATE_TIME" items with the item name and timestamp.

5. Select the data export plugin (fig. 4)

Selecting the data export plugin

Fig. 4 Selecting the data export plugin

6. Configure the data export plugin (fig. 5-6)

General settings

Fig. 5 Configuring the data export plugin. General.

To set up a connection, you should create and configure the ODBC data source for connecting to your MSSQL database. Click the "Configure" button to do it. After you create the data source, click "Update" and select the data source from the list.

Configuring the data export plugin

Fig. 6 Configuring the data export plugin. Connection.

7. Binding (fig. 7) allows you to specify which data to which columns the program should add. You should specify the column name and "bind" an item to it from the program's main window (the parser item). You should also specify the data type of the column.

Binding
Fig. 7 Configuring the data export plugin. Binding.

8. Click "OK" to save the changes.

9. Check the status bar to make sure the data is being successfully processed (fig. 8).

A message about data being successfully written

Fig. 8 A message about data being successfully written

Data in an MS SQL 2008 database

Fig. 9 Data in an MS SQL 2008 database

Related articles:

OPC and DCOM Configuration on Windows 2008 and Windows 7

OPC and DCOM Configuration on Windows Server 2012 and Windows 8