Moving Data to Outlook from SQL
January 8, 2001
Calendar Information and SQL
The nice thing about writing SQL server-related articles is that nearly any technology initiative can be tied back to databases. A variety of articles can be created covering numerous topics based on this type of enterprise database server.
A recent project offered an opportunity to access event information stored on SQL server and import it to Outlook. The two tables for the event information are shown below:
These tables are used to support events occurring at a trade show and contain three distinct types of events: attending educational sessions, visiting exhibitor booths, and scheduling personal events.
Educational sessions and personal events are treated as appointments since they have specific start and end times. However, trade show attendees tend to visit exhibitor booths in numeric order throughout the three-day event. Without a specific start and end time, visiting exhibitor booths is better treated as a task in Outlook.
The intent is for users to create their own individual daily planner (including all three event types) via the Web. Without much control over the user's calendar package, the first version used the tables to organize the information to create printable planner pages. This application was well received by both external visitors as well as internal staff members. However, with internal staff being accustomed to using Outlook 2000 to manage calendar information and several having PDA's synched with Outlook, it made sense to develop a process to move the event information into Outlook.
Given the Outlook object model and the events database, the project can be
For a long time, scripting routines in Windows had to be done using batch files. With Windows 2000 the WSH is built into the operating system allowing the creation of scripts using VBScript or Jscript. These scripts have the ability to create objects and interact with Microsoft Office Objects. For this project, a script was created to access the events table to add tasks and appointments to an Outlook calendar and task list.
There is enough code in this example to demonstrate the power of the Outlook object model without becoming overwhelmed. Listing 1 shows the stored procedure used on the SQL server. The stored procedure takes a UserID and finds all the events that correspond to the particular user. Other than trimming some spaces from two of the fields there is no other manipulation occurring in the stored procedure.
Listing 2 shows the VB Script file used to add the events to Outlook. [Both examples open in a new window. -Ed.]
As with any project, process two leads to process three. Further research revealed another feature of Outlook that can be exploited from the web, the vCalendar protocol. Exploring vCalendar and vCards will be covered in upcoming articles. The following links can also provide information about this alternative method. Adding information from SQL directly to Outlook for a web client will save some resources on the SQL server while providing the user with greater event control.
vCalendar & vCard Links