logo-opclabs-new

Professional OPC Development Tools and Services

opc logo

OPC with Microsoft SQL Server

It is a common requirement in automation to store or retrieve data to or from SQL databases. QuickOPC will make it easy for you to create OPC SQL solutions, i.e. integrate OPC client functionality into your database solution with Microsoft SQL Server. Reading a value from OPC Data Access server, or writing a data value can be achieved in just several lines of code. QuickOPC component interfaces hide the complexities of OPC and allow you to focus on your main task.

Dedicated solutions that integrate OPC and SQL databases exist from various vendors. For example, you can purchase packages that log the OPC data into the database, or packages that download recipes from the database to the technology. If you have more specialized needs, then a development work is needed to make the OPC SQL integration happen, and that's where QuickOPC components can help. Using programming language of your choice, or even T-SQL, you can express custom application logic that dedicated solutions cannot provide.

Read More

Integration Scenarios

How can OPC be integrated with SQL server? There are multiple ways of doing it. We have classified them into two main groups, External Usage and Internal Usage.

External Usage - Your Application Connects to SQL Server

In this case, you are writing an application that connects to the SQL server using traditional means, such as ADO.NET, OLEDB, or ODBC. You have great flexibility in selecting technologies and application types - there are almost no limitations: Your code can be written C/C++, C#, VB6, VB.NET, or just about anything else.

You will use QuickOPC.NET components if the technology is based on Microsoft .NET, or you will use QuickOPC-COM if it is a language or tool that is capable of working with OLE automation objects. Either of these QuickOPC variations will give you client access to OPC servers located on your network.

This is the traditional approach; its main advantage is that the SQL server can stay free of any custom, OPC-related code. It may be problematic to use this approach if you already have lots of database code that just needs to be extended at certain places to rad in some OPC data or write them out. 


Internal Usage - Your Code Works from Inside the SQL Server

In this case, you are not writing a separate application - instead, your program lives within the SQL server itself. This approach allows close integration of OPC access with the database. On the other, it is less "DBA-friendly", in the sense that a close security review is needed in order to assure that the integrity of the SQL server is not compromised.

One option here is to use the facility of SQL server that allows OLE automation objects to be used within a Transact-SQL batch (ODSOLE). With QuickOPC-COM, such OPC access can be done right from T-SQL, without a need to load further objects (assemblies) to the SQL server itself.

If you have requirements that are complicated to express in T-SQL, you may prefer to use .NET languages (such as C#) to write the application logic inside the SQL server (SQLCLR). You can write stored procedures, user-defined functions, triggers and other object types that access OPC data in a .NET language, and create an assembly that is then cataloged inside the SQL server. Your objects can then be used from other T-SQL code. Note: This example, while being a .NET code, uses our QuickOPC-COM component; we are working on allowing the same (and even easier) directly with QuickOPC.NET.