# 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`](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:

```sql
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`)

```sql
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.

```sql
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)

```sql
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)

```sql
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`](02-syntax-limits.md#where-clause-anomalies).

## Next

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