Invantive SQL also supports queries on Outlook (Microsoft Windows only). It is even possible to write and update in Microsoft Outlook using Invantive SQL.
This topic will introduce some some query techniques to access Microsoft Outlook using SQL. It will also explain how to copy data from one or many users on Exchange or GMail into a relational database such as Microsoft SQL Server or the in-memory driver.
The samples have been executed using Invantive Query Tool, but will also run using for instance Invantive Control for Excel. For all samples, a connection to any database will suffice since the Outlook SQL driver is a service-provider that is always available. Most people use the “Dummy” database in the group “Various”:
An overview of the tables available with the Invantive SQL Outlook driver can be found in the data model:
Concept of Outlook
Microsoft Outlook provides a user interface and logic around servers storing email messages, calendars, contacts and tasks. Typically Outlook is used in combination with Microsoft Exchange (on Azure or on-premise) and/or GMail, but the information on this topic can be also applied to other mail server platforms.
Microsoft Outlook needs to be running on the same device as Invantive SQL to run queries on the data available through Outlook. However, Invantive SQL does not need to run in the same process as with Invantive Business for Outlook. You can just as well run queries on Outlook from a separately running Invantive Query Tool or Invantive Data Hub. Communication between Outlook and Invantive SQL occurs using Windows communication protocols, whereas Outlook routes requests to the linked accounts on Exchange and/or GMail.
Outlook Profile and Session
Outlook runs using a profile. A profile contains a complete mail environment, including accounts and settings. Multiple Outlook profiles can be defined using Windows. The user is asked to choose a profile when multiple Outlook profiles exist.
Once Outlook is running with a specific profile, a session is created for the user. The session properties can be queried using the SQL statement:
select *
from SESSIONS@Outlook
with results like:
Outlook Stores
The backing mail servers are available through so-called “stores”. The stores can be queried also; the full list of Outlook tables available for use with SQL is:
One of mine stores is the primary mailbox:
Each store has a unique ID, a very long alphanumeric text. This store ID is available through the column storeID. It can be used on most tables as a table function filter to query only a specific store. When not provided, all stores (and possibly folders) are queried, like here on Contacts:
A simple query on Contacts is to count the number of Outlook contacts:
select count(*)
from CONTACTS@Outlook
with results like:
All functionality of Invantive SQL is available, such as where clauses:
select count(*)
from CONTACTS@Outlook
where companyname like '%B.V.%'
which returns approximately half of the contacts:
Folders
Each store can have multiple “folders”. An Outlook “folder” stores items of solely a specific item type. Frequently found item types are (an overview of the item types is available from Microsoft):
- Contacts (message class
IPM.Contact) - Meetings (message class
IPM.Appointment) - Emails (message class
IPM.Note) - Notes (message class
IPM.StickyNote) - Journal Entries (message class
IPM.Activity) - Tasks (message class
IPM.Task)
Typically you will find maybe 10 stores in your Outlook table, each with maybe dozens of folders. So filtering mails and other item types on folder or store can be very efficient in terms of reducing the amount of data retrieved and sieved through.
Query your Team’s Calendars
A user that has access to all calendars of the team members will find all the team members’ calendars in Appointments.
A simple query will return the appointments of all team members, such as:
select subject
, start
, duration
, end
, creationtime
, lastmodificationtime
, size
from APPOINTMENTS@Outlook
where subject like '%Musical%'
order
by start
with results similar to:
Copy Team Member Calendar Items
These appointments can easily be copied to any platform on which write functionality is available, such as Microsoft SQL Server. The following statement will create a backup of the calendars on SQL Server:
create or replace table backup_appointments@sqlserver
as
select subject
, start
, duration
, end
, creationtime
, lastmodificationtime
, size
from APPOINTMENTS@Outlook
where subject like '%Musical%'
As a pre-requisite the data container alias sqlserver must connect to a SQL Server instance. An example is described on Copy Exact Online to SQL Server. Also, the SQL Server user must have create table privileges.
Query Calendar for Billing
The most common scenario of using the Invantive SQL Outlook driver is to generate input for the billing process. Similarly, text output can be generated using statements such as:
select subject
, start
, duration
, end
, creationtime
, lastmodificationtime
, size
from APPOINTMENTS@Outlook
where subject like '%Musical%'
for json auto
with JSON output:
But of course, Invantive Script can also be used like:
local export results as "c:\temp\hours.xlsx" format xlsx include headers
with results:
