OPC to MSSQL: Writing several OPC tags to separate columns

If you need to not only log the changes of the OPC item values in the MSSQL database but also analyze and process them after that, it will be more convenient to create a table where there will be a separate column for every item. This method can be applied if there are not many OPC tags.

CREATE TABLE [dbo].[opc_data_2](
   [REC_ID] [int] IDENTITY(1,1) NOT NULL,
   [TIMESTAMP] [datetime] NULL,
   [ITEM1] [nchar](30) NULL,
   [ITEM2] [nchar](30) NULL
) ON [PRIMARY]

ITEM1 - will store the value of item 1;

ITEM2 - will store the value of item 2;

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.

Fig. 1 Group properties

3. Add the necessary items to the group.

Fig. 2 Item list

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

Fig. 3 Data

Every line contains the value of all items and the additional "UPDATE_DATE_TIME" item with the timestamp.

Steps 5-7 are similar to those from the previous example "Writing a lot of OPC tags".

8. Binding (fig. 4) is slightly different from that in the previous example because the table contains a different set of columns.

Fig. 4 Configuring the data export plug-in. Binding.

Click "OK" to save the changes.

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

Fig. 5 A message about data being successfully written

Fig. 6 Data in an MS SQL 2008 database

Related articles: OPC to MSSQL: Writing several OPC tags to separate columns

• Inserting OPC data to MS Access database through ODBC (features: adding groups and items, inserting to an ODBC database)
• Adding a timestamp in the CSV file (features: OPC groups)
• How to log OPC data to a SQL database.
• OPC to MSSQL: Writing a lot of OPC tags to MS SQL 2008 database
• OPC to MSSQL: Writing several OPC tags to separate columns
• OPC to MySQL: Writing OPC tags to MySQL 5 database
• OPC to a database: Writing OPC tags to a database
• OPC to Excel: Writing OPC data to Excel
• Filtering data by a tag value and write data to a database only when the value will change
• Filtering data out if an OPC tag value is not equal to a specified value
• Processing or storing OPC data by an event from the OPC server
• Aggregating data from multiple servers to one OPC server

OPC and DCOM Configuration on Windows 2008 and Windows 7

• Part 1. Installing OPC Core Components.
• Part 2. OPC and DCOM Configuration. Creating a user and giving access permissions.
• Part 3. Configuring the Windows 2008 and Windows 7 firewall for DCOM and OPC.
• Part 4. DCOM Configuration. Dcomcnfg.
• Part 5. OPC and DCOM Configuration. Typical problems and solutions.

OPC and DCOM Configuration on Windows 10, 11, Windows Server 2019, 2022

• Part 1. Installing OPC Core Components.
• Part 2. OPC and DCOM Configuration. Creating a user and giving access permissions.
• Part 3. Configuring the Windows 10, 11, Windows Server 2019, 2022 firewall for DCOM and OPC.
• Part 4. DCOM Configuration. Dcomcnfg.
• Part 5. OPC and DCOM Configuration. Typical problems and solutions.