Technical FAQs

Ask a Question

How to connect to ClearSCADA from MS SQL Server 2014 using Linked Servers

DISCLAIMER 

The example below is  intended to demonstrate one or more features or methods and is presented as a configuration example only. Schneider Electric assumes no liability for the use or application of this example or any portion thereof.


It is possible to make a direct connection to ClearSCADA from MS SQL Server 2014 using Linked Servers.  The linked server functionality in MS SQL Server will connect using the OLEDB Provider for ODBC.

The effect of a linked server is that the ClearSCADA database can essentially be directly queried through SQLServer with only a minor change to the structure of the query.

Making the Connection


  • From within SQL Server Management Studio, open the "Linked Servers" section below "Server Objects" in the Object Explorer;
  • Right-click on the "Linked Servers":

  • The Provider should be "Microsoft OLE DB Provider for ODBC Drivers" so that MS SQL Server can connect using the ClearSCADA ODBC interface;
  • The product name should match the name of the ODBC driver - in this case "ClearSCADA" (for ClearSCADA 2014 and up);
  • The Data source is the name of the System DSN configured previously to connect to ClearSCADA server.

Allowing MS SQL Server to write to ClearSCADA Tables

If you intend to perform updates on ClearSCADA tables, enable "Non Transact Updates" on the Linked Server Providers section:
  • From within SQL Server management Studio, open the Linked Servers/Providers section in the Object Explorer;
  • Double-click on "MSDASQL":
  • Enable "Non transacted updates" by checking its check box and clicking the "OK" button.
Should you fail to enable "Non transacted updates" and attempt to perform an UPDATE on a table, the following error message appears:"Msg 7390, Level 16, State 1, Line 1 The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface."

Running queries on ClearSCADA


  • Open a query window within SQL Server Management Studio - look for the "New Query" option in the toolbars:
          Select FullName from CLEARSCADA..Core.CDBPoint
 
Note that the use of "CLEARSCADA" tells MS SQL Server to use the linked server connection we created. The rest of the table reference defines the location of the table within the ClearSCADA database structure. In the above example, the CDBPoint table is within the "Core" group and so the reference must include both Core and CDBPoint.
  • The Level zero only option available in the properties of the MSDASQL provider turns on and off the SQLServer support for using ClearSCADA database schemas. So to reference CDBHistoric in the form of CLEARSCADA...CDBHistoric rather than using CLEARSCADA..Historic.CDBHistoric (or other schema name), enable the Level zero only option.
  • Click on the "Execute" button on the toolbar and the data set should be retrieved from ClearSCADA database.

Writing queries filtering on a DateTime field


Due to an issue with the SQL Server query optimizer, certain types of queries run in SQLServer against a linked server are consider non-remoteable. This means that SQLServer will ignore parts of the WHERE clause of the query and process the result set locally. One such case of this occuring is with the use of datetime fields in ClearSCADA.

The queries below highlight this problem:

SELECT TOP( 100000 )
    "RecordTime", "FormattedValue", "StateDesc"
FROM
    CLEARSCADA..Historic.CDBHistoric
WHERE
    "Id" = 50203 AND "RecordTime" > { ts '2016-01-02 00:00:00' }
ORDER BY
    "RecordTime" DESC

is actually passed through to ClearSCADA server as:

SELECT TOP( 100000 )
    "RecordTime", "FormattedValue", "StateDesc"
FROM
    CLEARSCADA..Historic.CDBHistoric
WHERE
    "Id" = 50203
ORDER BY
    "RecordTime" DESC

Note that the "RecordTime" part of the WHERE clause has been removed. In this case MS SQL Server has decided that the "RecordTime" part of this query is "non-remoteable" and so it issues a query which expects to get ALL records for point ID 50203 from CDBHistoric.

Since CDBHistoric could contain billions of records, this type of query will either be rejected as unconstrained, or take a very long time to run as all data would need to be passed to MS SQL Server for processing one record at a time. At best it would be grossly inefficient, at worst it would not work at all
 

Microsoft describe this as a "quirk" of the query processor.

There is however a couple of solutions to this problem.

Use OPENQUERY

OPENQUERY provides a way where an entire query can be sent through to a remote linked server. The query executed in MS SQL Server would look something like:

SELECT * FROM OPENQUERY(CLEARSCADA, 'SELECT TOP( 100000 )
    RecordTime, FormattedValue, StateDesc
FROM
    CDBHistoric
WHERE
    Id = 50203 AND RecordTime > { ts ''2016-01-02 00:00:00'' }
ORDER BY
    RecordTime DESC
')
 
Note the added complexity of the query and the two single quotes around the time.

Use a variable to hold the DateTime value

Another approach is to use a variable to hold the datetime value and pass the variable in the WHERE clause rather than the actual datetime value. See the example below:

declare @x as datetime
set @x = convert(datetime , '2016-01-02 00:00:00')
select Id, RecordTime, StateDesc from CLEARSCADA..Historic.CDBHistoric
    where    Id = 50203 AND
            RecordTime > @x
Was this helpful?
What can we do to improve the information ?