wwtools/histdb/01-overview.md

Overview — connecting and querying

What's actually behind a Historian query

   client (SSMS / app / aalog-equivalent)
         │  T-SQL
         ▼
   Microsoft SQL Server   ◄── normal SQL Server tables (Tag, AnalogTag, EngineeringUnit, etc.)
         │  OLE DB
         ▼
   INSQL linked server   ◄── this is the AVEVA Historian OLE DB Provider, name "INSQL"
         │  HCAL
         ▼
   Retrieval subsystem   ◄── reads .aaLGX history blocks on disk, returns rowsets

Configuration and event metadata live in real SQL Server tables. Time-series ("history") data lives in binary history blocks on disk and is presented through INSQL as if it were a SQL table. That's why every history query goes through the linked server.

The extension tables

The "virtual" tables exposed by INSQL:

Linked-server path	Local view	Purpose
INSQL.Runtime.dbo.History	History	Tall format. One row per (tag, timestamp). The default for everything historical.
INSQL.Runtime.dbo.Live	Live	Most recent value per tag. Fast snapshot.
INSQL.Runtime.dbo.WideHistory	WideHistory	Wide format — one row per timestamp, one column per tag. Must be queried via OPENQUERY. Up to 1024 columns.
INSQL.Runtime.dbo.AnalogSummaryHistory	AnalogSummaryHistory	Pre-summarized analog data.
INSQL.Runtime.dbo.StateSummaryHistory	StateSummaryHistory	Pre-summarized state-tag data.
INSQL.Runtime.dbo.HistoryBlock	HistoryBlock	Metadata about the on-disk blocks (no WHERE required).
INSQL.Runtime.dbo.Events	Events	Alarm/event records. See 06-alarms-events.md.

Legacy backward-compatibility tables (still supported, not preferred): AnalogHistory, DiscreteHistory, StringHistory, AnalogLive, DiscreteLive, StringLive, plus v_AlarmHistory, v_AlarmHistory2, v_EventHistory, v_AlarmEventHistoryInternal2, v_AlarmEventHistory2. Use the unified History / Live / Events tables for new work.

Linking INSQL to SQL Server

Done automatically by the Historian installer. If you ever need to redo it manually:

EXEC sp_addlinkedserver  @server='INSQL', @srvproduct='', @provider='INSQL';
EXEC sp_serveroption     'INSQL', 'collation compatible', true;
EXEC sp_addlinkedsrvlogin 'INSQL', 'TRUE', NULL, NULL, NULL;

-- Alias (used when joining the legacy analog and discrete tables in one query):
EXEC sp_addlinkedserver  @server='INSQLD', @srvproduct='', @provider='INSQL';
EXEC sp_serveroption     'INSQLD', 'collation compatible', true;
EXEC sp_addlinkedsrvlogin 'INSQLD', 'TRUE', NULL, NULL, NULL;

The INSQL OLE DB provider cannot run standalone — it must be hosted by SQL Server.

Four ways to issue a SELECT

You'll see all four in the wild; each has a niche.

1. Four-part naming (preferred for History, Live, Events)

SELECT *
FROM INSQL.Runtime.dbo.History
WHERE TagName = 'SysTimeSec'
  AND DateTime >= '2001-09-12 12:59:00'
  AND DateTime <= '2001-09-12 13:00:00';

The four parts: linked_server.catalog.schema.object_name. For INSQL the catalog is always Runtime and the schema is always dbo.

2. Provider view (sugar over four-part)

Each extension table has a SQL Server view of the same name — History, Live, Events, etc.

SELECT *
FROM History
WHERE TagName = 'SysTimeSec'
  AND DateTime >= '2001-09-12 12:59:00'
  AND DateTime <= '2001-09-12 13:00:00';

Backward-compatibility views use v_<name>.

3. OPENQUERY (required for WideHistory, useful for pass-through)

SELECT * FROM OPENQUERY(INSQL, '
  SELECT DateTime, SysTimeSec, ReactTemp
  FROM WideHistory
  WHERE DateTime >= "2001-09-12 12:59:00"
    AND DateTime <= "2001-09-12 13:00:00"
');

The body is sent verbatim to INSQL. Quoting is double quotes inside, single quote around the whole body. Hard caps and quirks:

• 8000-character body limit.
• DateTime format must be yyyy-mm-dd hh:mm:ss.fff.
• ORDER BY and GROUP BY don't work inside the body (SQL Server doesn't see them) — apply them outside.
• Joins inside the body are unsupported. Place them outside.
• Variables (@foo) cannot appear inside the body.

4. OPENROWSET (one-off ad-hoc)

SELECT * FROM OPENROWSET('INSQL', '', '
  SELECT DateTime, Quality, QualityDetail, Value
  FROM History
  WHERE TagName IN ("SysTimeSec")
    AND DateTime >= "2001-09-12 12:59:00"
    AND DateTime <= "2001-09-12 13:00:00"
');

Requires that ad-hoc distributed queries be enabled on the SQL Server (sp_configure 'Ad Hoc Distributed Queries', 1). Generally not recommended in production code.

When to use which style

Scenario	Style
Tall queries against History, Live, Events	Four-part or view
WideHistory (one column per tag)	OPENQUERY (mandatory)
Heavy filtering you want SQL Server to optimize	Four-part (the optimizer can see the predicates)
Forcing a predicate down to INSQL that the optimizer is dropping	OPENQUERY (literal pass-through)
Joining a SQL Server table against a Historian extension table	Four-part, with INNER REMOTE JOIN
One-off scripts on a server with ad-hoc DQ enabled	OPENROWSET

Date format gotcha

For four-part queries on non-English SQL Server installations (French, German, etc.), the default datetime format may be yyyy-dd-mm hh:mm:ss.fff instead of yyyy-mm-dd .... Use CONVERT(...) explicitly when in doubt — output formatting also depends on the OS regional settings.

For OPENQUERY, always use yyyy-mm-dd hh:mm:ss.fff regardless of locale.

"No WHERE clause" error

If you get:

Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'INSQL'.
[OLE/DB provider returned message: InSQL did not receive a WHERE clause from SQL Server. ...]

…the SQL Server query optimizer truncated the WHERE clause as "superfluous." Workaround: add a redundant condition such as AND wwRetrievalMode = 'delta'. See 02-syntax-limits.md.

Next

• 02-syntax-limits.md — what works and what doesn't, plus the wwXxx extensions overview.
• 03-retrieval-modes.md — pick a wwRetrievalMode.
• 05-query-recipes.md — practical patterns.