Troubleshooting and Frequently Asked Questions

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.

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.

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.

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

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.

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.

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

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.

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

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, the 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.

For more information on SQL aggregate functions, refer to SQL Aggregate Functions.

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.

Yes. For instance, you can use the OLE DB Provider to access data using Crystal Reports and VisiconX at the same time.

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 screen, 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 screen, 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.

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.

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.

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.

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 When to Use Excel Instead of the Historian Excel Add-In.

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.

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.

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 the Historian Administrator or the iFIX WorkSpace for connecting to that server, that information is not used by the OLE DB Provider.

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.

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.

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