How to Link Microsoft SQL Server to Fernhill SCADA

Help Contents

Introduction

You can link Fernhill SCADA data tables to Microsoft SQL Server using the ODBC driver. Microsoft SQL Server will forward queries on the linked tables to Fernhill SCADA.

Linking Data Tables into Microsoft SQL Server

  1. If you have not done so, follow the steps in: Setting up an ODBC System DSN.
  2. Start Microsoft SQL Server Management Studio
  3. In the Object Explorer, expand Server Objects:

    Microsoft SQL Server Management Studio showing expanded server objects.

  4. Right click Linked Servers and select New Linked Server:

    Microsoft SQL Server Management Studio new linked server dialog.

  5. Enter this information:

    FieldValueNotes
    Linked serverFERNHILLSCADAThe name of the data source to clients of SQL Server
    Server typeOther data source
    ProviderMicrosoft OLE DB Provider for ODBC Drivers
    Product NameFernhill SCADAThe name of the Fernhill SCADA ODBC driver
    Data sourceMy Fernhill SCADAThe ODBC System DSN pointing to the local instance of Fernhill SCADA
    Provider string You could set ODBC Connection Options here.
    LocationNot used, leave blank.
    CatalogTagDBThe default catalog in Fernhill SCADA

    Note: This step assumes an ODBC System DSN called My Fernhill SCADA has been setup.
    For more information see Setting up an ODBC System Data Source Name (DSN).

  6. Press OK to close the New Linked Server dialog. The FERNHILLSCADA linked server should now appear in the Linked Servers list:

    Fernhill SCADA Linked into SQL Server

  7. Expand Providers:

    Microsoft SQL Server Management Studio with the Providers branch expanded.

  8. Right click MSDASQL and select Properties. Check the option Non-transacted updates:

    Microsoft OLEDB Provider for ODBC Properties.

    Note: The Fernhill SCADA ODBC Driver does not support transacted updates.

  9. Click OK to close the window.

Accessing Data Tables in SQL Server

When working in SQL Server, you can select a Fernhill SCADA table by using a four-part name in this format:

ServerName.Catalog.Schema.Table

Where:

Field
ServerNameThe name of the Linked Server.
CatalogThe name of the catalog in the Fernhill SCADA database.
SchemaThe name of the schema in the Fernhill SCADA database.
TableThe name of the table in the Fernhill SCADA database.

For example:

FERNHILLSCADA.TagDB.CommonBase.TagDataPointAnalog

Example SQL Server Query on Fernhill SCADA

1. In Microsoft SQL Server Management Studio, click New Query.

2. Enter the following SQL:

SELECT FullName,Timestamp,Value,Units
FROM FERNHILLSCADA.TagDB.CommonBase.TagDataPointAnalog

3. Click Execute.  The query results should be visible in the Results window:

Fernhill SCADA Analog Table Displayed in Results Window

Further Information

Setting up an ODBC Data Source Name (DSN)

To learn about configuring ODBC data Source Names.

Fernhill SCADA ODBC Driver

To learn how the Fernhill SCADA ODBC Driver can provide data to other applications.

Glossary

For the meaning of terms used in Fernhill SCADA.