SQL Query Engine Stored Procedures

This topic lists every stored procedure that the SQL Query Engine registers in SQL Server, with its parameters, its result set, and a working example. For query patterns that combine these procedures, see Querying Historical Data with the SQL Query Engine.

The stored procedures are registered in the master system database. In SQL Server Management Studio, you can find them in Object Explorer under System Databases > master > Programmability > Stored Procedures. All of them are prefixed with QE_.

Common Parameters

The procedures share a small set of parameters. You can pass parameters by name, as in the examples below, or by position in the order shown in each procedure's signature.

Parameter	Type	Description
`@startTimestamp`	datetime2	Start of the time range to read. Timestamps are interpreted as UTC unless the SQL Query Engine is configured otherwise.
`@endTimestamp`	datetime2	End of the time range to read.
`@resamplingInterval`	nvarchar	Length of each interval that aggregated data is divided into. Pass either a time span in the form d.hh:mm:ss.fffffff (for example, 00:01:00 for one minute or 1.00:00:00 for one day) or a whole number of milliseconds (for example, 3600000 for one hour).
`@pointName`	nvarchar	The Data Historian point to read, as returned in the PointName column of `QE_GetAllPointNames` (for example, \Signals:Sine).
`@username`	nvarchar	(Optional) GENESIS Security user name. Defaults to `NULL`.
`@password`	nvarchar	(Optional) Password of the GENESIS Security user. Defaults to `NULL`.

Every procedure accepts @username and @password as its last two parameters. You can omit them when GENESIS Security is disabled, but they are required when it is enabled. For details and examples, see Using the SQL Query Engine with Security Enabled.

Status Columns

The procedures return a status code from Data Historian with every value. The raw-data procedures return it in the Quality column. The aggregate procedures return one status per aggregate: a numeric Status column and, where present, a Status_Str column holding the same status as text. A status code of 0 means Good, so filtering on Quality = 0 or MIN_Status = 0 keeps only the values that Data Historian reports as reliable. For the full set of codes and their meanings, see Data Historian Status Codes.

QE_GetAllPointNames

Returns a list of all Data Historian points and their metadata. Use this procedure to discover the point names to pass to the data query procedures.

QE_GetAllPointNames @username, @password

Parameters

None, other than the optional @username and @password.

Output Columns

Column	Type	Description
Path	nvarchar	Full path of the point in the Data Historian configuration, in the form `Folder\Point`.
DisplayName	nvarchar	Display name of the point.
Description	nvarchar	Description of the point, if one is configured.
PointName	nvarchar	The point name to pass as `@pointName` to the data query procedures.
EngineeringUnits	nvarchar	Engineering units of the point value (for example, °C, bar, or rpm).
RangeLow	float	The configured lower bound of the point's value range.
RangeHigh	float	The configured upper bound of the point's value range.
DataType	int	Numeric identifier of the point's data type.
DataTypeName	nvarchar	Name of the point's data type (for example, Double, Int32, or Boolean).
Timestamp	datetime2	Timestamp of the record.

Example—list every point:

EXEC QE_GetAllPointNames

Filtered Variants

Three variants return the same columns for a subset of points, selected by the Path column. Each takes one filter parameter followed by the optional @username and @password.

Procedure	Parameter	Returns
`QE_GetAllPointNamesContains`	`@pathContains` nvarchar	Points whose path contains the specified text.
`QE_GetAllPointNamesNotContains`	`@pathNotContains` nvarchar	Points whose path does not contain the specified text.
`QE_GetAllPointNamesEquals`	`@pathEquals` nvarchar	Points whose path exactly matches the specified text.

Example—list the points under the Signals folder:

EXEC QE_GetAllPointNamesContains @pathContains = N'Signals'

QE_GetHistoryRawModified

Returns the raw samples that Data Historian logged for one or more points over a time range, one row per sample. This procedure returns the value as text. When you know the data type of the point, use one of the typed variants listed below, which return the value in the matching SQL Server type.

QE_GetHistoryRawModified @startTimestamp, @endTimestamp, @pointName, @username, @password

Parameters

Parameter	Type	Description
`@startTimestamp`	datetime2	Start of the time range.
`@endTimestamp`	datetime2	End of the time range.
`@pointName`	nvarchar	The point to read. To read several points in one call, pass their names separated by commas (for example, `\Signals:Sine,\Signals:Ramp`).

Output Columns

Column	Type	Description
PointName	nvarchar	Name of the point the sample belongs to.
Timestamp	datetime2	Timestamp of the logged sample.
Quality	int	Data Historian status code of the sample. 0 means Good. Learn More
Value	varies	The logged value; `nvarchar` for `QE_GetHistoryRawModified`. See the table below for the typed variants.

Example—read the raw samples of one point over five minutes:

EXEC QE_GetHistoryRawModified @startTimestamp = '2026-01-01 10:00:00', @endTimestamp = '2026-01-01 10:05:00', @pointName = N'\Signals:Sine'

Typed Variants

The typed variants take the same parameters and return the same columns, with Value in the type shown.

Procedure	Value Type
`QE_GetHistoryRawModified_Boolean`	bit
`QE_GetHistoryRawModified_Byte`	tinyint
`QE_GetHistoryRawModified_SByte`	smallint
`QE_GetHistoryRawModified_Int16`	smallint
`QE_GetHistoryRawModified_Int32`	int
`QE_GetHistoryRawModified_Int64`	bigint
`QE_GetHistoryRawModified_UInt16`	int
`QE_GetHistoryRawModified_UInt32`	bigint
`QE_GetHistoryRawModified_UInt64`	decimal(20,0)
`QE_GetHistoryRawModified_Single`	real
`QE_GetHistoryRawModified_Double`	float
`QE_GetHistoryRawModified_DateTime`	datetime2

QE_GetHdaAnalog

Returns aggregated (processed) historical data for one numeric point. The procedure divides the time range into intervals of the length given by @resamplingInterval and returns one row per interval. Each row holds all of the aggregates listed in the table below at once; there is no parameter to select an aggregate. To work with a single aggregate, select its columns from the result set.

QE_GetHdaAnalog @startTimestamp, @endTimestamp, @resamplingInterval, @pointName, @username, @password

Parameters

Parameter	Type	Description
`@startTimestamp`	datetime2	Start of the time range.
`@endTimestamp`	datetime2	End of the time range.
`@resamplingInterval`	nvarchar	Length of each interval as a time span (00:01:00) or a number of milliseconds (60000). Learn more
`@pointName`	nvarchar	The numeric point to read.

Output Columns

The result set has a Timestamp column followed by a group of columns for each aggregate. Each group is named with the aggregate's prefix from the table below.

Column	Type	Description
Timestamp	datetime2	Start of the interval.
`<prefix>_Value`	float	The aggregate value for the interval. `COUNT_Value` is an int.
`<prefix>_Status`	int	Data Historian status code of the aggregate value. 0 means Good. Learn More
`<prefix>_Status_Str`	nvarchar	The Status column value as text.
`<prefix>_Timestamp`	datetime2	For `MIN`, `MAX`, and `LAST` only: the timestamp of the sample that supplied the value.

Aggregates Returned

Column Prefix	OPC HDA Aggregate	Description
`MIN`	MinimumActualTime	Lowest value in the interval. `MIN_Timestamp` gives the time it was logged.
`MAX`	MaximumActualTime	Highest value in the interval. `MAX_Timestamp` gives the time it was logged.
`AVG`	Average	Arithmetic mean of the samples in the interval.
`TIME_AVG`	TimeAverage	Time-weighted average, which accounts for how long each value was held. More representative than `AVG` when samples are irregular.
`TOTAL_TIME_AVG`	Total	Time-weighted total (integral) of the value over the interval. Useful for flow-rate or energy points.
`INTERPOLATIVE`	Interpolative	Value interpolated at the start of the interval from the surrounding samples.
`LAST`	End	Last value in the interval. `LAST_Timestamp` gives the time it was logged.
`DELTA`	Delta	Difference between the last and the first value in the interval.
`RANGE`	Range	Difference between the highest and the lowest value in the interval.
`TOTAL`	Sum	Sum of the sample values in the interval.
`COUNT`	Count	Number of samples in the interval.

Example—read the aggregates of one point for a day, in one-hour intervals:

EXEC QE_GetHdaAnalog @startTimestamp = '2026-01-01 00:00:00', @endTimestamp = '2026-01-02 00:00:00', @resamplingInterval = '01:00:00', @pointName = N'\Signals:Sine'

Example—keep only the hourly minimum, maximum, and average by loading the result into a table and selecting the columns you need:

CREATE TABLE #Hourly ( [Timestamp] datetime2, MIN_Value float, MIN_Status int, MIN_Status_Str nvarchar(2048), MIN_Timestamp datetime2, MAX_Value float, MAX_Status int, MAX_Status_Str nvarchar(2048), MAX_Timestamp datetime2, AVG_Value float, AVG_Status int, AVG_Status_Str nvarchar(2048), TIME_AVG_Value float, TIME_AVG_Status int, TIME_AVG_Status_Str nvarchar(2048), TOTAL_TIME_AVG_Value float, TOTAL_TIME_AVG_Status int, TOTAL_TIME_AVG_Status_Str nvarchar(2048), INTERPOLATIVE_Value float, INTERPOLATIVE_Status int, INTERPOLATIVE_Status_Str nvarchar(2048), LAST_Value float, LAST_Status int, LAST_Status_Str nvarchar(2048), LAST_Timestamp datetime2, DELTA_Value float, DELTA_Status int, DELTA_Status_Str nvarchar(2048), RANGE_Value float, RANGE_Status int, RANGE_Status_Str nvarchar(2048), TOTAL_Value float, TOTAL_Status int, TOTAL_Status_Str nvarchar(2048), COUNT_Value int, COUNT_Status int, COUNT_Status_Str nvarchar(2048) ) INSERT INTO #Hourly EXEC QE_GetHdaAnalog '2026-01-01 00:00:00', '2026-01-02 00:00:00', '3600000', N'\Signals:Sine' SELECT [Timestamp], MIN_Value, MAX_Value, AVG_Value FROM #Hourly WHERE MIN_Status = 0 AND MAX_Status = 0 AND AVG_Status = 0 ORDER BY [Timestamp]

QE_GetHdaAnalog5

Returns the same aggregates as QE_GetHdaAnalog for up to five numeric points in a single call, with one row per interval. Use it instead of calling QE_GetHdaAnalog five times when a report needs the same aggregates for several points over the same time range and interval.

QE_GetHdaAnalog5 @startTimestamp, @endTimestamp, @resamplingInterval, @pointName1, @pointName2, @pointName3, @pointName4, @pointName5, @username, @password

Parameters

Parameter	Type	Description
`@startTimestamp`	datetime2	Start of the time range.
`@endTimestamp`	datetime2	End of the time range.
`@resamplingInterval`	nvarchar	Length of each interval as a time span or a number of milliseconds.
`@pointName1` to `@pointName5`	nvarchar	The five numeric points to read. All five are required.

Output Columns

The result set has a Timestamp column followed by the aggregate columns for point 1, then point 2, and so on. The columns are named as for QE_GetHdaAnalog with the point's position appended: MIN_Value_1, MIN_Status_1, MIN_Timestamp_1, MAX_Value_1, and so on through COUNT_Status_5. The _Status_Str columns are not included.

Example—read the aggregates of five points in 20-second intervals:

EXEC QE_GetHdaAnalog5 @startTimestamp = '2026-01-01 10:00:00', @endTimestamp = '2026-01-01 10:05:00', @resamplingInterval = '00:00:20', @pointName1 = N'\Signals:Sine', @pointName2 = N'\Signals:Ramp', @pointName3 = N'\Signals:Random', @pointName4 = N'\Signals:SineFast', @pointName5 = N'\Signals:RampFast'

QE_GetHdaBool

Returns aggregated historical data for one Boolean point, one row per interval. Use it to analyze state data such as equipment running status. Each row holds all of the aggregates listed below.

QE_GetHdaBool @startTimestamp, @endTimestamp, @resamplingInterval, @pointName, @username, @password

Parameters

The same as QE_GetHdaAnalog: @startTimestamp, @endTimestamp, @resamplingInterval, and @pointName, where the point is a Boolean point.

Output Columns

The result set has a Timestamp column (start of the interval) followed by <prefix>_Value, <prefix>_Status, and <prefix>_Status_Str columns for each aggregate, as for QE_GetHdaAnalog.

Column Prefix	OPC HDA Aggregate	Value Type	Description
`DURATION_IN_STATE_0`	DurationInStateZero	float	How long the point was false (0) during the interval.
`DURATION_IN_STATE_1`	DurationInStateNonZero	float	How long the point was true (non-zero) during the interval.
`NUMBER_OF_TRANSITIONS`	NumberOfTransitions	int	How many times the point changed state during the interval.
`COUNT`	Count	int	Number of samples in the interval.

Example—read the hourly state summary of a running indicator:

EXEC QE_GetHdaBool @startTimestamp = '2026-01-01 00:00:00', @endTimestamp = '2026-01-02 00:00:00', @resamplingInterval = '01:00:00', @pointName = N'\Signals:Running'

QE_GetHdaBool5

Returns the same aggregates as QE_GetHdaBool for up to five Boolean points in a single call, with one row per interval.

QE_GetHdaBool5 @startTimestamp, @endTimestamp, @resamplingInterval, @pointName1, @pointName2, @pointName3, @pointName4, @pointName5, @username, @password

Parameters

The same as QE_GetHdaAnalog5: @startTimestamp, @endTimestamp, @resamplingInterval, and @pointName1 to @pointName5, where the points are Boolean points. All five are required.

Output Columns

The result set has a Timestamp column followed by the aggregate columns for point 1, then point 2, and so on, named as for QE_GetHdaBool with the point's position appended: DURATION_IN_STATE_0_Value_1, DURATION_IN_STATE_0_Status_1, and so on through COUNT_Status_5. The _Status_Str columns are not included.