Troubleshooting and Frequently Asked Questions

Troubleshooting

The following sections outline what to do if the following problems occur:

The sections that follow the answers to this list describe frequently asked questions. These answers may help you when you are first configuring and using the Historian OLE DB provider.

Cannot Connect With the Historian Interactive SQL Application

About this task

When the OLE DB provider connects to the archiver, a connection message is generated and logged to the archiver messages list. If you are having problems connecting with the Historian Interactive SQL application (ihSQL.exe), you will either not see a connection message or see a connection error instead.

If you suspect that you are having problems connecting to the archiver, follow these steps:

Procedure

  1. Open Historian Administrator.
  2. Select Messages.
    The message fields appear in the main window.
  3. In the Priority group box, select the All option.
  4. In the Topic drop-down list, select All Topics.
  5. Select Search.
    A list of messages appears on the right side of the window.
  6. Scroll through the list of connection messages and look for any missing connections or connection errors.
    Connections denied due to security display the user name passed to the archiver. For example, the message would be similar to this:
    Unknown(\kmckenna) failed login at 03/01/2002 04:30:58.415 PM.

Cannot Log Into the Historian Interactive SQL Application

When you use ihSQL.exe for the first time, 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.

Cannot Get Historian OLE DB provider Data

About this task

If you cannot get data and you suspect there is a security problem with Historian, follow these steps to confirm that the Historian OLE DB provider is working:

Procedure

  1. Open the Historian Interactive SQL application and connect to the OLE DB provider.
  2. Enter the following command:
    SELECT * FROM ihQuerySettings
  3. Select the Execute Query button.
  4. Confirm that data appears in the bottom half of the window:
    • If one row of data returns, then the provider is installed and working correctly, but you may have security problems between the provider and the server. You must use a valid Historian username and password.
    • If no rows return, then there is a connection problem between the client and the OLE DB provider.

Results

The ihQuerySettings data is internal to the OLE DB provider and does not use any Historian security. Browsing the tables and columns also is unaffected by Historian security and is another way to confirm the connection between the client and provider.

Samples Do Not Run

If you follow the recommended installation procedures, you should not have any difficulty in running the sample reports. If you do encounter any problems, they are likely to relate to the locations of files.

For example, if you are using Crystal Reports, check that you changed the server name. If the server name is incorrect, the data links will not update correctly. See Changing the Server Name for directions on how to change it.

Time Zones Do Not Work

If you are using Windows 9x, times zones are not supported on this operating system. Returned data displays the client time zone.

If you are expecting a server or explicit bias time zone and a client time zone displays, check the defaults in the ihQuerySettings table. By default, the TimeZone column is set to Client. See Supported SET Statement Syntax for more information on setting defaults using a SET statement, or see WHERE Clauses for information on specifying a time zone in the SELECT statement.

Cannot Get String Data From the ihRawData Table

The Historian OLE DB provider, by default, does not return string data types in the ihRawData table. This is because the default SamplingMode value is Calculated. You have to change the SamplingMode value to Interpolated using the SET statement or a WHERE clause.

For example, this query does not return interpolated data:

SELECT * FROM ihRawData
WHERE tagname = simulationstring00001

However, this query does:

SELECT * FROM ihRawData
WHERE tagname = simulationstring00001 AND
samplingmode = interpolated

And so does this query:

SET samplingmode=interpolated
SELECT * FROM ihRawData
WHERE tagname = simulationstring00001

Timestamps Include Only the Previous Two Hours

By default, the data returned only includes data from two hours prior to the execution of the query. If you want to change the time frame of the data query, you need to specify a start and end time in a SET statement, or use a WHERE clause to specify a date and time period.

Row Count Less Than Expected

By default, all queries return up to a maximum of 5,000 rows. If you want to change the maximum number of rows returned, you can specify another RowCount value in a SET statement, or use the TOP predicate in your SELECT statement.

If you specify RowCount=0 in the SET statement, the RowCount limit is disabled. However, the RowCount is not actually unlimited. It can be constrained by other factors such as the time interval, or by using the TOP predicate in your SELECT statement.

Linked Server Not Working

Check that you selected the Select the Level Zero Only and Allow in Process options in the Provider Options window. You may have forgotten to set them when you were creating your linked server. These are the only two options that should be selected.

SET Not Applied to SELECT When Using a Linked Server

Make sure that the SET and SELECT statements are combined in the same query. If you open the connection and only perform the SET, as shown below, the SET parameters only get applied for the duration of the connection.

SELECT * FROM OPENQUERY(linkedserver, 'SET SamplingMode=interpolated')

The SamplingMode option in the previous example does not get applied to the next OPENQUERY that you perform with a SELECT statement. The SET statement only gets applied to the query if it is included with the SELECT statement. See Use OPENQUERY to Access a Linked Server for examples of how to include the SET statement with a SELECT statement.

Client Crashes When Using Historian OLE DB provider

Ensure that your client is initializing COM in Apartment threaded mode.

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