Guidelines for Working with Clients

Important: You do not have the latest version of Historian! You are missing out on the newest capabilities and enhanced security. For information on all the latest features, see the Historian product page. For more information on upgrades, contact your GE Digital sales agent or e-mail GE Digital Sales Support. For the most up-to-date documentation, go here.

Supported Historian OLE DB Clients

This section describes the following clients supported by the Historian OLE DB Provider:

Other OLE DB clients are likely to work with the Historian OLE DB Provider, but have not been tested.

Connecting to the OLE DB Provider

  1. To connect an OLE DB client to a Historian server on a local connection (both software packages are on the same PC), run the following:
    Provider=iHOLEDB.iHistorian.1
  2. To connect an OLE DB client to an Historian Server on a remote PC, use the following string:
    Provider=iHOLEDB.iHistorian.1;PersistSecurity Info=False;USER ID=[Username];Password=[Password];Data Source=[Target]

    Replace Target with the network name of your Historian server, Username with your Historian server user name, and Password with your Historian server password.

    Important: Clients using the OLE DB Provider must initialize COM in Apartment threaded mode.

Historian Interactive SQL Application

The Historian Interactive SQL application (ihSQL.exe) is a test program that allows you to run a SQL query and display the results of the query in the same window. It is useful if you need to test or verify a query using the Historian OLE DB Provider. The Historian Interactive SQL application requires that you have some SQL knowledge, since there are no wizards or experts. It can open and save SQL queries and can even show multiple windows, each containing a query request to the same server or different servers.

This application is particularly helpful in troubleshooting OLE DB problems from different client tools. If you are having trouble with a SQL query in Visual Basic or Crystal Reports, try it in the Historian Interactive SQL application first.

When to Use the Historian Interactive SQL Application

The Historian Interactive SQL application allows you to access data quickly and efficiently. With this application you can perform such functions as the following:

  • Test SQL syntax before embedding it in an application.
  • Troubleshoot OLE DB connections or Historian errors.
  • Perform more complex searching or filtering of data than you can in the Historian SDK and Administration applications (for web and Windows).

Historian Interactive SQL Application Functionality

You can retrieve Historian data from any available Historian server. The Historian Interactive SQL application provides a login screen when you open the application so that you can specify the server you want to query. You specify the server at startup, and you can switch to another server after completing your queries, or you can also open multiple windows to the same server or different servers. For instance, you might want to open more than one window to compare two different time periods on the same server, or the same time period on different servers.

Aside from entering and executing SQL queries, you can save and load queries, or export query results to Microsoft Excel.

The following figure displays an example of a query and its results in the Historian Interactive SQL application.

Figure: Historian Interactive SQL Application

Historian Interactive SQL Toolbar

The Historian Interactive SQL application toolbar provides quick access to common functions such as:
  • Executing queries
  • Switching to a new Historian server
  • Exporting query results to Microsoft Excel
  • Saving a query
  • Printing query results
The following figure shows the toolbar for the Historian Interactive SQL application, outlining what each button does.

Figure: Historian Interactive SQL Toolbar

Starting the Historian Interactive SQL Application

When you start the application, you can log in to the default server or another Historian server.

To start the Historian Interactive SQL application, follow these steps:

  1. From the Start menu, select Programs > Historian 6.0 > Historian Interactive SQL.
    Important: In Windows 10 and Windows 7, the first time you use ihSQL.exe, you may need to select Run As Administrator. If you do not do this the first time you use ihSQL.exe, you may not be able to log in. After this, you do not need to select Run as Administrator again.
    The Historian Interactive SQL Login dialog box appears, as shown in the following figure.

    Figure: Historian Interactive SQL Login Dialog Box

  2. Do one of the following:
    • Select a server from the drop-down list.
    • Click Browse to open the Historian Servers dialog box to select a server.
  3. Enter a user name, password, and domain, if any.
    If no username and password is specified, the currently logged-in username and password is used.
    Note: You must enter a username and password when running on Windows 9x.
    • If the username or password is incorrect, a message box appears. Click OK and try to log in again.
  4. Click OK.
    You can now proceed with all Historian Interactive SQL functions or tests.
The session begins with the default values for the SET variables. See SET Variables for a table that lists the SET variables with their default values.

For more detailed information on the supported SQL syntax that you can use in the Historian Interactive SQL application, refer to Supported SQL Syntax.

Work With Queries

You can use the Historian Interactive SQL application to run a query against the data that is contained in the Historian database tables. A query is a SET or SELECT statement, or a combination of both of these SQL statements. When you execute a SELECT or SET statement in the Historian Interactive SQL application, you can execute only one SET and one SELECT statement per query.

The tables that you can query contain data and statistics associated with Historian tags, collectors, archivers, raw data, messages, comments, trend, and session settings. For information see Historian database tables.

Entering and Executing Queries

To enter and execute a query in the Historian Interactive SQL application, follow these steps:
  1. Enter your query in the Query Entry field in the top half of the window.
  2. Do one of the following:
    • Click the Execute Query button.
    • Press Ctrl+E.
    If you entered a valid query, the results of the query appear in the bottom half of the window, as shown in the following figure.

    Figure: Sample Query in the Historian Interactive SQL Application

Make a New Connection to the Historian Server

The Historian Interactive SQL application allows you to make multiple connections to the same server or different servers. This allows you to look at data from different servers.

Making a New Connection

  1. Select New from the File menu.
    The Historian Interactive SQL Login dialog box appears, as shown in the following figure.

    Figure: Historian Interactive SQL Login Dialog Box

  2. Do one of the following:
    • Select a server from the drop-down list.
    • Click the Browse button to open the Historian Servers dialog box to select a server.
  3. Enter a username, password, and domain, if any.
    Note: You must enter a username and password when running on Windows 9x.
    • If the username or password is incorrect, a message box appears. Click OK and try to log in again.
  4. Click OK.
    You can now proceed with all Historian Interactive SQL functions or tests on the selected server.
The new session starts with the default values for the SET variables. See SET Variables for a table that lists the SET variables with their default values.
Note: If modifications or additions are made to the list of available Historian servers using any of the Historian clients (Excel, non-web Administrator, or iFIX WorkSpace: Expression Builder and iFIX Migration Tools), those settings are global for any Historian clients running on that computer.

Save Queries

The Historian Interactive SQL application allows you to save any SQL query. When you save a query, you do not have to re-type the query every time you want to run it. Since some of your queries may be complex statements, recalling a saved query helps save time. The saved files are stored as .SQL files in the current working directory. You can later open these file in the Historian Interactive SQL application or even use them in other client applications.

Saving a Query

  1. Enter your query into the Query Entry field in the top half of the Historian Interactive SQL application window.
  2. Select Save from the File menu.
    The Save Query to File dialog box appears.
  3. Enter a name for the query.
    Important: Be sure to use the .SQL file extension.
  4. Click Save.
    The Historian Interactive SQL application saves the file into the working directory.

Recall Saved Queries

The Historian Interactive SQL application allows you to recall a previously saved query, so that you do not have to re-type the whole query again. This is particularly useful if you have a long query that you want to run against the Historian OLE DB Provider.

Recalling a Saved Query

  1. Select Open from the File menu.
    The Open Query File dialog box appears.
  2. Select the file that contains the query you want to open.
  3. Click Open.
    The query appears in the query entry field.
  4. Click the Execute Query button to run the query.

Export Query Results to Microsoft Excel

The Historian OLE DB Interactive SQL application allows you to export the results of a query directly into Microsoft Excel, as shown in the following figure.

Figure: Sample Export of Query Results Into Microsoft Excel

After you export the results, you must format the date and time column in Microsoft Excel so that it displays correctly. See Formatting Dates and Times in Excel for instructions on how to format the date column.

Exporting Query Results to Excel

  1. Enter the query that you want to execute in the query field.
  2. Click the Execute Query button.
  3. Click the Export Results to Microsoft Excel button, as shown in the following figure.

    Figure: Export Results to Microsoft Excel Button

    After you click the Export Results to Microsoft Excel button, Microsoft Excel opens with your data already inserted into a spreadsheet. Additional formatting to the data can be done in Excel.

    NOTE: Ensure that the cells in the Excel worksheet do not contain quotation marks at the beginning.

Proficy Real Time Information Portal SQL Client

Proficy Real Time Information Portal is a web-based tool for accessing, analyzing, and visualizing production information. It has sophisticated trending and reporting capabilities that take advantage of the vast archival and retrieval capabilities of Historian.

Parameterized SQL Queries in the Proficy Portal

Parameters are used within the Proficy Real Time Information Portal to build SQL queries that can be reused with different values. In the place of a constant value in a SQL query, you can instead place a parameter, which takes a value at execution time. Parameterized SQL queries are driven by Proficy Real Time Information Portal components such as list boxes, combo boxes or grids.

Parameterized queries are built in the Proficy Real Time Information Portal SQL Query Builder application. You can define a parameter by entering the name of the parameter in the Parameter field of the Specify Selected Item Wizard or the Specify Criterion Wizard. When you enter a name, the Statement Builder adds a question mark to the end of the name and encloses the entire string in curly brackets. For example, the parameter temperature becomes {temperature?}.

You also have the option to select a data type for the parameter. The data type determines the kind of data the parameter can hold. By default, the data type is set to char. However, you can select int, date, num, or char as the type of database column.

When you define a parameter in the SQL Statement Builder, you can specify a default value for the parameter from the Parameters tab.

Parameter names must be unique. You cannot create multiple parameters with the same name. Consult your Proficy Real Time Information Portal documentation for more information.

Crystal Reports

Crystal Reports allows you to create reports easily through its experts and wizards. Refer to the Crystal Reports documentation for more details. When working with Crystal Reports, be aware of the following items:

  • Crystal does not support the SET command. You must use a WHERE clause in a SELECT statement to specify query parameters.
  • A single Crystal Report can only retrieve data from one server, but you can create subreports from different servers within a report.
  • The Crystal Reports application does not display milliseconds in timestamps.
  • If you want to report on numeric data in the Value or Quality columns of the ihRawData table, you may want to convert all Variant to Float data types from the Report Expert, so that Crystal Reports displays them correctly in the report. This also enables the correct formatting.
    Note: See Displaying Variant Data Type Columns as Floats in Crystal Reports for more details.
  • Analysis of the ihTrend and ihAlarm tables in Crystal Reports is not supported.

Creating a Crystal Report with Historian Data

The following instructions offer an example of how to import Historian table data into Crystal Reports. For detailed instructions on formatting and using Crystal Reports, refer to the Crystal Reports online Help.

To import Historian table data into Crystal Reports using the Standard Report Expert:

  1. Select New from the File menu in Crystal Reports.
    The Crystal Reports Gallery dialog box appears.
  2. Select the Using the Report Expert option, and then select the Standard Report Expert in the list of experts, and then click OK.
    The Standard Report Expert appears, with the Data tab selected.
  3. Click the Database button.
    The Data Explorer appears.
  4. Open the More Data Sources folder, and then open the OLE DB folder.
  5. Select Make New Connection and click Add.
    The Data Link Properties dialog box appears.
  6. Select the Historian OLE DB Provider, and then click Next to display the information in the Connection tab.
  7. Leave these fields empty to use the default server and currently logged-in user. Otherwise, do the following:
    1. Enter the name of the Historian server in the Data Source field.
    2. Clear the Blank Password check box.
    3. Enter a Windows username and password.
  8. Click OK.
    The Historian OLE DB Provider tables should now appear in the Data Explorer.
  9. Select the table that you want to query and click Add, and then click Close to exit the Data Explorer.
  10. Click on the Fields tab of the Standard Report Explorer, click a field that you want to report on, and then click Add to move the field into the Fields to display list.
    Note: If you want to report on numeric data in the Value or Quality columns in the ihRawData table, you may want to convert all Variant data types to Float data types, so that Crystal displays them correctly in the report. Refer to Display Variant Data Type Columns as Floats in Crystal Reports for details.
  11. Repeat the previous step for each field that you want to add, and then click Finish to generate the report.

Display Variant Data Type Columns as Floats in Crystal Reports

You must convert Variant data types to Float data types in Crystal Reports to format decimal point precision in your report. For instance, if retrieving the Value column from the ihRawData table, you would need to convert the values to Floats. You do not have to perform these steps if you are working with strings.

Converting the IhRawData Value Column From Variant to Float

  1. After you have added the Historian OLE DB Provider database connection and added Historian database tables, select the Fields tab in the Standard Report Expert.
    The following figure shows how the Fields tab should appear.

    Figure: Fields Tab in Standard Report Expert

  2. Click the Formula button.
    The Formula Name dialog box appears.
  3. Enter a name.
    You can enter any name that you want.
    The Formula Editor appears.
    Note: You can also access the Formula Editor by selecting Field Object from the Insert menu. This opens the Field Explorer. Right-click the formula fields and select New from the pop-up menu.
  4. Enter the following text in the Formula field:
    ifnumerictext({ihRawData.Value}) then cdbl({ihRawData.Value}) else 0
    The Formula Editor should look similar to the following figure.

    Figure: Crystal Reports Formula Editor

  5. Click the Save button.
  6. Use the formula as a normal numeric column instead of the Value column in the report.

Crystal Reports Date and Time Formats

This section describes how to format the date/time column for Historian tables in Crystal Reports, if you need to display dates in a specific format. When formatting timestamps, note that milliseconds do not display in Crystal Reports. For more specific information on formatting reports, refer to the Crystal Reports online Help.

Formatting Dates and Times in Crystal Reports

  1. Select a field in a column that contains timestamps.
  2. Right-click the field and select the Format Field option from the right-click menu.
    The Format Editor dialog box appears.
  3. Click the Date/Time tab if it is not already selected.
  4. Select the date format that you want.
  5. Click OK.
    All timestamps in the column should then update to the new format that you just selected.

Microsoft Excel

With Excel, you can import a snapshot of Historian data at a single point in time. You can make Historian available as a data source in Excel by either of the following methods:

  • Importing the Historian table data directly
  • Importing the Historian table data from a .UDL file

Once Historian is available as a data source, you can create and edit SQL queries in Excel. The following sections describe how to import Historian data, edit the default query, refresh the query data, and format the date/time columns. For detailed information about using Microsoft Excel, refer the Microsoft Excel online Help.

When to Use Excel Instead of the Historian Excel Add-In

Use the Excel Add-In when you want to get data into Microsoft Office 2003, 2007 or 2010 (32-bit/64-bit). Use the Historian OLE DB Provider with Excel, instead of the Excel Add-In, when you want to do any of the following:

  • Perform advanced filtering, sorting, and joining of data.
  • Obtain detailed information from the ihTrend table.
  • Run calculations using the SQL aggregate functions.
  • Perform advanced summaries.

Importing Historian Table Data Into Excel

  1. From the Data menu, select Import External Data and then Import Data.
    The Select Data Source dialog box appears.
  2. Select +Connect to New Data Source.odc from the My DataSources folder (the default folder) and click Open.
    The Data Connection Wizard appears.
  3. Select Other/Advanced from the list of data sources to which you can connect, and then click Next.
    The Data Link Properties dialog box appears.
  4. Select Historian OLE DB Provider from the OLE DB Providers list, and then click Next.
    The Connection tab appears in the Data Link Properties dialog box.
  5. Leave these fields empty to use the default server and currently logged-in user. Otherwise, do the following:
    1. Enter the name of the Historian server in the Data Source field.
    2. Clear the Blank Password check box.
    3. Enter a Windows username and password.
    4. Select the Allow Saving Password check box.
      Note: You only need to select the Allow Saving Password check box when the password is not blank.
  6. Click Test Connection to confirm that the data source, username, and password provide a successful connection, and then click OK.
    The Select Database and Table screen appears in the wizard.
  7. Click the table that you want to query, and then click Next.
    The Save Data Connection File and Finish screen appears in the wizard.
  8. Accept the default settings and click Finish.
    The Import Data dialog box opens.
    Note: If you want to run a specific SQL command instead of the default table command setting, refer to Edit SQL Queries in Excel.
  9. Click OK to import the column data from the selected table.
    Historian data populates the current spreadsheet.

Import Historian Table Data From a .UDL File Into Excel

With a universal data link (.UDL) file, you can specify the connection information so that Excel can connect to the tables in Historian OLE DB Provider and import the data. Before you can import data using a .UDL file, you must create the .UDL file.

Creating a .UDL File

  1. Create a .TXT file.
    The best folder to use is the My Data Sources folder in the My Documents folder.
  2. Rename the .TXT file extension to a .UDL file extension.
  3. Double-click the .UDL file.
    The Data Link Properties dialog box appears.
  4. Click the Provider tab.
  5. Select Historian OLE DB Provider, and then click Next.
    The Connection tab appears in the Data Link Properties dialog box.
  6. Leave these fields empty to use the default server and currently logged-in user. Otherwise, do the following:
    1. Enter the name of the Historian server in the Data Source field.
    2. Clear the Blank Password check box.
    3. Enter a Windows username and password.
    4. Select the Allow Saving Password check box to save the password that you entered.
      Tip: Leave the server, user name, and password fields empty so that the report can be shared by clients with different default servers.
  7. Click Test Connection to confirm that the data source, username, and password provide a successful connection, and then click OK.

Importing Data Into Excel by Using a .UDL File

  1. From the Data menu, select Import External Data and then Import Data.
  2. Select the .UDL file that you just created, and then click Open.
    The Select Table dialog box appears.
  3. Click the table that you want to query, and then click OK.
    The Import Data dialog box appears.
    Note: If you want to run a SQL command instead of the default table command setting, refer to Edit SQL Queries in Excel.
  4. Click OK to import the column data from the selected table.
    Historian data populates the current spreadsheet.

Edit SQL Queries in Excel

By default, data import functionality in Excel selects all columns from the specified Historian table using the default query parameters. This command is the equivalent of running the SQL command SELECT * FROM TABLE_NAME, where TABLE_NAME is the name of the table that you want to query.

You can change the query by issuing a different SQL query if you are familiar with SQL syntax. Refer to the Microsoft Excel documentation for more information.

If you are unsure if the SQL syntax is correct, you can test your SQL query outside of Excel using the Historian Interactive SQL application. See Historian Interactive SQL Application for more details.

Excel Date and Time Formats

This section describes how to format the date/time column for Historian tables in Excel if you need to display a specific date format. For more specific information on formatting spreadsheets, refer to the Microsoft Excel online Help.

Formatting Dates and Times in Excel

  1. To select the column you want to format, click its heading.
  2. Right-click the selected column.
  3. Select Format Cells from the right-click menu.
  4. Click the Number tab, if it is not already selected.
  5. Click the Date category.
  6. Select a timestamp type in the Type group box.
    • If you need to display milliseconds, instead of clicking the Date category, select Custom, and then enter dd-mmm-yyyy hh:mm:ss.000 in the Type field.
  7. Click OK.

Refresh Excel Data

You can use the data refresh feature in Excel to update the external data that displays in the spreadsheet. When you perform the update, the current Historian data at the time of the refresh is returned with the query. The refresh feature is most useful when using relative start times, such as Now - 2h. Excel also allows you to automatically set refresh intervals.

Refreshing Data in Excel

  1. To refresh the data that currently displays in the Excel spreadsheet, click the Refresh Data button on the External Data toolbar, as shown in the following figure.

    Figure: Microsoft Excel Refresh Data Button

    • If the External Data toolbar is not available, select Toolbars from the View menu, and then click External Data.
  2. To automatically set refresh intervals, click the Data Range Properties button (the second button from the left) in the External Data toolbar.
Refer to the Microsoft Excel online Help for more detailed instructions.

Linked Servers in Microsoft SQL Server

If you require the ability to relate Historian data with other data in SQL Server tables such as batch events, iFIX alarms and events, iDownTimedata, and any other information that is available in a relational database, you can use the Historian OLE DB Provider as a linked server in Microsoft SQL Server. You can also use the OLE DB Provider as a linked server if you do not want to duplicate data with an import.

With linked servers, when you query data from Historian, the SQL Server fetches the requested data from Historian at the time the query is executed. Data is not duplicated because nothing is imported or stored in SQL Server. The data is simply returned as part of a query, just as any other query on a SQL Server database would return data.

Another benefit of using the Historian OLE DB Provider as a linked server is that clients do not need any Historian software installed. For example, a client tool such as Microsoft Query Analyzer can be used to retrieve Historian product data over the network on a computer with no Historian software installed.

Configuring the Historian OLE DB Provider as a Linked Server

The following steps are necessary in order to access a linked server via the OPENQUERY statement.
  1. From the Start menu, open the SQL Server Enterprise Manager.
  2. Select a SQL server and open the Security folder.
  3. Right-click the Linked Servers folder and select New Linked Server from the right-click menu.
    The Linked Server Properties dialog box appears.
  4. Enter a name for the linked server, such as iHist, and then select the Historian OLE DB Provider from the Provider Name drop-down list.
  5. Enter the name of the Historian server in the Data Source field, and then click the Provider Options button.
    The Provider Options dialog box appears.
    Note:
    • Select the Level Zero Only option only when older versions of SQL Server are used. For better performance while executing small queries, select the Allow in Process option. Clear the option if larger queries are to be executed.
    • For configuring the Historian 64-bit OLE DB provider as a linked server, the Allow in Process option is mandatory.
  6. Click OK in the Provider Options dialog box.
  7. If Historian security is enabled, enter a Historian username and password.
  8. For SQL Server 2008 (32-bit/64-bit), follow these steps:
    1. Click the Security tab.
    2. Select the Be made using this security context option.
    3. Enter a Historian username and password in the Remote Login and With Password fields.
  9. Click OK in the Linked Server Properties dialog box to create the linked server.
For more information on linked servers, refer to the Microsoft SQL Server online Help.

Use Microsoft System Stored Procedures

You can use the sp_addlinkedserver system stored procedure from Microsoft SQL Server to programmatically configure a linked server definition. However, using this system stored procedure alone does not provide a way to set the Allow In Process and Level Zero Only options.

To use sp_addlinkedserver you must first configure a linked server and options using the Enterprise Manager , as described in Configuring the Historian OLE DB Provider as a Linked Server. Then, since the options Allow In Process and Level Zero Only apply to all linked servers that use the provider, you can create additional linked server definitions to other Historian servers using sp_addlinkedserver.

Once you create your linked server definitions, you can then perform tasks such as using sp_linkedservers to browse the set of linked servers, or using sp_dropserver to remove a linked server.

Example 1: Configure a Linked Server Definition by Using sp_addlinkedserver

EXEC sp_addlinkedserver @server='MYSERVER_LS', @srvproduct='', @provider='iHOLEDB.iHistorian.1', @datasrc='MY_SERVER'

Example 2: Browse Linked Server Definitions by Using sp_linkedservers

EXEC sp_linkedservers

Example 3: Delete Linked Server Definitions by Using sp_dropserver

EXEC sp_dropserver 'MYSERVER_LS', 'droplogins'

Refer to the Microsoft SQL Server documentation for additional information on sp_addlinkedserver, sp_linkedservers, and sp_dropserver. If you have access to MSDN, refer to the System Stored Procedures topic in the Microsoft SQL Server online documentation.

Access a Linked Server in Microsoft SQL Server

You can use four types of syntax to access the Historian OLE DB Provider as a linked server:

  • OPENQUERY
  • Four-Part Name Syntax
  • OPENROWSET
  • OPENDATASOURCE

OPENQUERY is the recommended method of accessing data by means of a linked server. This method requires that you preconfigure a linked server definition. Then, use that linked server name in the OPENQUERY command. Four-part name syntax also requires that you preconfigure a linked server definition.

OPENROWSET and OPENDATASOURCE are considered ad hoc methods of accessing data via an OLE DB provider. They are recommended only for infrequently accessed data. When using either syntax, you must specify the data source, username, and password in each query instead of configuring it once in a linked server definition. If you want to limit the number of users to a defined set of servers and user names, you can disallow all methods of ad hoc access by selecting the Disallow Adhoc Accesses option in the Provider Options dialog box.

The sections that follow provide some examples of each syntax type.

Use OPENQUERY to Access a Linked Server

The following examples show how to use OPENQUERY to access the Historian OLE DB Provider as a linked server in Microsoft SQL Server. Be sure that you configure the linked server as described in Configuring the Historian OLE DB Provider as a Linked Server before using any of the examples.

Note: You cannot use OPENQUERY to access the ihTrend table. Use four-part name syntax to access the ihTrend table.
Example 1: Return a List of Tags From Historian
SELECT * FROM OPENQUERY(iHist,'SELECT * FROM ihTags')
Example 2: Use a Complex Query to Extract Values From Historian
SELECT TagName, TimeStamp, Value, Quality FROM OPENQUERY (iHist,'
SET
StartTime=yesterday-12Day, EndTime=Today, IntervalMilliseconds=1Hour, SamplingMode=Calculated, CalculationMode=Maximum
SELECT * FROM ihRawData WHERE TagName LIKE *simulation00001')

Use Four-Part Name Syntax

Although four-part name syntax works with all tables, it is only necessary to use it with the ihTrend table, because the ihTrend table does not work with OPENQUERY.

Example: Access the ihTrend Table From a Linked Server
SELECT * FROM iHist...[SELECT timestamp, *.value FROM ihTrend]

Use OPENROWSET and OPENDATASOURCE to Access a Linked Server

SQL Server supports ad hoc access for infrequently accessed data using OPENROWSET and OPENDATASOURCE. However, you achieve the best results using the OPENQUERY command.

The following examples show how to use OPENROWSET and OPENDATASOURCE to access the Historian OLE DB Provider as a linked server in Microsoft SQL Server.

Example 1: Use OPENROWSET With a SQL Query
Note: The following example uses double quotes surrounding date times, because single quotes do not work inside the overall single-quoted query. It is important for you to use double quotes in this scenario.
SELECT * FROM OPENROWSET('ihOLEDB.iHistorian.1', 'MY_SERVER';'';'','SET starttime="2002-01-30 10:00:00", endtime="2002
Example 2: Use OPENDATASOURCE to Access a Table
SELECT * FROM OPENDATASOURCE('iHOLEDB.iHistorian.1', 'Data Source=MY_SERVER')...ihTags
Example 3: Use OPENDATASOURCE With a SQL Query and Security
SELECT * FROM OPENDATASOURCE('iHOLEDB.iHistorian.1', 'Data Source=MY_SERVER;User ID=user1;Password=thepassword')...[SE

Advanced Linked Server Examples

Example 1: Join Historian Data With iFIX Alarms Logged With AlarmODBC

The following examples display some advanced SQL queries that use the linked server capability in Microsoft SQL Server.

The following example determines the last time and date a specific analog tag went into alarm. Then, the date and time of the alarm is used to collect the data from the previous hour leading up to the alarm. You can use this example to determine if the value spiked into alarm or slowly approached the alarm limit.

declare @var1 as varchar(300)
declare @iHistServer as varchar(10)
declare @Tagname as varchar(40)
declare @HistTagname as varchar(50)
declare @AlarmStatus as varchar(10)
declare @Node as varchar(8)
declare @StartDt as varchar(30)
declare @EndDt as varchar(30)
declare @queryDt as varchar(30)
SET @iHistServer = 'iHistMY_SERVER'
SET @Node = 'MY_SCADA'
SET @Tagname = 'Simulation00001'
SET @HistTagname = 'MY_SERVER.' + @Tagname
SET @AlarmStatus = 'HIHI'
SET @queryDt= DATEADD(day, -1, CURRENT_TIMESTAMP)
SET @EndDt = (SELECT TOP 1 DateTimeLast FROM AlarmODBC WHERE AlarmStatus = @AlarmStatus AND Node = @Node and Tagname =
SET @StartDt = DATEADD(hour, -1, @EndDt)
set @var1 = 'SELECT * FROM OPENQUERY 
('+ @iHistServer +',''SET StartTime="'+ @StartDt +'", EndTime="'+ @Enddt +'"
SELECT Tagname, TimeStamp, Value, Quality FROM ihRawData WHERE TagName = '+ @HistTagname +''')' exec (@var1)
Example 2: Access Linked Server Data With a Stored Procedure

A stored procedure can insulate callers from the details of linked server processing and can improve maintainability by localizing the linked server code.

This example procedure interfaces with the alarm ODBC table to get the last alarm time for a specified tag in the past 24 hours. It then uses this time to retrieve data for the tag from 1 hour before to the time the alarm occurred via a linked server to the Historian server.

The input parameters are the linked Historian server name, tag name, alarm status, and SCADA node name the alarm was created on. This example uses a sim tag in the Historian database rather than setting up a collector to an iFIX SCADA node. An iFIX tag name would probably need to be concatenated with the node and field (node.tagname.fieldname).

This is the command used to execute the stored procedure:
EXEC alarmhist 'iHistMY_SERVER', 'simulation00001', 'HIHI', 'MY_SCADA'

The first time you create the procedure in Enterprise Manager, you must include the following two lines before the create procedure command to avoid an error:

SET ANSI_NULLS ON
GO
(@iHistServer varchar(10),
@Tagname varchar(40),
@AlarmStatus varchar(10),
@Node varchar(8))
AS
declare @var1 as varchar(400)
declare @HistTagname as varchar(50)
declare @StartDt as varchar(30)
declare @EndDt as varchar(30)
declare @queryDt as varchar(30)
declare @count as int
declare @CalculationMode as varchar(20)
SET @HistTagname = 'MY_SERVER.' + @Tagname
SET @queryDt= DATEADD(day, -1, CURRENT_TIMESTAMP)
SET @count = (SELECT COUNT(*) FROM AlarmODBC WHERE AlarmStatus = @AlarmStatus AND Node = @Node AND Tagname = @Tagname
If @count > 0
BEGIN
If @AlarmStatus = 'HIHI' or @AlarmStatus = 'HI'
BEGIN
SET @CalculationMode = 'Maximum'
END
ELSE
BEGIN
SET @CalculationMode = 'Minimum'
END
SET @EndDt = (SELECT TOP 1 DateTimeLast FROM AlarmODBC WHERE AlarmStatus = @AlarmStatus AND Node = @Node AND Tagname =
SET @StartDt = DATEADD(hour, -1, @EndDt)
SET @var1 = 'SELECT * FROM OPENQUERY
('+ @iHistServer +',''SET StartTime="'+ @StartDt +'",
EndTime="'+ @EndDt +'", IntervalMilliseconds=60000,
SamplingMode=Calculated,CalculationMode='+ @CalculationMode +'
SELECT Tagname, TimeStamp, Value, Quality FROM ihRawData WHERE TagName = '+ @HistTagname +''')'
print (@var1)
exec (@var1)
END 
GO

VisiconX

Using the Historian OLE DB Provider with VisiconX, you can do the following:

  • Use table or SQL queries.
  • Insert multiple controls into a picture to the same or different servers.
  • Pass a user name and password or be prompted when opening a picture.

To access the Historian OLE DB Provider from VisiconX, follow the steps outlined for OLE DB providers in the Using VisiconX electronic book.

After you follow the steps outlined in the Using VisiconX manual, you need to perform one other task with the FixUserPreferences.ini file in the Dynamics/Local folder. This additional step is necessary because the provider does not support asynchronous (ASYNC) executes. By updating one line in the FixUserPreferences.ini file, you can make all of your VisiconX controls use synchronous (SYNC) executes. See Updating FixUserPreferences.ini for details.

For more information on VisiconX and the SQL Wizard, refer to the VisiconX Using VisiconX electronic book.

Updating FixUserPreferences.ini

To use synchronous (SYNC) executes in VisiconX, follow these steps:
  1. Open FixUserPreferences.ini in the Dynamics/Local folder.
  2. Add the following lines to the end of the file:
    [VisiconX]
RUNASYNC=FALSE
  3. Save the file.
  4. Restart the iFIX WorkSpace.

Visual Basic and ADO

You can access the Historian OLE DB Provider as you would any other OLE DB Provider by using Microsoft ActiveX Data Objects (ADO). This approach is more generic than using the Historian SDK.

Asynchronous Connection Support

Visual Basic supports asynchronous (ASYNC) connections. You can open multiple ADO connections to the same data source from within a Visual Basic program. You are limited to one server per connection, and one username and password. A different user can make another connection to the same server, however, by using a different username and password.

Client-Side Cursor Recommendation

It is recommended that you use client-side cursors instead of server-side cursors in Visual Basic.

If you use a server-side cursor, the RowCount property on the recordset object will always be -1 instead of the actual row count.

Retrieve Milliseconds

The following snippet of code shows how you can use Visual Basic to retrieve milliseconds within timestamps.

Public Function Time_To_String_With_Milliseconds(TheTime As Double) As String
Dim Temp As String
Dim TimeFraction As Double
Dim Msc As Long
Dim TempTime As Date

On Error GoTo errc

If TheTime = 0 Then
Time_To_String_With_Milliseconds = ""
Exit Function
End If

TimeFraction = TheTime * 86400#
TimeFraction = TimeFraction - Fix(TimeFraction)

Msc = CLng(TimeFraction * 1000)

TempTime = TheTime - (TimeFraction / 86400#)
If Msc = 1000 Then
Msc = 0
TempTime = DateAdd("s", 1, TempTime)
End If

Time_To_String_With_Milliseconds = LCase(Format$(TempTime, "dd-mmm-yyyy hh:nn:ss") + "." + Format$(Msc, "000"))

errc:

End Function

Throttle Results With the ADO MaxRecords Property

The following example shows how you can use the MaxRecords Property in ADO to throttle the number of rows returned from your query.

SET rstTitles = New ADODB.Recordset
rstTitles.MaxRecords = 10
strSQLTitles = "SELECT Tagname FROM ihTags"
rstTitles.Open strSQLTitles, strCnxn, adOpenStatic, adLockReadOnly, adCmdText

Parameterized Queries in Visual Basic

The following example shows how you can use parameterized queries in your VB script.

Private Sub SampleParameterizedQuery()
    Dim ihConnectString As String
    Dim ihRecordSet     As ADODB.Recordset 
    Dim ihConnection    As ADODB.Connection
    Dim ihParameter     As ADODB.Parameter
    Dim ihCommand       As ADODB.Command

    'Set Up the Historian Connect String...
    Set ihConnectString = "Provider=ihOLEDB.iHistorian.1;User Id=;Password="

    'Create Our Other Objects...
    Set ihConnection = CreateObject("ADODB.Connection")
    Set ihRecordSet = CreateObject("ADODB.Recordset")
    Set ihCommand = CreateObject("ADODB.Command")

    'Open the Connection to the Historian Archiver...
    ihConnection.ConnectionString = ihConnectString
    ihConnection.Open
    'Set up the Command Object
    With ihCommand

        'Set the Active Connection to the Historian Connection Opened Above..
        .ActiveConnection = ihConnection

        'Set the Command Text to a Parameterized Sql Statement....
        .CommandText = "select * from ihTags where datatype = ?"

        'Set the Type of the Command...
        .CommandType = adCmdText

        'Refresh Our Parameter List...
        .Parameters.Refresh
    End With

    'Create a Single Parameter Object...
    Set ihParameter = ihCommand.CreateParameter("Temp", adChar, adParamInput, 100)
    'Set the Parameters Value...
    ihParameter.Value = "SingleFloat"
    'Add the Parameter to the Command Object...
    ihCommand.Parameters.Append ihParameter
    'Run the Command!
    Set ihRecordSet = ihCommand.Execute
End Sub

Oracle

You can import Historian data into Oracle by using an ADO program. A sample program is provided in the Historian/Samples/Oracle folder.

Use SQL WorkSheet to test that Oracle imported the data and created the tables properly.