Plug-in for your
MS SQL Server
With easy database connection to networked production
Microsoft SQL Server is one of the most common SQL servers in the industrial environment. Databases are the heart of networked production in modern Industry 4.0 production. That is where all relevant data for optimal production is stored and made available to other systems.
With the OPC Router Plug-in for MS SQL Server you can easily connect to the SQL Server and access its tables, views and procedures (see screenshot). In combination with the other Plug-ins, you can easily collect data from various systems in the SQL Server and distribute it back to the right system at the right time. For example, you allow the PLC via OPC UA to access the SQL server or very simply transfer data from SQL to SAP. The Plug-in list gives you an overview of all coupling options.
Our tutorial video explains how easy it is to transfer CSV files into a database or find a more detailed video about this topic by visiting our tutorial stream.
That’s how easy it is.
SQL Server connection via drag & drop
The connection of your MS SQL database is remarkably simple and intuitive with the OPC Router via drag & drop. The screenshot shows an example of a Microsoft SQL Server connection. A classic use case is the coupling of SQL to OPC, displayed with a stored procedure call.
Automated data exchange for your SQL databases
- Achieving more with combined systems:
By connecting Microsoft SQL via the OPC Router, valuable data can be distributed and recorded between systems. Making that vital connection eliminates island systems as well as propels and mobilizes interconnected production of applications, which were designed with SQL. This can also give a powerful edge to smaller systems and PLCs. - Industry 4.0 for Microsoft SQL:
Once you have established a connection between the Microsoft SQL Server and your systems, many new options for crosslinking and data transfer are open to you within the broad scope of industry 4.0 implementations. A classic application here is the coupling of SQL to OPC where the OPC Router acts as OPC Client. - About the OPC Router:
The OPC Router helps you connect your systems as data hub. Numerous Plug-ins are available, which make the OPC Router the ideal middleware in industrial settings.
MS SQL Server Plug-in application examples
Production data acquisition
With the OPC Router, you create the basis for your production data acquisition system. The various Plug-ins allow you to load data from various systems and transfer it to the evaluating target system. Typical targets are MES systems (Manufacturing Execution System) or ERP systems (Enterprise Resource System (e.g. SAP)). Due to the simple configuration, a Microsoft SQL Server can be used as a central operating database and the systems with the data sources are connected graphically and without programming. Existing operational data collections can often be connected via an SQL Server connection and are supplied with data via this connection. A typical example of data collected for production data acquisition is control data from PLC systems. Controllers are connected via the standardized OPC UA protocols (with the OPC UA Client Plug-in).
Database application without programming
To exchange data between SQL Server and other systems only the OPC Routers graphical designer is needed. After the connection parameters of the database have been announced and tested, the database objects can be searched and selected. In the connections, the data transfers between an external system (SAP, MQTT, OPC, REST, etc.) and the SQL Server are established. Once the project has been published, all data transfers can be checked and viewed in the graphical monitoring system.
Video: Easy transfer of CSV files into a database
Watch this video tutorial to see how easy it is to extract records from a CSV file and write the CSV column values to a database table. There is a simple format where each process variable has its own measure ID. Both process values (temperature and pressure) are written as individual data records in the same table. You can monitor a folder for the corresponding CSV file(s), read out data and write them into a corresponding table simply by drag & drop.
Connecting Systems with the OPC Router SQL Server Plug-in
Transfer Objects
The transfer object MS SQL Plug-in allows access to Tables, Views and Stored Procedures in the database.
Tables
The following actions are available for tables: Select, Update, Insert and Delete. After successful data processing, the OPC Router can mark (i.e. identify) the transferred data as such.
Views
Access Views exclusively via Select Query.
Stored Procedure
When accessing any stored procedure in the database, Execute is carried out by the OPC Router. During the process Input Parameter can be transmitted. It is also possible to read back retrieval information as well as return parameters (and also results).
Trigger
The MS SQL Plug-in has its database trigger.
Database Trigger
The database trigger monitors (i.e. checks) changes in Table columns. The OPC Router sends a change notification when a change occurs and then triggers a connection.
The database trigger does not have outgoing data fields.
Browsing
The MS SQL Plug-in can browse the available objects from the MS SQL Server.
Tables and Views
All tables and views the user has access to are readout from the MS SQL Server and then the columns and their types are distinguished and made available for user-specific selection.
Stored Procedures
The available stored procedures are also read out. Depending on the respective procedure, input and output parameters are read out together with the type information, and made available for selection. The obligational return value is automatically made available.