Discover our Clipsal Smart Home packages as seen on the Block

in location

OR

I'm looking for

  • Wholesalers
    • Uncheck All Check All
  • EXPERTISE
    • Expand
    • Expand
    • Expand
    • Uncheck All Check All
  • Switchboard Manufacturers
    • Uncheck All Check All
OR

The best way to see what Clipsal electric products can do for your home is seeing them in action at a Clipsal Display Centre.

Product stock may vary according to location. If you're after a specific product range, please check with your chosen location before visiting.

10 Results
    Load More Load Complete
    Change Location

    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

    Didn’t find what you were looking for?

    Try Searching Again View Our Categories

    Need further assistance?

    Our Customer Care department provides total customer service solutions for our residential, industrial and commercial applications.

    Get Assistance