Frequently Asked Questions

The following sections outline some of the most frequently asked questions when using the Historian OLE DB provider. These questions include:

How Are Historian Calculation Modes and SQL Aggregate Functions Different?

You can extract calculated data from Historian by setting the SamplingMode column to Calculated and the CalculationMode column to the desired calculation mode type. You can use SQL aggregate functions to perform a calculation on a set of values, possibly calculated data, for the same tag or different tags and return a single value.

For instance, when comparing multiple tags you could retrieve the minimum (MIN) value of each tag. By setting calculation modes, Historian Administrator only calculates the minimum for each tag over a given time period. By using aggregate functions, the Historian OLE DB provider calculates the minimum value across all tags (all rows in a table), in other words, the minimum of all minimum tag values.

How Are the ihTrend and ihRawData Tables Different?

Typically, you use the ihTrend table when you want to compare multiple tags at the same time. The OLE DB provider needs to synchronize all the returned data by time, so it takes more time to query the ihTrend table than to query the ihRawData table. You can retrieve multiple tags from the ihRawData table, but the tags are not synchronized.

Can I Run Multiple Applications Using the OLE DB provider?
Yes. For instance, you can use the OLE DB provider to access data using Crystal Reports and VisiconX at the same time.
Can I Retrieve Data From Multiple Servers?

Yes. The OLE DB provider can have connections to multiple servers at the same time. Each is regarded as a separate session.

You cannot mix multiple servers in the same SELECT statement, except indirectly in a linked server in Microsoft SQL Server. Crystal Reports allows you to create subreports inside of a report. Each report gets its own data source (which would be a Historian server) and its own SELECT query. However, the reports cannot share data. You can have multiple VisiconX data controls in one picture, each going to a different server.

For instance, say you run iFIX and Crystal Reports at the same time. From the VisiconX page, establish a connection to the Historian OLE DB provider and perform a query on Server1. Next, run a report from Crystal Reports connecting to the same provider, but with a connection to a different server, Server2. After you run the report and go back to the VisiconX page, you will notice that VisiconX is still connected to Server1. If you refresh the control, it uses the same settings and server as it did before. The provider maintains these two sessions separately, each with its own SET parameters.

So, in general, you can access multiple servers, but the data from each server remains independent. You must work with linked servers in Microsoft SQL Server to combine data from multiple servers.

What is a Session?

A session is defined as an OLE DB connection. You can run multiple server connections to the OLE DB provider. Each is regarded as a separate session.

You can have multiple sessions with multiple clients, such as Crystal Reports and iFIX. Multiple sessions between a client computer and a server computer count as one licensed session.

How Do the > and >= Operators Work With Timestamps?

The > and >= comparison operators, when used with timestamp, return the same values. For example, this SQL statement...

SELECT * FROM ihRawData WHERE tagname=simulation00001 AND
timestamp>='4/1/2001 01:50:00' AND
timestamp<='4/1/2001 04:00:00' AND
samplingmode=lab

...returns exactly the same first result as this statement:

SELECT * FROM ihRawData WHERE tagname=simulation00001 AND
timestamp > '4/1/2001 01:50:00' AND
timestamp <= '4/1/2001 04:00:00' AND samplingmode=lab

The first result is timestamped at 1:51:00.

How Do I Throttle Query Results?

The default maximum row count is 5,000. If you want to throttle the number of rows that you return in a single query, you can do one of the following:

  • Use the SET statement to specify the RowCount to a specific number of rows.
  • Use the TOP predicate to specify the top number or top percentage of rows that you want to return.
  • Use the MaxRecords property on the recordset object in ADO.
When Should I 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 or 64-bit). Use Excel with the Historian OLE DB provider when you want to perform advanced filtering, sorting, and joining of data. For other features that you might to perform with Excel and the Historian OLE DB provider, see Microsoft Excel.

Why Is the Raw Sample at the Start Time Not Returned?

Historian OLE DB provider does not return raw samples with timestamps that match the start time. If you want to include the start time, you need to set the start time to a time earlier than the first raw sample desired.

Note: This only applies to RawByTime sampling mode and not RawByNumber.

For example, if you want to return raw samples starting at 11/28/2001 18:25:00 you can use 1/28/2001 18:24:59 as the start time. For example, you would enter the following SQL command:

SELECT TimeStamp, Tagname, Value FROM ihRawData
WHERE (SamplingMode = 'RawByTime') AND
(TimeStamp >= {ts '11/28/2001 18:24:59'})
ORDER BY TimeStamp ASC

If your timestamps are using millisecond resolution, you can retrieve timestamps starting at 11/28/2001 18:24:59.999 to prevent any sample prior to 18:50:00 from being returned.

What Username and Password Is Used if Not Specified in the Connect String?

If you leave a username and password empty in the connect string, then the user that owns the process, usually the currently logged-in user, is passed to the archiver for validation. For example, this statement leaves the username and password empty:

ConnectionString="Provider=ihOLEDB.iHistorian.1;User Id=;Password="

This statement also leaves the username and password empty:

ConnectionString="Provider=ihOLEDB.iHistorian.1"

If you saved username and password information in Historian Administrator or the iFIX WorkSpace for connecting to that server, that information is not used by the OLE DB provider.

What Is an Array Tag?

Historian allows you to store a set of values with a single timestamp and single quality and then read the elements back individually or as an array.

On retrieval, if you specify only the tag name, then all elements are returned. If you want to retrieve only an element, you can specify <TagName>[n] where n is the element number you want to retrieve.

In an array tag:

  • The size of the array tag does not need to be configured. The Data Archiver will store the number of elements that were written.
  • The maximum number of elements that an array tag can store is 10000. If this limit is exceeded, Historian does not accept any further elements.
  • All calculation modes except TagStats are supported by array tags. The calculation mode is applied on array elements and not on the array. For example, if you do a minimum on a three-element array, this works like three individual tags. The minimum of element [0] over time is computed and returned as the minimum of element [0]. The Data Archiver does not compute the minimum of element [0], [1], [2] at a single point in time and return that as the minimum of the array.
  • When a normal tag is converted to an array tag, on data retrieval, the data of the normal tag cannot be retrieved.

You can query both an array tag and an element of the tag. Each element of the array tag will be displayed in a separate row and they all will have the same timestamp.

What Is a User-Defined Type?

Historian gives you the ability to create a new user-defined data type which includes multiple fields of any data type and then create Historian tags of that type. All the regular tag operations can be performed on this tag. You can perform raw and calculated queries on the collected data.

What Is Not Supported?

A frequently asked question that may also relate to troubleshooting is what functions are not supported by Historian OLE DB provider. Some of these unsupported items include:

  • Concatenation in SQL statements. For example, this syntax does not work:
    SELECT * FROM ihtags WHERE tagname= "MY_SERVER." + ihtags.Tagname
  • Calculation in SQL statements. For example, this syntax does not work:
    SELECT * FROM ihtags WHERE ihrawdata.value * 2 > ihtags.LoEngineeringUnits
  • SQL inserts, updates, deletes, or commits.
  • Ordering by columns not specified in the SELECT statement.
  • The semicolon (;) as a separator between SET and SELECT statements (which is commonly used in DTS and Oracle). Only a space or line break is necessary.
  • Nested SELECT statements.
  • The UCASE macro or other similar SQL syntax.
  • ASYNC executes in ADO and Visual Basic.
  • Bookmarks in ADO and Visual Basic.
  • Table creation in SQL.
  • The UNION statement in SQL.
  • The HAVING clause in a SELECT statement.
  • Using comments in a query.
  • The DISTINCT clause in aggregate functions. For example, this syntax does not work:
    SELECT Topic, count(DISTINCT *), sum(DISTINCT messagenumber), avg(DISTINCT messagenumber) FROM ihmessages GROUP BY topic ORDER BY Topic
  • A literal on the left side of a comparison operator. SQL-92 standards support this feature, but GE Intelligent Platforms does not currently support it. For example, this syntax does not work:
    SELECT DISTINCT tagname FROM ihRawData WHERE 50>Value
  • Analysis of the ihTrend table in Crystal Reports or the Microsoft SQL Server DTS application.
  • Command or connect timeouts (Connection.ConnectTimeout, Connection.CommandTimeout, or Command.CommandTimeout) in Visual Basic. For example, this syntax does not work:
    SET adoConn = New ADODB.Connection
adoConn.ConnectionString = "Provider=ihOLEDB.iHistorian.1;User Id=;Password="
adoConn.ConnectionTimeout = 5 ' does nothing
adoConn.CommandTimeout = 5 ' does nothing
SET cmdTestTimer = New ADODB.Command
SET cmdTestTimer.ActiveConnection = adoConn
cmdTestTimer.CommandText = "SELECT * FROM ihtags"
cmdTestTimer.CommandType = adCmdText
cmdTestTimer.CommandTimeout = 15 ' does nothing