How to connect to Geo SCADA from MS SQL Server 2019 using Linked Servers

It is possible to make a direct connection to Geo SCADA from MS SQL Server 2019 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 Geo SCADA database can essentially be directly queried through MS SQL Server 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 Linked Server name can be “GEOSCADA” for example
• 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, including the rebranded Geo SCADA)
• The Data source is the name of the System DSN (64-bit version) configured previously to connect to ClearSCADA Server.In this case “Geo SCADA”. More details on how to configure a DSN to allow a third-party ODBC client to connect to the Geo SCADA database can be found in the Geo SCADA manual
• Click on the “OK” button to create the new Linked Server.

Allowing MS SQL Server to write to Geo SCADA Tables

If you intend to perform updates on Geo SCADA 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.

Note: 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 the GEOSCADA Linked Server

• Open a query window within SQL Server Management Studio - look for the "New Query" option in the toolbars:

Select FullName from GEOSCADA..Core.CDBPoint

Note that the use of "GEOSCADA" 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 Geo SCADA database structure. In the above example, the CDBPoint table is within the "Core" group and so the reference must include both Core and CDBPoint.

• Click on the "Execute" button on the toolbar and the data set should be retrieved from ClearSCADA database.
• The Level zero only option available in the properties of the MSDASQL provider turns on and off the SQLServer support for using Geo SCADA database schemas. So to reference CDBHistoric in the form of GEOSCADA...CDBHistoric rather than using GEOSCADA..Historic.CDBHistoric (or other schema name), enable the Level zero only option.

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 considered 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 occurring is with the use of DateTime fields in Geo SCADA.
The queries below highlight this problem:

SELECT TOP( 100000 )
"RecordTime", "FormattedValue", "StateDesc"
FROM
GEOSCADA..Historic.CDBHistoric
WHERE
"Id" = 6719 AND "RecordTime" > { ts '2021-09-02 00:00:00' }
ORDER BY
"RecordTime" DESC

is actually passed through to Geo SCADA server as:

SELECT TOP( 100000 )
"RecordTime", "FormattedValue", "StateDesc"
FROM
GEOSCADA..Historic.CDBHistoric
WHERE
"Id" = 6719
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-removable" and so it issues a query that expects to get ALL records for point Id = 6719 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 describes this as a "quirk" of the query processor.

There is however a solution 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 this:

SELECT * FROM OPENQUERY(GEOSCADA, 'SELECT TOP( 100000 )
RecordTime, FormattedValue, StateDesc
FROM
CDBHistoric
WHERE
Id = 6719 AND RecordTime > { ts ''2021-09-02 00:00:00'' }
ORDER BY
RecordTime DESC')

Note the added complexity of the query and the two single quotes around the time.

Publikováno pro:Schneider Electric Česká republika