SQL Query Engine Stored Procedures

The SQL Query Engine registers the following stored procedures in your SQL Server database. All procedures read data from Data Historian and return standard result sets.

Authentication Parameters

In addition to the parameters listed for each procedure below, every procedure accepts two optional authentication parameters, @username and @password. These are used to authenticate as a GENESIS Security user and both default to NULL. 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.

The QUALITY Column

Every procedure that returns data includes a QUALITY column holding the Data Historian status code for the returned value. A status code of 0 means Good, so filtering for QUALITY = 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 historian tags (points) and their associated metadata. Use this procedure to discover available tags before constructing data queries.

Parameters

None.

Output Columns

Column	Type	Description
PATH	string	Hierarchical path of the tag in the Data Historian configuration.
DISPLAYNAME	string	Human-readable display name of the tag.
DESCRIPTION	string	Optional description of the tag.
POINTNAME	string	Unique tag identifier used in data query procedures.
ENGINEERINGUNITS	string	Engineering units label for the tag value (for example, °C, bar, rpm).
RANGELOW	double	The configured lower bound of the tag's expected value range.
RANGEHIGH	double	The configured upper bound of the tag's expected value range.
DATATYPE	ui2	Numeric OPC data type code for the tag value.
DATATYPENAME	string	Human-readable name of the tag data type (for example, Double, Int32, Boolean).
TIMESTAMP	timestamp	The timestamp of the most recently logged value for the tag.

QE_GetHistoryRawModified

Returns raw (unprocessed) historical samples for a specified tag and time range. Returns values as variant. Use a typed variant (listed below) when the tag data type is known, which may improve performance.

Parameters

Parameter	Type	Description
@TagName	nvarchar	The POINTNAME of the Data Historian tag to query.
@StartTime	datetime	Start of the query time range (inclusive).
@EndTime	datetime	End of the query time range (exclusive).

Output Columns

Column	Type	Description
TAGNAME	string	The tag identifier matching the @TagName input.
TIMESTAMP	timestamp	The timestamp of the logged sample.
QUALITY	ui4	Data Historian status code for the sample. A value of 0 indicates Good. Learn More
VALUE	variant	The logged value of the tag at the given timestamp.

Typed Variants

The following typed variants of QE_GetHistoryRawModified accept the same parameters but return VALUE in a specific .NET data type. Use these when the tag data type is known:

• QE_GetHistoryRawModified_Boolean

• QE_GetHistoryRawModified_Byte

• QE_GetHistoryRawModified_DateTime

• QE_GetHistoryRawModified_Double

• QE_GetHistoryRawModified_Int16

• QE_GetHistoryRawModified_Int32

• QE_GetHistoryRawModified_Int64

• QE_GetHistoryRawModified_SByte

• QE_GetHistoryRawModified_Single

• QE_GetHistoryRawModified_UInt16

• QE_GetHistoryRawModified_UInt32

• QE_GetHistoryRawModified_UInt64

QE_GetHdaAnalog

Returns processed (aggregated) historical data for a numeric tag, divided into equal-length time buckets. Each result row contains the aggregate value for one bucket. The aggregate type is specified per call.

Parameters

Parameter	Type	Description
@TagName	nvarchar	The POINTNAME of the historian tag to query.
@StartTime	datetime	Start of the query time range (inclusive).
@EndTime	datetime	End of the query time range (exclusive).
@Aggregate	nvarchar	The OPC HDA aggregate to compute. See the Supported Aggregate Types table below.
@BucketDuration	int	The length of each time bucket in seconds. For example, 3600 produces one result row per hour.

Output Columns

Column	Type	Description
TAGNAME	string	The tag identifier matching the @TagName input.
TIMESTAMP	timestamp	Start timestamp of the returned time bucket.
QUALITY	ui4	Data Historian status code for the aggregated result. A value of 0 indicates Good. Learn More
VALUE	double	The aggregate value for the time bucket.

Supported Aggregate Types

@Aggregate Value	OPC HDA Aggregate	Description
MIN	MinimumActualTime	Minimum value recorded in the bucket at its actual timestamp.
MAX	MaximumActualTime	Maximum value recorded in the bucket at its actual timestamp.
AVERAGE	Average	Arithmetic mean of all samples in the bucket.
TIME_AVERAGE	TimeAverage	Time-weighted average that accounts for the duration each value was held. More representative than a simple average when sample rates are irregular.
TOTALIZE_AVERAGE	Total	Totalized (integrated) value over the bucket period. Useful for flow-rate or energy consumption tags.
INTERPOLATIVE	Interpolative	Interpolated value at the start of each bucket, calculated from surrounding data points. Useful for reconstructing a regular time series from sparse data.

QE_GetHdaAnalog5

Returns five aggregate values simultaneously per time bucket for a numeric tag: minimum, maximum, average, time-weighted average, and total. Use this procedure instead of calling QE_GetHdaAnalog five times separately when all five statistics are needed for the same tag and time range.

Parameters

Accepts the same parameters as QE_GetHdaAnalog, except @Aggregate (the aggregate type is fixed and returns all five values).

Output Columns

Column	Type	Description
TAGNAME	string	The tag identifier matching the @TagName input.
TIMESTAMP	timestamp	Start timestamp of the returned time bucket.
QUALITY	ui4	Data Historian status code for the aggregated result. A value of 0 indicates Good. Learn More
VALUE_MIN	double	Minimum value in the time bucket.
VALUE_MAX	double	Maximum value in the time bucket.
VALUE_AVERAGE	double	Arithmetic average value in the time bucket.
VALUETIMEAVERAGE	double	Time-weighted average value in the time bucket.
VALUE_TOTAL	double	Totalized value in the time bucket.

QE_GetHdaBool

Returns aggregated historical data for a boolean tag, divided into equal-length time buckets. Use this procedure to analyze state data such as equipment running status or alarm active states.

Parameters

Accepts the same parameters as QE_GetHdaAnalog.

Output Columns

Column	Type	Description
TAGNAME	string	The tag identifier matching the @TagName input.
TIMESTAMP	timestamp	Start timestamp of the returned time bucket.
QUALITY	ui4	Data Historian status code for the aggregated result. A value of 0 indicates Good. Learn More
VALUE	boolean	Aggregate boolean value for the time bucket.

QE_GetHdaBool5

Returns five aggregate values simultaneously per time bucket for a boolean tag. Equivalent to QE_GetHdaAnalog5 but designed for boolean (true/false) data.

Parameters

Accepts the same parameters as QE_GetHdaAnalog5.

Output Columns

Column	Type	Description
TAGNAME	string	The tag identifier matching the input tag name parameter.
TIMESTAMP	timestamp	Start timestamp of the returned time bucket.
QUALITY	ui4	Data Historian status code for the aggregated result. A value of 0 indicates Good. Learn More
VALUE1 through VALUE5	boolean aggregate	Five aggregate value columns returned for the time bucket.