Introduction
This document will demonstrate how to connect an OMRON NX102-1020 (database model) PLC to a MS SQL Server database. For this document, MS SQL Server 2019 will be used locally on a PC with IP address 192.168.250.50.
Pre-Requisites
Installation of MS SQL Server 2019
Please link any other KB articles or prior knowledge here which are referenced here
Further Reading
OMRON NJ/NX Database Connection User's Manual
Please refer to these Precautions and Terms and Conditions which relate to the information and program samples provided before proceeding
Procedure
Step 1 - Setting the Network
In the multiview explorer, under Configurations and Setup -> Controller Setup -> Built-in EtherNet/IP Port Settings
Keep default IP address for the OMRON NX102-1020 PLC as :192.168.250.1
Step 2 - SQL Server Configuration
On the Windows PC open SQL Server Configuration Manager
Under SQL Server Services make sure SQL Server (SYSMACDEMO) and SQL Server Browser are both running.
Under SQL Server Network Configuration -> Protocols for SYSMACDEMO make sure TCP/IP is Enabled.
Run SQL Server Management Studio
Connect to your SQL Server in this case SYSMACDEMO
Once connected right click the server and go to Properties
Under Security, make sure Server Authentication uses SQL Server and Windows Authentication mode. Press OK.
Right click Database and add a New Database. Give the database a name in this case SysmacDB
Step 3 - Creating a Table using a New Query
Create a table named OmronDemoTable via a query on SQL Server Management Studio. The table will consist of:
⦁ Product_ID of type INT
⦁ Product_Name of type varchar(50)
⦁ Product_Quantity of type INT
⦁ Batch_Date of type datetime
⦁ Discontinued of type bit
Highlight Tables and add a New Query (Ctrl + N).
Add the following code to create the table with the listed columns and data types then click Execute to run the query to create the table.
In the Object Explorer window click on the refresh button, this will refresh the SysmacDB to include the newly added dbo.OmronDemoTable.
Step 3 - Creating a Table using a New Query
Create a table named OmronDemoTable via a query on SQL Server Management Studio. The table will consist of:
⦁ Product_ID of type INT
⦁ Product_Name of type varchar(50)
⦁ Product_Quantity of type INT
⦁ Batch_Date of type datetime
⦁ Discontinued of type bit
Highlight Tables and add a New Query (Ctrl + N).
Add the following code to create the table with the listed columns and data types then click Execute to run the query to create the table.
In the Object Explorer window click on the refresh button, this will refresh the SysmacDB to include the newly added dbo.OmronDemoTable.
Step 3 - Configuring Database Security and adding the NJ/NX MAC as a User
In the Object Explorer Window under Security -> Logins, right click and add a New Login.
Under General:
Login name: OmronNX102
SQL Server Authentication (selected):
Password: Omron_DB1
User must change password at next login: unticked
Default database: SysmacDB
Under Server Roles, nesure public and sysadmin boxes are ticked.
Under Users Mapping, tick the SysmacDB Map box and under Database role membership for: Sysmac DB, make sure db_owner and public are ticked. Click OK
Step 4 - NJ/NX Database Connection Configuration
In Sysmac, in the multiview exporer pane - under Configurations and Setup -> Built-in EtherNet/IP Port Settings, set the IP of Port 1 to 192.168.250.1 (default)
Under Host Connection Settings -> DB Connection, right click DB Connection Settings -> Add and rename to DemoDB.
On the newly added DemoDB connection, under Connection Settings:
| Parameter | Setting |
|---|---|
| Datbase Type | SQL Server |
| Server Specification Method | IP Address |
| IP Address | 192.168.250.50 (IP address of the PC with the server on it) |
| Instance name/Port No | SYSMACDEMO (name of the server) |
| Service name/Database name | SysmacDB |
| Username | OmronNX102 |
| Password | Omron_DB1 |
Submit the changes. Go online from the top menu tab Controller -> Online then Controller -> Synchronize then click Transfer to Controller. Ensure there are no errors.
Info
If IP changes were made to the OMRON NJ/NX, power cycle the unit after a successful program transfer.
Step 5 - NJ/NX and MS SQL Connection Test
Stay online, under the connection settings of DemoDB, click on Communications Test button. If all is configured correctly, Test OK will show
Step 6 - Programming - Structure Creation to Mirror MS SQL Table
The OmronDemoTable structure will need to be created on the Sysmac platform in order to read, write and update the table on the MS SQL Database.
To commence programming work offline Controller->Offline. In the multiview explorer pane. Under Programming->Data->Data Types, select Structures and add a new structure named OmronDemoTable.
Using the table below from section 3-5 of the OMRON NJ/NX Database Connection User's Manual match the members added with the corresponding data types. For example, Product_Name varchar(50) (SQL Server) translates to Product_ID STRING[50] (Sysmac).
Section 3-5 Data Types Conversion in MS SQL to OMRON NJ/NX Controllers
