Moving Data to Outlook from SQL

Calendar Information and SQL

An Overview

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:

Click image for full size

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 Goal

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.

The Solution

Given the Outlook object model and the events database, the project can be
completed using Windows Scripting Host
(WSH).

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.

The Code

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.]

• Lines 1-15 declare the variables used in the script.
• Lines 17 and 18 set constants for the default folder
  types used by Outlook.
• Lines 20-24 instantiate the objects to be used by the main routines on the page.
  The subOpenConnection routine opens a connection to the database on the server.
• Lines 28-30 get the UserID from an e-mail address input by the user.

The e-mail address information is collected from the web using a login procedure.

• Lines 32-36 verify the UserID is valid.
• Lines 40-41 create the statement that needs to be sent to the server to
  get the event information based on a UserID and submit the statement.
• Lines 44-46 splice the information from the server and convert
  the information to date and time. Although the information from the SQL server
  is brought back as a date and time there are different fields in the table
  to hold the various pieces of information. The table has an event date, start time
  and end time. This allows for easier input on a form but does not necessarily facilitate
  getting the information into Outlook. For this reason these few lines of code are devoted to formatting the information.
• When a user signs up on the web page to visit a booth no date or time is assigned.
  The default time from SQL is then taken in this case (“01/01/1900”). If this default date is the event date the booth number to visit is added as a task.
• If the event date is specified line 57 checks to make sure the event has
  both a start and end time. Without both a start and end time, Outlook will skip the event without entering the information and notify the user. If it meets the requirements of a start and end time the event is added to Outlook as an appointment.
• To add an appointment, add an appointment item (line 62),
  set the appointment properties (lines 62-68), and save the appointment (line 69).
• After going through the first event the script moves to next event, provided it exists and the process begins until the end of the recordset is reached.
• At the end of the recordset the objects are released the connection to
  the database is closed. The events and tasks are now in
  Outlook and can be manipulated in Outlook and synched with a handheld device.
  This offers the flexibility of adding information via the Web
  and still syncing with a PDA or manipulating the information from Outlook
  in a more user-friendly form.

Conclusion

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

Personal Data Interchange
Outlook Appointments, ASP and vCal