The Microsoft Project Central inbox contains all update, assignment, status request, and other messages generated in Microsoft Project Central. Ordinarily, Microsoft Project task owners and managers must go to the Microsoft Project Central Messages page to see these messages. However, this article describes how to create an object that runs on the Microsoft Exchange 2000 Server and duplicates all Microsoft Project Central messages in the users' Exchange mailboxes.
This article is one of three articles that discuss integrating Microsoft Project and Microsoft Project Central with Microsoft Exchange 2000 Server. The other two articles are Integrating Microsoft Project 2000 and Project Central with Microsoft Exchange 2000 Server and Copying Microsoft Project Central Tasks to Exchange 2000 Server.
Microsoft Project Central Messaging
Microsoft Project Central writes all e-mail messages in the MSP_WEB_MESSAGES table; the specific task information for messages is stored in related tables as shown in the following table:
| Table Name | Contents |
| MSP_WEB_MESSAGES | Contains a row for each message. |
| MSP_WEB_MESSAGES_ASSIGNMENTS | Contains a row for each task in a particular assignment message. |
| MSP_WEB_MESSAGES_DELEGATIONS | Contains a row for each task in a particular delegation message. |
| MSP_WEB_MESSAGES_NONWORKING | Contains a row for each nonworking notification included in a message. |
When actions performed in Microsoft Project Central generate messages, these tables are populated with the appropriate information.
Messaging Duplication Architecture
The goal of this example is to simply duplicate all messages sent to a Microsoft Project Central user’s inbox. This example does not address any built-in message response capability nor does it update information in Microsoft Project Central. The messages in this example are a text-only rendering of the messaging features available in Microsoft Project Central.
In Microsoft Exchange 2000 Server, each user’s mailbox is exposed as a single folder. In addition, in Exchange 2000 events can be registered with each user’s folder. By implementing the Exchange 2000 event sink in a component, the component can respond to interaction with an individual folder. Many folders can share this same component; to share the component across folders, you register the component on each folder. Microsoft Project Central provides a source for querying project-related messages through SQL Server™.
The solution illustrated in this article includes sample code that works with Microsoft Exchange 2000 Server, SQL Server 7.0 Service Pack 1 (SP1), and Microsoft Project 2000 and Microsoft Project Central.
This solution includes two parts, a Microsoft Visual Basic® 6.0 component to listen to the timer event on a user’s inbox and an SQL 7.0 stored procedure to query the new messages in the Microsoft Project Central user’s inbox. The component is registered in the inbox folder of each Microsoft Project Central user whose Microsoft Project Central messages are to be copied in that user's Exchange mailbox. When the timer event occurs, the component queries Microsoft Project Central for new messages and then stores all of these messages in the user’s mailbox through Collaboration Data Object (CDO) calls.
Figure 1 shows the architecture for copying messages from Microsoft Project Central to a user's Exchange 2000 mailbox.
.gif)
Figure 1. Architecture of Microsoft Project Central/Exchange 2000 message integration
Microsoft Project Central Database Interaction
A stored procedure (sp_get_project_central_messages) is used to select the messages that should be copied from Microsoft Project Central. To try this sample code, on your SQL server, run the create script for the stored procedure in the Microsoft Project Central database. The stored procedure accepts the e-mail address of a project task owner. The stored procedure queries the MSP_WEB_RESOURCES table for the WRES_ID of the e-mail address passed in. The MSP_WEB_MESSAGES is queried by WRES_ID and time to retrieve all messages created for a particular user since that time. This result set is opened using a fast-forward (static and forward-only) cursor.
The time that messages were last queried is stored with each individual Microsoft Project Central resource. The Microsoft Project Central database schema is expanded by adding a Datetime column to the MSP_WEB_RESOURCES table. The last-queried date is stored each time messages are queried to avoid retrieving duplicate messages.
The script below adds message query time to the resource information stored in Microsoft Project Central:
ALTER TABLE dbo.MSP_WEB_RESOURCES ADD
WRES_LAST_EX_UPDATE datetime NULL
Code exists in the stored procedure to handle MSG_TYP_ID 22, a TeamAssign message. The descriptive text about individual tasks included in a TeamAssign message is added to the message text (this is ordinarily handled by Microsoft Project Central using a grid control) by getting all of the tasks from the MSP_WEB_MESSAGES_ASSIGNMENTS table. The stored procedure presented here does this by opening another cursor and retrieving the tasks associated with the message and concatenating them with the original body of the message. Note that there are other types of messages including task delegations, updates, and nonworking time messages. These message types are not handled by this sample. However, you could add this feature by querying the correct Microsoft Project Central table and retrieving the task information (for example, you would query MSP_WEB_MESSAGES_DELEGATIONS for task delegation messages).
Sample code from the stored procedure is shown below:
CREATE PROCEDURE sp_get_Project_Central_Messages @ResEmail NVARCHAR(255) AS
/* Retrieves all Project Central messages for a particular Resource's
email address
In: @ResEmail VARCHAR(255) Valid Email address for a Project Central
resource as listed in the MSP_WEB_RESOURCES table
Out: Recordset containing Sender (MSG_SENDER_EMAIL NVARCHAR(255)),
Subject (MSG_SUBJECT NVARCHAR(255)), Body (MSG_BODY NTEXT) for each
message new in the MSP_WEB_MESSAGES table
*/
DECLARE @ResID INT --Stores the WRES_ID of the resource
passed in @ResEmail
DECLARE @UpdateTime DATETIME --Stores the last updated time
DECLARE @MsgSubject NVARCHAR(255) --Subject of current message
DECLARE @MsgBody NVARCHAR(3500) --Message content for current message
DECLARE @MsgID INT --Project Central ID of current message
DECLARE @SenderResID INT --Web Resource ID of the person
sending the Project Central message
DECLARE @SenderResEmail NVARCHAR(255) --Email address of the person
sending the Project Central message from ---MSP_WEB_RESOURCES
DECLARE @MsgTypeID INT --Project Central Message Type
DECLARE @TaskName NVARCHAR(255) --Current Task Name in Project Central
--Create a temporary table to store all messages to be sent
CREATE TABLE #t1
(MSG_SENDER_EMAIL NVARCHAR(255)
,MSG_SUBJECT NVARCHAR(255)
,MSG_BODY NVARCHAR(3500))
SET NOCOUNT ON --Do not want to return row counts for intermediate selects
SELECT @ResID = WRES_ID, @UpdateTime = WRES_LAST_EX_UPDATE
FROM MSP_WEB_RESOURCES
WHERE WRES_EMAIL = @ResEmail
--Create and Open a cursor that includes all messages for this resource
sent since the last time this was run
DECLARE MessagesCursor CURSOR FAST_FORWARD FOR
SELECT WMSG_ID
,WMSG_SUBJECT
,WMSG_BODY
,WMSG_PROJ_TYPE
,WRES_ID_SENDER
FROM MSP_WEB_MESSAGES
WHERE WRES_ID_RECEIVER = @ResID
AND WMSG_TIME >
COALESCE(@UpdateTime,'1/1/2000')
OPEN MessagesCursor
--Static cursor is open, we can read the time and get the current run time
to indicate the latest record read
Select @UpdateTime = GETDATE()
UPDATE MSP_WEB_RESOURCES
SET WRES_LAST_EX_UPDATE = @UpdateTime
WHERE WRES_ID = @ResID
FETCH NEXT FROM MessagesCursor INTO @MsgID, @MsgSubject, @MsgBody, @MsgTypeID, @SenderResID
WHILE @@FETCH_STATUS = 0
BEGIN
--Get the email address of this sender as recorded in MSP_WEB_RESOURCES
SELECT @SenderResEmail = WRES_EMAIL
FROM MSP_WEB_RESOURCES
WHERE WRES_ID = @SenderResID
--Add Task names to the Team Assign Message. This could be done for
delegations and other types of messages also.
--That task could easily be inserted here.
IF @MsgTypeID = 0
BEGIN
DECLARE TasksCursor CURSOR FAST_FORWARD FOR
SELECT TASK_NAME
FROM MSP_WEB_MESSAGES_ASSIGNMENTS WMA
INNER JOIN MSP_WEB_ASSIGNMENTS WA
ON WMA.WASSN_ID = WA.WASSN_ID
WHERE WMA.WMSG_ID = @MsgID
OPEN TasksCursor
FETCH NEXT FROM TasksCursor INTO @TaskName
WHILE @@FETCH_STATUS = 0
BEGIN
--Add a CR-LF and the name of each task
SELECT @MsgBody = @MsgBody + CHAR(10) + CHAR(13) + COALESCE(@TaskName,'')
FETCH NEXT FROM TasksCursor INTO @TaskName
END
CLOSE TasksCursor
DEALLOCATE TasksCursor
END
--Insert new message into temporary table
INSERT INTO #t1
(MSG_SENDER_EMAIL
,MSG_SUBJECT
,MSG_BODY)
VALUES
(@SenderResEmail
,@MsgSubject
,@MsgBody)
FETCH NEXT FROM MessagesCursor INTO @MsgID, @MsgSubject, @MsgBody, @MsgTypeID, @SenderResID
END
CLOSE MessagesCursor
DEALLOCATE MessagesCursor
--Return result recordset to caller
SELECT MSG_SENDER_EMAIL
,MSG_SUBJECT
,MSG_BODY
FROM #t1
--Cleanup
DROP TABLE #t1
Microsoft Exchange 2000 Interaction
Web Store Events
You can create Web Store event sinks in Visual Basic or Visual C++. The sink written for the Microsoft Project 2000 SDK sample application was written with Visual Basic 6.0. It consists of an ActiveX® dynamic-link library (DLL) component that must be packaged in a COM+ application.
Web Store event sinks must be packaged in COM+ packages to eliminate the possibility that a renegade event sink will affect the Microsoft Exchange 2000 Server. These DLLs then run in their own process space rather than in the Microsoft Exchange process space.
Sinks must be registered in the Web Store. Creating a special Web Store record in the folder where you want the sink to live completes the registration. For this Microsoft Project 2000 SDK sample, the sink is registered in the inbox of each user that will receive Microsoft Project Central data.
When designing Web Store Event Sinks in a Visual Basic project, you must first reference the EXOLEDB type library (Exoledb.dll). The EXOLEDB type library provides several interfaces. The functionality of your sink determines which interface you implement.
For this sample application, the IexStoreSystemEvents interface is implemented. Using this interface, you have access to the OnTimer event, which allows you to periodically query the Microsoft Project Central database and update the Web Store.
Microsoft Project Central Message Component Registration
To attach to store events, this code implements IexStoreSystemEvents as mentioned above in the description of Web Store events.
This sample includes a component that needs to be registered in each Microsoft Project Central user’s mailbox (sample registration code is shown below) so that tasks for users who are not Microsoft Project Central resources are ignored. One way that automated deployment can be implemented is to create an insert trigger on the MSP_WEB_RESOURCES table in Microsoft Project Central. This trigger could run a Data Transformation Services (DTS) package containing this script to register the user when the user is added to the resources table. Delete and update triggers could also be created to handle changes and deletions. Alternatively, you could modify the code to run on a Web Store timer, query all of the resources, and process messages for each one. If you do this, you send a query to SQL Server for every Microsoft Exchange user. In addition, you will need to handle resources that are in Microsoft Exchange and not in Microsoft Project Central. You would also lose the granularity of control over the response of each user’s inbox. Finally, instead of using a script, you could manually add the timer event sink to a user’s Microsoft Exchange 2000 mailbox folder.
The sample code used to register the timer event on the mailbox of each Exchange 2000 user is included below. To register the event, run this VBScript file to pass in the e-mail address of each user. This sample code must run on the Microsoft Exchange server as the source of oInfo indicates.
' This script accepts the email address of a project central
' resource and registers that email to use the OnTimer event to
' run a component
'Get the email address submitted at the command prompt
Set oArgs = WScript.Arguments
if oArgs.count > 0 then
sUser=Trim(oArgs(0))
'Get Domain Name assumes that machine running this script is in the
'same domain as Microsoft Exchange
Set oInfo = CreateObject("AdSystemInfo")
sDomainName = oInfo.DomainDNSName
sFolderURL = "file://localhost/backofficestorage/" & _
sDomainName & "/mbx/" & sUser
sFolderURL = sFolderURL & "/inbox/"
'Create an OLEDB connnection to the exchange user's folder
Set cnSess = CreateObject("ADODB.Connection")
cnSess.Provider = "Exoledb.Datasource"
cnSess.Open sFolderURL
'Register the Web Folder for Project Central OnTimer events
sEvent = sFolderURL & "OnTimerEventRegistration"
cnSess.BeginTrans
Set rBinding = Nothing
Set rBinding = CreateObject("ADODB.Record")
'Open or create with adModeReadWrite and CreateOptions adOpenIfExists
rBinding.Open sEvent, cnSess, 3, 33554432
With rBinding
'Set the type of content to event registration
.Fields("DAV:contentclass")="urn:content-class:storeeventreg"
'Set type of Event registration to OnTimer
.Fields("http://schemas.microsoft.com/exchange/events/EventMethod")="OnTimer"
'Set the Prog ID of the class to instantiate during the OnTimer event
'*Insert the ProgID of your component here
.Fields("http://schemas.microsoft.com/exchange/events/SinkClass")="ProjectData.Sink"
'Set the Start Time for the event to the current time
vDate = Date()
.Fields("http://schemas.microsoft.com/exchange/events/TimerStartTime") = vDate
'Set the event to occur every five minutes
.Fields("http://schemas.microsoft.com/exchange/events/TimerInterval") =5
'Set the timer to expire in 100 years
.Fields("http://schemas.microsoft.com/exchange/events/TimerExpiryTime") = #8/4/2099#
'Add a custom field that contains the Last Download time and set it to now
.fields.Append "ProjectLastDownload", 8, , , CStr(vDate)
.fields.Update
.Close
End With
'Commit modifications and clean-up
cnSess.CommitTrans
Set rBinding = Nothing
set cnSess = Nothing
End if
Using the Timer Event
When the timer event occurs in the Web Store, the IExStoreSystemEvents_OnTimer event is invoked. The name of the user’s mailbox, which has the sink registered in the bstrURLItem parameter, is passed to the event procedure. Using the mailbox name and a parameter indicating the time of the last data refresh, the GetNewMessages subroutine is called. GetNewMessages takes the user ID and last run time and executes the stored procedure, sp_get_Project_Central_Messages in the Microsoft Project Central database. A corresponding Web Store e-mail is sent to the user for each row in the recordset that is returned from the stored procedure. The From, Subject, and TextBody attributes are populated with values from the recordset. The message is sent to the user whose inbox triggered the message. Each message is created using the CDO for Exchange 2000 CDO.Message object. The sample code shown below is the part of this application that shows how you might execute the Microsoft Project Central stored procedure and use the returned dataset to create mailbox messages.
Public Sub GetNewMessages(ByVal sUser$)
'This procedure executes a stored procedure,'sp_get_Project_Central_Messages',
'in the Project Central database and uses the result set to create
'new Exchange messages for the current User.
Dim oConn As New ADODB.Connection 'Connection to database
Dim oRS As New ADODB.Recordset 'Recordset holding result from sproc
Dim oCMD As New ADODB.Command
Dim oPar As New ADODB.Parameter
Dim sRS as String 'The recordset's open string
Dim oMsg As CDO.Message 'New message created with sproc result
Dim sConnect as sString 'OLEDB connection string
Dim oInfo As Object 'NT system info object
Dim sDomainName 'Current domain name
Dim oInfoNT
Dim sServerName as String 'Name of current machine
On Error Resume Next
'Get the Domain Name in order to create the
'User's email address.
Set oInfo = CreateObject("AdSystemInfo")
sDomainName = oInfo.DomainDNSName
'Get the Server and Domain name
Set oInfoNT = CreateObject("WinNTSystemInfo")
sServerName = LCase(oInfoNT.ComputerName)
****************************************************
'Connect to SQL Server DB and run sproc (Insert your own connection
'information here)
*****************************************************
sConnect = "Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"User ID=" & DB_User & ";Password=" & DB_Password & _
";Initial Catalog=" & DB_Name & ";" & _
"Data Source=" & DB_SERVER
oConn.ConnectionString = sConnect
oConn.Open
oCMD.CommandText = "sp_get_Project_Central_Messages"
oCMD.ActiveConnection = oConn
oCMD.CommandType = adCmdStoredProc
oPar.Name = "@ResEmail"
oPar.Value = sUser & "@" & sDomainName
oPar.Direction = adParamInput
oPar.Type = adVarChar
oPar.Size = 255
oCMD.Parameters.Append oPar
Set oRS = oCMD.Execute()
Set oPar = Nothing
'Now create an email message for each item in the recordset
While Not oRS.EOF
Set oMsg = New CDO.Message
With oMsg
oMsg.To = sUser & "@" & sDomainName
oMsg.From = oRS(0) 'This code assumes that oRS(0) is a
'valid Exchange address. You
'may want to use error handling to check
'the address.
oMsg.Subject = oRS(1)
oMsg.TextBody = oRS(2)
oMsg.Send
End With
oRS.MoveNext
Wend
oRS.Close
Set oRS = Nothing
Set oCMD = Nothing
End Sub
The code block below responds to the timer event (this code actually calls the message creation code later in this article). The event IExStoreSystemEvents_OnTimer is invoked. This example shows calls to two methods, GetNewMessages and GetTasks. Each of these methods implements separate functionality (GetTasks is discussed in the Copying Microsoft Project Central Tasks to Exchange 2000 Server article.
Note GetTasks functionality does not need to be implemented in the same event sink as the message functionality. It has been shown this way for simplicity. If you are writing both of these pieces of functionality into your application, you may want to write two different event sinks. You could then break out the functionality and:
- Perform each function at different time intervals. You may chose to process messages every hour and only update task information once a day (the event interval is identified in the component registration script).
- Give different users different capability. That is, messages and tasks for some users and only tasks for others. The features you give to each user would then depend on the sinks that you registered on the user's inbox.
Here's what the code looks like:
Private Sub IExStoreSystemEvents_OnTimer(ByVal bstrURLItem As String, ByVal lFlags As Long)
'This event periodically checks the Microsoft Project Central
'database for new message and task items.
Dim sReturn as String
Dim sMailbox as String
On Error Resume Next
'Since this sink can be called from a number of mailboxes, we need
'to first determine the current mailbox
sMailbox = Right(bstrURLItem, Len(bstrURLItem) - InStr(bstrURLItem, "MBX") - 3)
sMailbox = Left(sMailbox, InStr(sMailbox, "/") - 1)
'*** Note that the function you choose to call here will depend on whether
'you are trying to Get messages or tasks ***
'Send new messages to Exchange mailbox
GetNewMessages sMailbox
'Create task items.
CreateNewTasks sMailbox
End Sub
Putting It All Together
Because this sample was written using prerelease Microsoft Exchange 2000 code, this article includes only sample code. You will need to deploy this code on your version of Microsoft Exchange 2000 Server.
Duplicating the sample
Use the following steps to duplicate the sample:
- In Visual Basic 6.0, create a new ActiveX DLL project.
- Add references to
- Microsoft ADO 2.5 Object Library.
- CDO for Microsoft Exchange 2000.
- The EXOLEDB type library.
- Create a new class.
- In the class, implement the IexStoreSystemEvents interface.
- Implement all required methods for the IexStoreSystemEvents interface.
- Copy the sample OnTimer event code into the IExStoreSystemEvents_OnTimer event and make any necessary changes.
- Write or copy the GetMessages code and make any necessary changes.
- Make any changes necessary to support new versions of Microsoft Exchange 2000 Server.
- Compile the DLL.
- Register your component with COM+ using the component services administrative tool. Give the component and administrative security context.
- Create the sp_get_project_central_messages stored procedure in your SQL Server 7.0 Microsoft Project Central database.
- Register the event component on the inbox of a Microsoft Project Central resource (you can use the sample registration code included to perform this task).
Debugging
You can debug Web Store event sinks in the Visual Basic IDE. Make sure that you enable binary compatibility for your ActiveX DLL project. Debug the DLL that the Web Store event registration item references.