Building the Folder Event Agent
For the client contacts to exist in SQL Server, some synchronization mechanism is required. In this case, synchronization is handled by a server-side folder event agent. Basically, any time a client contact is added, modified, or deleted from the Client Contacts folder, an event agent script ensures that the appropriate action is performed on the SQL Server Clients table.
Understanding How the Exchange Event Service Works
Let me take a moment to reflect on how the Exchange Event Service works. The Exchange Event Service is a Windows NT service. Functionally, however, the Event Service is responsible for querying an information store, stored on the Exchange Server, for information about all of the events that occurred, including additions, deletions, or modifications to items in a folder, since the last time the Event Service checked for changes. Interestingly, the Event Service uses the same technology Outlook does when it performs synchronization between the client OST file and the Exchange folders, called Incremental Change Synchronization (ICS). Every change is caught by the Event Service—even if the Event Service is not running—and forwarded to an installed event handler. Not only changes cause actions to be taken by the Event Service. Scheduled events can be configured to cause some action to be taken without any changes being made to messages in the associated folder.
The default event handler, and the entire focus for this section, is the Exchange Event Script Agent. This event handler allows developers to write custom event handlers in either VBScript or Jscript. As you will see, these scripts have the capability to manipulate the message and folder that caused the event to occur. You can even make calls to other COM components, such as ADO or Active Directory Services Interface (ADSI), in your scripts. In fact, your scripts will probably use a great deal of CDO because all event handlers are passed a pre-logged-on CDO session, the message ID of the message that caused the event, and the folder ID of the folder the event occurred in. You can use CDO to manipulate or reference these objects to perform required tasks such as message routing or tracking. The remainder of this chapter focuses on the creation of scripts that are executed by the Exchange Event Script Agent whenever events fire within a particular public folder. I will also discuss how to use the intrinsic objects I mentioned earlier to do some really fun things.
Preparing to Write the Script
Before you write any folder event agent, it is important to understand the requirements of the Microsoft Exchange Event Service and Outlook. In this section, I will discuss what must be done before you can create agents and expect them to run.
Event Service Account
Agents are pieces of code that are executed by the Microsoft Exchange Event Service. Remember that the Event Service is actually just a Windows NT service running in the background, and the Windows NT account under which the Microsoft Exchange Event Service is logged in is one of the most important configuration parameters that needs to be set to ensure proper function and execution of the agents you write as emphasized in Figure 13.5.
){kind=link}
By default, the Exchange Event Service logs on using the same Exchange service account that all of the other Exchange services use. This provides adequate permissions to access Exchange resources but very limited access to other resources that the agents may be interacting with, such as SQL Server and NTFS files and folders. Depending on your requirements, you might want to change the Windows NT account that is used by the Event Service to an account that has a bit more, or a bit less, security than the default.
Figure 13.5
The Event Service must have adequate permissions to access resources
in and outside of Exchange.
Granting Permissions to Create Agents
Helping the Debugging Process
The Event Service relies on values stored in the System Registry to control how events are fired and how much is logged in the Windows event log. Table 13.5 summarizes the Registry values that exist in the following Registry key:
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSExchangeES\Parameters
Table 13.5 Event Service Registry Values
|
Name |
Description |
Valid Range |
Recommended Value |
|
Logging Level |
Amount of information written to Windows event log. |
0–5 (0 is default.) |
5—Development. 1—Production. |
|
Maximum execution time for scripts in seconds. |
Number of seconds that a script is allowed to run in seconds |
Positive integer values (900 is default). |
As required by functionality in the script. |
|
Maximum size for agent log in KB |
Sets the maximum size for logs created by agents. |
Positive integer values (32 is default). |
As required. Logs are overwritten as necessary. |
Note - Additionally, the ICS notification interval in HKEY_LOCAL_MACHINE\System\ CurrentControlSet\Services\MSExchangeIS\ ParametersSystem specifies how often Exchange checks public folders to see if changes have been made. The default value is 5 minutes (300 seconds). You can decrease this value, but you might not want to because this will negatively impact the performance of the Exchange Server.
The next step you must take to create folder agents is to assign the appropriate permissions to users who require this ability. To do this, you must set permissions on a special system folder called EventConfig_<ServerName> where <ServerName> is the name of the Exchange Server the Event Service is running on. This folder is located in the following location in the Exchange Administrator program:
..\Folders\System Folders\Event Root\
Simply display the properties of this folder and click the Client Permissions button located on the General tab of the properties dialog box. Add the users that will be creating agents and ensure that they hold the Author, or higher, role.
The Agents Tab Does Not Display?
You have set up the Exchange Event Service and are ready to write some cool scripts in a public folder, but you don't see the Agents tab in the properties window of the folder. To create agents for a public folder using Outlook, two conditions must be satisfied. First, you must own the public folder. Second, the Server Scripting Outlook add-in must be installed and enabled.
You can enable the Server Scripting add-on by opening the Outlook Options window from the Tools menu, selecting the Other tab, and clicking the Advanced Options button. From the Advanced Options dialog box, click the Add-In Manager button and ensure that the Server Scripting add-in has a check mark beside it.
Other Ways of Running the Event Agents
Some organizations might want the event scripting agent to run within MTS to run it in a different security context or to isolate it in a different middle tier process. Well, because the event scripting agent is a DLL, SCRIPTO.DLL, this is indeed possible. Microsoft makes this even simpler by providing a pre-built MTS package for this purpose. The package is called Scripto.pak and is located on the Exchange 5.5 CD in the Server\Support\Collab\ Sampler\Scripts folder.
For load purposes, you might also want to have the agents run on computers other than the server where the public folder is located. Exchange fully supports this because agents can be quite I/O and processor intensive.
Creating the Script
Before I describe how to write the agent that synchronizes changes from the Client Contacts public folder to the SQL Server database, it is beneficial to discuss the implicit objects that need to be manipulated to accomplish this task. The following is a list and description of each of these objects:
EventDetails.Session. A pre-logged on CDO session. The session is logged on under the user of the last person to save the agent.
EventDetails.FolderID. The entry ID of the folder from where the event occurred.
EventDetails.MessageID. The entry ID of the message that caused the event.
GetFolder and GetMessage
You can use the GetFolder and GetMessage CDO functions in your script to gain an object reference to the folder and message that the EventDetails.FolderID and EventDetails.MessageID refer to.
In SCOES, this chapter's sample application, the event scripting agent needs to respond to the OnMessageCreated, OnChange, and OnMessageDeleted events. In the OnMessageCreated, the agent must connect the SQL Server and execute an Insert statement to add a record to the appropriate table. The OnChange and OnMessageDeleted events similarly execute Update and Delete SQL statements, respectively.
You can begin creating your scripts by displaying the properties dialog box for your public folder and selecting the Agents tab, as shown in Figure 13.6. Next, click the New button to create a new event script for your folder. You are required to specify what events you would like to have monitored as well as the name of the agent. In case of client contact, I only want events to fire whenever new items are added to the folder, items are updated, or when items are deleted from the folder.
){kind=link}
Out-of-Sync Data
The method I use to synchronize contact information between Exchange and SQL leaves room for the SQL Server to become out-of-sync with the Client Contacts public folder. Future considerations could use the scheduled event to perform consistency checks and backfills against the SQL data store to ensure that both data locations are completely synchronized all the time.
When you are ready to start coding the script, click the Edit Script button, as shown in Figure 13.7.
){kind=link}
Figure 13.6
The Agents tab in the properties dialog box of a public folder.
Figure 13.7
The Event Script dialog box that allows you to view schedule details
such as the schedule, event logs and the event script.
Before you start writing the agent, let's take a look at what there is to start with. Example 13.3 is what you would see if you created a new agent using the Outlook interface.
Example 13.3 Default agent VBScript.
<SCRIPT RunAt=Server Language=VBScript> '——————————————————————————————————————— 'FILE DESCRIPTION: Exchange Server Event Script '——————————————————————————————————————— Option Explicit '——————————————————————————————————————— ' Global Variables '——————————————————————————————————————— '——————————————————————————————————————— ' Event Handlers '——————————————————————————————————————— ' DESCRIPTION: This event is fired when a new message is added to the folder Public Sub Folder_OnMessageCreated End Sub ' DESCRIPTION: This event is fired when a message in the folder is changed Public Sub Message_OnChange End Sub ' DESCRIPTION: This event is fired when a message is deleted from the folder Public Sub Folder_OnMessageDeleted End Sub ' DESCRIPTION: This event is fired when the timer on the folder expires Public Sub Folder_OnTimer End Sub </SCRIPT>
As you can see, the default script provides a place for you to stick global variables and placeholders for the four folder events OnMessageCreated, OnChange, OnMessageDeleted, and OnTimer.
MAPI Session
The Event Service is passed a logged on MAPI session, so there is no need to write any code in your script that establishes a MAPI session. Exchange uses the identifier of the person who last saved the agent as CDO logon credentials.
Using a Script Recipient
It is a good idea to create a separate recipient for creating and managing scripts. This ensures that the CDO session your agents use is logged on using the same credentials every time instead of using the credentials of whoever saved the script last. Additionally, if your scripts create and send mail messages to other users, the message is addressed from this new user and not the developer of the script.
When you start to build folder agents, you quickly see the importance of logging. The Event Service provides an implicit object called Script.Response that can be used to save string responses that are later saved into the event handler's corresponding log file. You can see the contents of this log file by clicking the Logs button as displayed in Figure 13.7. The contents of the Script.Response object are not written to the log file until the end of the script and is not written at all if unhandled errors occur from within the script. This means that you have to concatenate all of your error and logging strings as you go, while assigning the result Script.Response. To make this job a little cleaner and to make my code a little easier to read, I use a homemade function called Log. Here's the code:
Public Sub Log(sMessage) ' Collect the Logged Messages in Script.Response Object ' Adding the correct formatting Script.Response = Script.Response & vbNewLine & Now & vbTab & sMessage End Sub
That's it. You simply pass Log a parameter, sMessage, and it concatenates it onto the existing Script.Response value, adding time stamps and line formatting as well.
I will focus exclusively on the OnMessageCreated event handler in the agent because all of the other events are extremely similar. The code is shown in Example 13.4.
Example 13.4 Using the OnMessageCreated event to store data into SQL Server.
Public Sub Folder_OnMessageCreated
'============================================================================
' DESCRIPTION: This event is fired when a new message is added to the folder
' NOTE: Extensive Logging is enabled for demonstration purposes only
'=============================================================================
' Local Variables
dim iFolder
dim iMessage
dim cdoSession
dim oContactItem
dim oContactsFolder
dim adoConnection
dim sConnectionString
dim sSQL
dim sValues
Log "The OnMessageCreated Event Fired"
on error resume next
' Load the Intrinsic objects into local vars
'===========================================
iFolder = EventDetails.FolderID
iMessage = EventDetails.MessageID
Set cdoSession = EventDetails.Session
err.Clear
' Get the Message that Caused the Event
'=======================================
Set oContactItem = cdoSession.GetMessage(iMessage, Null)
if Err.Number <> 0 then
log "Error getting oContactItem"
else
'For Logging Purposes
'==========================================================
Log "Processing Contact"
Log "Customer ID: " & CStr(oContactItem.Fields.Item("ContactID"))
Log "Company Name: " & CStr(oContactItem.Fields.Item(974520350))
Log "Contact Name: " & CStr(oContactItem.Fields.Item(3604510))
Log "Contact Title: " & CStr(oContactItem.Fields.Item(974585886))
Log "Address: " & CStr(oContactItem.Fields.Item(975765534))
Log "City: " & CStr(oContactItem.Fields.Item(975634462))
Log "Region: " & CStr(oContactItem.Fields.Item(("Region"))
Log "Postal Code: " & CStr(oContactItem.Fields.Item(975831070))
Log "Country: " & CStr(oContactItem.Fields.Item(975568926))
Log "Phone: " & CStr(oContactItem.Fields.Item(973602846))
Log "Fax: " & CStr(oContactItem.Fields.Item(974913566))
'Construct SQL
'==========================================================
Log "Constructing Values String..."
sValues = ConstructValueString(oContactItem)
Log sValues
if sValues <> "" then
sSQL = "Insert Into Customers"
sSQL = sSQL & "(CustomerID, CompanyName, ContactName, Contacttitle,"
sSQL = sSQL & " Address, City, Region, PostalCode, Country, Phone, Fax)"
sSQL = sSQL & " Values (" & sValues & ")"
End if
log sSQL
'Connect to SQL Server
'==========================================================
Set adoConnection = CreateObject("ADODB.Connection")
if Err.Number = 0 then
Log "Connecting to SQL Server"
sConnectionString = "driver={SQL
Server};server=Joelhome;uid=sa;pwd=;database=collabmtp"
log "Opening SQL Connection"
adoConnection.ConnectionString = sConnectionString
adoConnection.Open
if Err.Number = 0 then
adoConnection.Execute sSQL,,128
if Err.Number <> 0 then
Log "There were Problems executing SQL"
Log "Error" & Err.Description
end if
else
Log "Error opening: " & sConnectionString
Log Err.Description
end if
else
Log "Error Referencing ADO" & Err.Description
end if
'Execute the Script
end if
End Sub
This probably looks a lot more complex than what you thought it would, huh? It only looks complicated because of the extra inline error checking and handling, and all of the extra logging I do for debugging purposes.
The first thing that this event handler does is assign the implicit variables into local variables:
' Load the Intrinsic objects into local vars '=========================================== iFolder = EventDetails.FolderID iMessage = EventDetails.MessageID Set cdoSession = EventDetails.Session
After these values have been obtained, they can be used to get an object reference to the new client contact message that fired the event. To get this message, the CDO session object's GetMessage function is used. Notice that the cdoSession object is used for this purpose because it is obtained from an implicit pre-logged on CDO session passed by the Event Service. The GetMessage function accepts the unique identifier of the message—in this case, the iMessage variable that was copied from EventDetails.MessageID—and an optional reference to a CDO Information store—in this case, Null:
' Get the Message that Caused the Event '======================================= Set oContactItem = cdoSession.GetMessage(iMessage, Null)
After the message is obtained, the script proceeds to build the appropriate SQL string that is passed to the SQL Server through the ADO Connection object. The SQL string that is built in this event is an Insert SQL statement that must be built dynamically from the field values of the oContactItem message that was just obtained (see Example 13.5).
Example 13.5 Constructing the SQL string.
sValues = ConstructValueString(oContactItem)
...
if sValues <> "" then
sSQL = "Insert Into Customers"
sSQL = sSQL & "(CustomerID, CompanyName, ContactName, Contacttitle,"
sSQL = sSQL & " Address, City, Region, PostalCode, Country, Phone, Fax)"
sSQL = sSQL & " Values (" & sValues & ")"
End if
To make the job of extracting the field values from the OcontactItem message object easier, I constructed a function called ConstructValueString that accepts the OContactItem message object and returns the appropriate string of comma delimited values that will be used in the Insert statement. Example 13.6 lists the code for this function.
Example 13.6 Extracting item values to be used in a SQL Insert command.
Public Function ConstructValueString(oContactItem)
' This function will collect the fields from the ContactItem
' and build the appropriate Value String used in Insert and Update
' SQL Statements
'
' Note: Chr(39) = ' (single quote)
' Chr(44) = , (comma)
dim sTemp
on Error Resume Next
' Get Value for ContactID
sTemp = chr(39) & CStr(oContactItem.Fields.Item("ContactID")) & Chr(39) & chr(44)
' Checked to see if we died on reference to the oContactItem
if Err.Number = 0 then
' Get Value for Company Name
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item(974520350)) & Chr(39) & chr(44)
' Get Value for Contact Name
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item(3604510)) & Chr(39) & chr(44)
' Get Value for Contact Title
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item(974585886)) & Chr(39) & chr(44)
' Get Value for Address
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item(975765534)) & Chr(39) & chr(44)
' Get Value for City
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item(975634462)) & Chr(39) & chr(44)
' Get Value for Region
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item("Region")) & Chr(39) & chr(44)
' Get Value for Postal Code
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item(975831070)) & Chr(39) & chr(44)
' Get Value for Country
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item(975568926)) & Chr(39) & chr(44)
' Get Value for Phone
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item(973602846)) & Chr(39) & chr(44)
' Get Value for Fax
sTemp = sTemp & chr(39) & CStr(oContactItem.Fields.Item(974913566)) & Chr(39)
ConstructValueString = sTemp
else
ConstructValueString = ""
End If
End Function
The method used here is not as obvious as you might imagine. First of all, to gain access to the values that are stored in the fields of the message, I'm using functions that look like the following:
oContactItem.Fields.Item(974520350)
Why don't I just reference the fields by their names? In CDO, all of the default field names, such as Address and City, do not have named fields, thus forcing us to reference them using their IDs. I could have made the code more readable by using constant values, but I wanted to emphasize the underlying field identification values. Notice that I do reference some of the fields using their corresponding field name, such as oContactItem.Fields. Item("Region"). I do this because these fields correspond to custom fields that I created when I created the Client Contact form. These fields have IDs, but they will not be the same in your implementation of this script. Don't worry, this really bugs me too.
CDO Constants
Unfortunately, CDO's built-in constants don't work in the event script to reference the fields. Typically, you simply use the field ID constant instead of the actual number. For example, you can use CdoPR_COMPANY_NAME instead of the number that it represents (974520350) to reference the field. If you are wondering how I found the values for these fields, I just use Visual Basic's object browser to look for CDO constants that meet my field requirements and pull the value for the constant into VBScript. Again, I could just recreate the constants in the script to make the code more readable.
After the SQL statement has been created, the next step is to connect to the SQL Server and execute the script. I first create a reference to ADO using late binding, as in the following:
Set adoConnection = CreateObject("ADODB.Connection")
Then I simply connect to the appropriate database using the following code:
sConnectionString = "driver={SQL Server};server=Joelhome;uid=sa;pwd=;database=collabmtp"
adoConnection.ConnectionString = sConnectionString
adoConnection.Open
If no connection errors occur, I go ahead and execute the SQL Statement using the following line of code:
adoConnection.Execute sSQL,,128
If errors occur during the execute, I log them; otherwise, the client contact synchronization is successful. You can always double-check the results by looking at the resulting log file shown in Figure 13.8.
){kind=link}
Figure
13.8
The log file generated after the OnMessageCreated event fires.
The code that needs to be created for the other two events, the OnChange and OnMessageDeleted events, looks similar to the preceding code we discussed, so I will refrain from that discussion and assume you can take it from here. Just remember that in the OnChange event handler, you build an UPDATE SQL string, and in the OnMessageDeleted, you create a DELETE SQL string.
Other Ways of Managing Server Scripts
There are other ways you can create and bind agents to a folder. In fact, you can do this without any user interface programmatically. The Microsoft Exchange Event Service provides an object library that allows you to create and remove agents programmatically. I will not go into detail on how this is done; however, I will give you some direction on where to get started. The best place to start is with the Microsoft Exchange Event Service Config 1.0 Type Library object that you can reference from a Visual Basic application. After you reference this object in Visual Basic, you can spend some time examining the object hierarchy. The Microsoft Exchange Event Service Config 1.0 object library is stored in ESCONF.DLL, which is usually installed in the c:\ExchSrvr\bin directory.
The OnTimer Event
The one event that has not been discussed much in this chapter is the OnTimer event. As you might have guessed, this event is fired according to some schedule that you determine.
To configure the OnTimer event to fire and to set its firing schedule, open the Agent and make sure the A Schedule Event Occurs check box is selected. Next, to configure the frequency that this event fires, click the Schedule button to display the Scheduled Event dialog box, as shown in Figure 13.9.
){kind=link}
Figure
13.9
The Scheduled Event dialog box that allows a schedule to be set for the OnTimer
event to occur.