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”:
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:
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
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
select count(*) from CONTACTS@Outlook where companyname like '%B.V.%'
which returns approximately half of the contacts:
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
- Meetings (message class
- Emails (message class
- Notes (message class
- Journal Entries (message class
- Tasks (message class
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
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