I needed to perform the same set of tasks for the Requests folder, which contains tasks items.
I decided to start by doing the SQL Table to hold my items. To assist with this I used MFCMapi, downloadable from:
http://www.microsoft.com/downloads/details.aspx?familyid=55fdffd7-1878-4637-9808-1e21abb3ae37&displaylang=en
This helped me with determining the field names to use in my SQL Table, not that it matters, afterall 'A rose by any other name would smell as sweet' a saying my maths tutor Mr Bunting once used to explain variables!
So my script to create the SQL Table turned out as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Requests]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Time]
GO
CREATE TABLE [dbo].[Requests] (
[GUID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[URLName] [nvarchar] (256) COLLATE Latin1_General_CI_AS NOT NULL ,
[StoreEntryID] [nvarchar] (160) COLLATE Latin1_General_CI_AS NOT NULL ,
[EntryID] [nvarchar] (160) COLLATE Latin1_General_CI_AS NOT NULL ,
[LoggedBy] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[LoggedFor] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[Contacts] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Company] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Subject] [nvarchar] (256) COLLATE Latin1_General_CI_AS NOT NULL ,
[Priority] [int] NULL ,
[PurchaseOrderNumber] [nvarchar] (32) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequestID] [nvarchar] (16) COLLATE Latin1_General_CI_AS NULL ,
[Categories] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[DueDate] [datetime] NULL ,
[StartDate] [datetime] NULL ,
[CompletedDate] [datetime] NULL ,
[Status] [int] NULL ,
[Owner] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[Employees] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[TotalWork] [int] NULL ,
[ActualWork] [int] NULL ,
[BillingInformation] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[Creator] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[CreationTime] [datetime] NULL ,
[LastModifier] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[LastModificationTime] [datetime] NULL ,
[Body] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Requests] WITH NOCHECK ADD
CONSTRAINT [PK_Requests] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Requests] ADD
CONSTRAINT [DF_Requests_ID] DEFAULT (newid()) FOR [GUID]
GO
The next step was to create the Stored Procedure to do our saves:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaveRequest]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaveRequest]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE SaveRequest
(
@GUID uniqueidentifier=NULL,
@ID int=0,
@URLName nvarchar(256)='',
@StoreEntryID nvarchar (160),
@EntryID nvarchar (160),
@LoggedBy nvarchar (256)=NULL,
@LoggedFor nvarchar (256)=NULL,
@Contacts nvarchar (128)=NULL,
@Company nvarchar (128)=NULL,
@Subject nvarchar (256),
@Priority int=NULL,
@PurchaseOrderNumber nvarchar (32)=NULL,
@RequestID nvarchar (16)=NULL,
@Categories nvarchar (128)=NULL,
@DueDate datetime=NULL,
@StartDate datetime=NULL,
@CompletedDate datetime=NULL,
@Status int=NULL,
@Owner nvarchar (32)=NULL,
@Employees nvarchar (256)=NULL,
@TotalWork int=NULL,
@ActualWork int=NULL,
@BillingInformation nvarchar (256)=NULL,
@Creator nvarchar (32)=NULL,
@CreationTime datetime=NULL,
@LastModifier nvarchar (32)=NULL,
@LastModificationTime datetime=NULL,
@Body ntext=NULL
)
AS
SET NOCOUNT ON
DECLARE
@trncnt int,
@ErrorNumber int
SELECT @ErrorNumber = -1
SELECT @trncnt = @@trancount
IF @trncnt = 0
BEGIN TRANSACTION T1
ELSE
SAVE TRANSACTION T1
IF @EntryID IS NOT NULL
SET @ID = (SELECT ID FROM Requests WHERE EntryID=@EntryID)
IF @ID IS NULL
SET @ID = 0
IF @ID=0 --No ID passed so INSERT
BEGIN
SET @GUID=NEWID()
INSERT INTO Requests
(GUID, URLName, StoreEntryID, EntryID, LoggedBy, LoggedFor, Contacts, Company, Subject, Priority, PurchaseOrderNumber, RequestID, Categories, DueDate, StartDate, CompletedDate, Status, Owner, Employees, TotalWork, ActualWork, BillingInformation, Creator, CreationTime, LastModifier, LastModificationTime, Body)
VALUES
(@GUID, @URLName, @StoreEntryID, @EntryID, @LoggedBy, @LoggedFor, @Contacts, @Company, @Subject, @Priority, @PurchaseOrderNumber, @RequestID, @Categories, @DueDate, @StartDate, @CompletedDate, @Status, @Owner, @Employees, @TotalWork, @ActualWork, @BillingInformation, @Creator, @CreationTime, @LastModifier, @LastModificationTime, @Body)
SET @ID=@@IDENTITY
END
ELSE --ID Passed so UPDATE
BEGIN
UPDATE Requests
SET
URLName=@URLName,
StoreEntryID=@StoreEntryID,
EntryID=@EntryID,
LoggedBy=@LoggedBy,
LoggedFor=@LoggedFor,
Contacts=@Contacts,
Company=@Company,
Subject=@Subject,
Priority=@Priority,
PurchaseOrderNumber=@PurchaseOrderNumber,
RequestID=@RequestID,
Categories=@Categories,
DueDate=@DueDate,
StartDate=@StartDate,
CompletedDate=@CompletedDate,
Status=@Status,
Owner=@Owner,
Employees=@Employees,
TotalWork=@TotalWork,
ActualWork=@ActualWork,
BillingInformation=@BillingInformation,
Creator=@Creator,
CreationTime=@CreationTime,
LastModifier=@LastModifier,
LastModificationTime=@LastModificationTime,
Body=@Body
WHERE (ID=@ID)
END
SELECT @ID
IF @@Error <> 0 GOTO ErrorHandler
IF @trncnt = 0
COMMIT TRANSACTION
RETURN (0)
ErrorHandler:
ROLLBACK TRANSACTION
RETURN (100)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
The next step was to alter my existing TimeItem.vbs script to cope with receiving a request and then saving it to the DB, this is the script I ended up with (although at some point I'll probablt tidy up and create one script for both Time and Requests):
' VBScript File
Set obArgs = WScript.Arguments
Action = lcase(obArgs.Item(0))
itemURL = lcase(obArgs.Item(1))
If obArgs.Count=3 Then
EntryID = obArgs.Item(2)
End If
providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders/"
connectionString = "Provider=SQLOLEDB;Data Source=ctsnts01;Initial Catalog=Exchange;User ID=sa;Password=************"
Dim GUID, ID, URLName, StoreEntryID, EntryID, LoggedBy, LoggedFor, Contacts, Company, Subject, Priority, PurchaseOrderNumber, RequestID, Categories, DueDate, StartDate, CompletedDate, Status, Owner, Employees, TotalWork, ActualWork, BillingInformation, Creator, CreationTime, LastModifier, LastModificationTime, Body
Select Case Action
Case "save"
GetJournalItem itemURL
SaveRequest GUID, ID, URLName, StoreEntryID, EntryID, LoggedBy, LoggedFor, Contacts, Company, Subject, Priority, PurchaseOrderNumber, RequestID, Categories, DueDate, StartDate, CompletedDate, Status, Owner, Employees, TotalWork, ActualWork, BillingInformation, Creator, CreationTime, LastModifier, LastModificationTime, Body
DebugScript
Case "delete"
EntryID=GetEntryID(itemURL)
If Len(EntryID)>0 Then
DeleteRequest EntryID
DebugScript
End If
End Select
'DebugVariables
Sub GetJournalItem(bstrURL)
On Error Resume Next
If Len(EntryID)=0 Then
EntryID = GetEntryID(bstrURL)
End If
'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"
'Get the message based on the message entry ID.
Set objpubstore = objSession.InfoStores("Public Folders")
Set objMsg = objSession.GetMessage(EntryID,objpubStore.id)
URLName=objMsg.Fields(&H6707001E).Value
StoreEntryID=objMsg.Fields(&H0FFB0102).Value
EntryID=objMsg.Fields(&H0FFF0102).Value
LoggedBy=objMsg.Fields(&H82E0001E).Value
LoggedFor=objMsg.Fields(&H82DF001E).Value
MultiValue=objMsg.Fields(&H819B101E)
Contacts=Join(MultiValue, ";")
MultiValue=objMsg.Fields(&H813A101E)
Company=Join(MultiValue, ";")
Subject=objMsg.Fields(&H0037001E).Value
Priority=objMsg.Fields(&H00260003).Value
PurchaseOrderNumber=objMsg.Fields(&H82DE001E).Value
RequestID=objMsg.Fields(&H82E2001E).Value
MultiValue=objMsg.Fields(&H80DB101E).Value
Categories=Join(MultiValue, ";")
DueDate=objMsg.Fields(&H811F0040).Value
StartDate=objMsg.Fields(&H811E0040).Value
CompletedDate=objMsg.Fields(&H81250040).Value
Status=objMsg.Fields(&H81200003).Value
Owner=objMsg.Fields(&H82FC001E).Value
MultiValue=objMsg.Fields(&H82E1101E)
Employees=Join(MultiValue, ";")
TotalWork=objMsg.Fields(&H81290003).Value
ActualWork=objMsg.Fields(&H81280003).Value
BillingInformation=objMsg.Fields(&H808B001E).Value
Creator=objMsg.Fields(&H3FF8001E).Value
CreationTime=objMsg.Fields(&H30070040).Value
LastModifier=objMsg.Fields(&H3FFA001E).Value
LastModificationTime=objMsg.Fields(&H30080040).Value
Body=objMsg.Fields(&H1000000A).Value
Set objMsg = Nothing
objSession.Logoff
Set objSession = Nothing
'If Err.Number<>0 Then DebugError "GetJournalItem Error:" & Err.Number & ":" & Err.Description
End Sub
Function SaveRequest(GUID, ID, URLName, StoreEntryID, EntryID, LoggedBy, LoggedFor, Contacts, Company, Subject, Priority, PurchaseOrderNumber, RequestID, Categories, DueDate, StartDate, CompletedDate, Status, Owner, Employees, TotalWork, ActualWork, BillingInformation, Creator, CreationTime, LastModifier, LastModificationTime, Body)
On Error Resume Next
Dim Conn,Rec,strSQL
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.ConnectionString = connectionString
Conn.Open
strSQL="Exec SaveRequest "
strSQL=strSQL & "@URLName='" & ConvertSQLDataType("nvarchar", URLName) & "', "
strSQL=strSQL & "@StoreEntryID='" & ConvertSQLDataType("nvarchar", StoreEntryID) & "', "
strSQL=strSQL & "@EntryID='" & ConvertSQLDataType("nvarchar", EntryID) & "', "
strSQL=strSQL & "@LoggedBy='" & ConvertSQLDataType("nvarchar", LoggedBy) & "', "
strSQL=strSQL & "@LoggedFor='" & ConvertSQLDataType("nvarchar", LoggedFor) & "', "
strSQL=strSQL & "@Contacts='" & ConvertSQLDataType("nvarchar", Contacts) & "', "
strSQL=strSQL & "@Company='" & ConvertSQLDataType("nvarchar", Company) & "', "
strSQL=strSQL & "@Subject='" & ConvertSQLDataType("nvarchar", Subject) & "', "
strSQL=strSQL & "@Priority=" & ConvertSQLDataType("int", Priority) & ", "
strSQL=strSQL & "@PurchaseOrderNumber='" & ConvertSQLDataType("nvarchar", PurchaseOrderNumber) & "', "
strSQL=strSQL & "@RequestID='" & ConvertSQLDataType("nvarchar", RequestID) & "', "
strSQL=strSQL & "@Categories='" & ConvertSQLDataType("nvarchar", Categories) & "', "
strSQL=strSQL & "@DueDate=" & ConvertSQLDataType("datetime", DueDate) & ", "
strSQL=strSQL & "@StartDate=" & ConvertSQLDataType("datetime", StartDate) & ", "
strSQL=strSQL & "@CompletedDate=" & ConvertSQLDataType("datetime", CompletedDate) & ", "
strSQL=strSQL & "@Status=" & ConvertSQLDataType("int", Status) & ", "
strSQL=strSQL & "@Owner='" & ConvertSQLDataType("nvarchar", Owner) & "', "
strSQL=strSQL & "@Employees='" & ConvertSQLDataType("nvarchar", Employees) & "', "
strSQL=strSQL & "@TotalWork=" & ConvertSQLDataType("int", TotalWork) & ", "
strSQL=strSQL & "@ActualWork=" & ConvertSQLDataType("int", ActualWork) & ", "
strSQL=strSQL & "@BillingInformation='" & ConvertSQLDataType("nvarchar", BillingInformation) & "', "
strSQL=strSQL & "@Creator='" & ConvertSQLDataType("nvarchar", Creator) & "', "
strSQL=strSQL & "@CreationTime=" & ConvertSQLDataType("datetime", CreationTime) & ", "
strSQL=strSQL & "@LastModifier='" & ConvertSQLDataType("nvarchar", LastModifier) & "', "
strSQL=strSQL & "@LastModificationTime=" & ConvertSQLDataType("datetime", LastModificationTime) & ", "
strSQL=strSQL & "@Body='" & ConvertSQLDataType("ntext", Body) & "'"
Conn.Execute strSQL
If Err.Number<>0 Then
DebugError Err.Number & ":" & Err.Description & "-" & strSQL
End If
Conn.Close
set rec = nothing
set conn = nothing
If Err.Number<>0 Then DebugError "SaveRequest Error:" & Err.Number & ":" & Err.Description
End Function
Function DeleteRequest(EntryID)
On Error Resume Next
Dim Conn,Rec,strSQL
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.ConnectionString = connectionString
Conn.Open
strSQL="Exec DeleteRequest "
strSQL=strSQL & "@EntryID='" & ConvertSQLDataType("nvarchar", EntryID) & "'"
Conn.Execute strSQL
Conn.Close
set rec = nothing
set conn = nothing
If Err.Number<>0 Then DebugError "DeleteRequest Error:" & Err.Number & ":" & Err.Description
End Function
'Exchange specific functions
Function GetEntryID(bstrURL)
'Function to get the Exchange EntryID from the URL to the item
On Error Resume Next
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.Provider = "ExOLEDB.DataSource"
Conn.Open providerURL
Rec.Open bstrURL
byteEntryID = Rec.Fields.Item("http://schemas.microsoft.com/mapi/proptag/x0FFF0102")
GetEntryID = OctetToHexStr(byteEntryID)
Rec.Close
Conn.Close
set rec = nothing
set conn = nothing
End Function
'SQL specific functions
Function ConvertSQLDataType(DateType,Value)
'Function to convert VB Script values to SQL values
Select Case DateType
Case "int"
If (IsNull(Value) Or Value="" Or IsEmpty(Value)) Then
ConvertSQLDataType="NULL"
Else
ConvertSQLDataType=Value
End If
Case "datetime"
If (IsNull(Value) Or Value="" Or IsEmpty(Value)) Then
ConvertSQLDataType="NULL"
Else
ConvertSQLDataType="'" & FormatDateTime(Value,1) & " " & FormatDateTime(Value,3) & "'"
End If
Case "varchar", "nvarchar", "text", "ntext"
ConvertSQLDataType=Replace(Value,"'","''")
Case Else
ConvertSQLDataType=Value
End Select
End Function
'General functions
Function OctetToHexStr(arrbytOctet)
' Function to convert OctetString (byte array) to Hex string.
Dim k
OctetToHexStr = ""
For k = 1 To Lenb(arrbytOctet)
OctetToHexStr = OctetToHexStr & Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
Next
End Function
Sub DebugVariables()
WScript.Echo "URLName :" & URLName
WScript.Echo "StoreEntryID :" & StoreEntryID
WScript.Echo "EntryID :" & EntryID
WScript.Echo "LoggedBy :" & LoggedBy
WScript.Echo "LoggedFor :" & LoggedFor
WScript.Echo "Contacts :" & Contacts
WScript.Echo "Company :" & Company
WScript.Echo "Subject :" & Subject
WScript.Echo "Priority :" & Priority
WScript.Echo "PurchaseOrderNumber :" & PurchaseOrderNumber
WScript.Echo "RequestID :" & RequestID
WScript.Echo "Categories :" & Categories
WScript.Echo "DueDate :" & DueDate
WScript.Echo "StartDate :" & StartDate
WScript.Echo "CompletedDate :" & CompletedDate
WScript.Echo "Status :" & Status
WScript.Echo "Owner :" & Owner
WScript.Echo "Employees :" & Employees
WScript.Echo "TotalWork :" & TotalWork
WScript.Echo "ActualWork :" & ActualWork
WScript.Echo "BillingInformation :" & BillingInformation
WScript.Echo "Creator :" & Creator
WScript.Echo "CreationTime :" & CreationTime
WScript.Echo "LastModifier :" & LastModifier
WScript.Echo "LastModificationTime:" & LastModificationTime
WScript.Echo "Body :" & Body
WScript.Echo Action
WScript.Sleep 10000
End Sub
Sub DebugScript()
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("D:\DataApps\Exchange\Logs\Request" & Year(Now()) & Right("0" & Month(Now()),2) & Right("0" & Day(Now()),2) & ".txt",8,true)
wfile.write vbNewLine & Now() & "," & Action & "," & itemURL & "," & EntryID
wfile.close
set fso = nothing
set wfile = nothing
End Sub
Sub DebugError(strMessage)
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("D:\DataApps\Exchange\Logs\Error.txt",8,true)
wfile.write vbNewLine & Now() & "," & Action & "," & itemURL & "," & strMessage
wfile.close
set fso = nothing
set wfile = nothing
End Sub
So we have our SQL and we have our main VBScript, so all that was left was to create the VBScript for the event sinks, which was no different to the Time event sinks other than a reference to the folder.
Conclusion tonight.... I have both folders syncing to my SQL server, woooooohoooooo!
I wont kick off the import of 24,000 items tonight, wouldn't want it impacting tomorrows work, so will kick this off at the weekend.
I'm sooooo glad I got the boring bit of replicating the code out of the way tonight!
Next step???? Well I think it has to be syncronizing the other way (only because it's going to the next interesting bit). As far as I can tell at the point I need to:
1) Get SQL syncronizing to the existing exchange folders, could be interesting to do this via SQL triggers??
2) Get a basic front-end for the SQL data going and test parallel working with the data
3) Do some linking to private Calendars and Tasks to enable usage of Outlook at some level
4) Tart up the front end to the SQL
5) Decomission Exchange Public Folders
Shouldn't take me long :-(
Tuesday, 24 April 2007
Sunday, 15 April 2007
The big import
Well in the last post I boasted that looping through the time items was going to be easy, well it threw up a couple of interesting issues....
First off, I couldn't find an easy way to get to my Time folder by name, so in the end I copped out and just found it's EntryID and got the folder directly. But the confusing one was providing the URL to my existing script. I thought I would be able to get the URL using objMessage.Fields(&H6707001E).Value and pass that to my script. Unfortunately when retrieving this value it's already URL Encoded and unfortunately when you use ExOLEDB to find the record it doesn't want it URL Encoding. Rather than trying to decode it I decided to retrieve the EntryID (as I already had the message) and pass that to my script. Then I altered the original script to check for EntryID being passed as an argument, if it was it could skip the process to get it.
The following script is what I came up with to loop through the Time items and call the script with the relevant arguments:
providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders"
'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"
'Get the message based on the message entry ID.
Set objPubStore = objSession.InfoStores("Public Folders")
Set objRootFolder = objPubStore.RootFolder
Set objFolder = objSession.GetFolder("EF000000198262C0AA6611CD9BC800AA002FC45A020000000100000000039672")
Set WshShell = CreateObject("WScript.Shell")
For Each objMessage In objFolder.Messages
WScript.Echo "URL:" & providerURL & objMessage.Fields(&H6707001E).Value & ",EntryID:" & objMessage.Fields(&H0FFF0102).Value
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""save"" " & chr(34) & providerURL & objMessage.Fields(&H6707001E).Value & chr(34) & " " & chr(34) & objMessage.Fields(&H0FFF0102).Value & chr(34),7,True)
Next
Set WshShell = nothing
Set objFolder = Nothing
Set objRootFolder = Nothing
Set objPubStore = Nothing
objSession.Logoff
Set objSession = Nothing
Since my last post I've also noticed a couple of problems in the main script which I've since altered. Mainly to do with the SQL DataType conversion function. I've included a check for INTs so that it can put in NULL if a value isn't provided and also to check NTEXTs and TEXTs for apostrophes. This function now looks like:
Function ConvertSQLDataType(DateType,Value)
'Function to convert VB Script values to SQL values
Select Case DateType
Case "int"
If (IsNull(Value) Or Value="" Or IsEmpty(Value)) Then
ConvertSQLDataType="NULL"
Else
ConvertSQLDataType=Value
End If
Case "datetime"
ConvertSQLDataType=FormatDateTime(Value,1) & " " & FormatDateTime(Value,3)
Case "varchar", "nvarchar", "text", "ntext"
ConvertSQLDataType=Replace(Value,"'","''")
Case Else
ConvertSQLDataType=Value
End Select
End Function
The only other thing I added was some basic error checking. Unfortunately it has to be basic as VBScript doesn't support any clever form of error checking, so I've just had to guess where my script might go wrong and dump any potential errors to a text file.
So as I write this post my script is quite happily chundering throught the 87,000 or so items dumping them to the SQL DB.
The next installment should see me replicate this whole process for the Request folder.
First off, I couldn't find an easy way to get to my Time folder by name, so in the end I copped out and just found it's EntryID and got the folder directly. But the confusing one was providing the URL to my existing script. I thought I would be able to get the URL using objMessage.Fields(&H6707001E).Value and pass that to my script. Unfortunately when retrieving this value it's already URL Encoded and unfortunately when you use ExOLEDB to find the record it doesn't want it URL Encoding. Rather than trying to decode it I decided to retrieve the EntryID (as I already had the message) and pass that to my script. Then I altered the original script to check for EntryID being passed as an argument, if it was it could skip the process to get it.
The following script is what I came up with to loop through the Time items and call the script with the relevant arguments:
providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders"
'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"
'Get the message based on the message entry ID.
Set objPubStore = objSession.InfoStores("Public Folders")
Set objRootFolder = objPubStore.RootFolder
Set objFolder = objSession.GetFolder("EF000000198262C0AA6611CD9BC800AA002FC45A020000000100000000039672")
Set WshShell = CreateObject("WScript.Shell")
For Each objMessage In objFolder.Messages
WScript.Echo "URL:" & providerURL & objMessage.Fields(&H6707001E).Value & ",EntryID:" & objMessage.Fields(&H0FFF0102).Value
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""save"" " & chr(34) & providerURL & objMessage.Fields(&H6707001E).Value & chr(34) & " " & chr(34) & objMessage.Fields(&H0FFF0102).Value & chr(34),7,True)
Next
Set WshShell = nothing
Set objFolder = Nothing
Set objRootFolder = Nothing
Set objPubStore = Nothing
objSession.Logoff
Set objSession = Nothing
Since my last post I've also noticed a couple of problems in the main script which I've since altered. Mainly to do with the SQL DataType conversion function. I've included a check for INTs so that it can put in NULL if a value isn't provided and also to check NTEXTs and TEXTs for apostrophes. This function now looks like:
Function ConvertSQLDataType(DateType,Value)
'Function to convert VB Script values to SQL values
Select Case DateType
Case "int"
If (IsNull(Value) Or Value="" Or IsEmpty(Value)) Then
ConvertSQLDataType="NULL"
Else
ConvertSQLDataType=Value
End If
Case "datetime"
ConvertSQLDataType=FormatDateTime(Value,1) & " " & FormatDateTime(Value,3)
Case "varchar", "nvarchar", "text", "ntext"
ConvertSQLDataType=Replace(Value,"'","''")
Case Else
ConvertSQLDataType=Value
End Select
End Function
The only other thing I added was some basic error checking. Unfortunately it has to be basic as VBScript doesn't support any clever form of error checking, so I've just had to guess where my script might go wrong and dump any potential errors to a text file.
So as I write this post my script is quite happily chundering throught the 87,000 or so items dumping them to the SQL DB.
The next installment should see me replicate this whole process for the Request folder.
Monday, 9 April 2007
Saving Time Items to SQL
Well I had a nice break in Spain, very much just relaxing, eating and drinking.
As mentioned in the previous post the next step was to create the VBScript to execute the stored procedure to save items.
There were a few mistakes in the previous post which I've since corrected and updated. Then I moved on to the simple task of creating the script to persist the values. This is relatively easy, it's just a matter of opening a connection to the SQL server, generating the SQL script with variables to execute the stored procedure. The following VB Script holds the function to execute the procedure given a bunch of variables:
Function SaveTime(GUID, ID, URLName, StoreEntryID, EntryID, ProjectTitle, Subject, Contacts, Company, Employees, Start, EndDate, Duration, RequestID, BillingCategory, BillingDuration, TypeName, Categories, Creator, CreationTime, LastModifier, LastModifcationTime, Body)
Dim Conn,Rec,strSQL
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.ConnectionString = "Provider=SQLOLEDB;Data Source=ctsnts01;Initial Catalog=Exchange;User ID=sa;Password=************"
Conn.Open
strSQL="Exec SaveTime "
strSQL=strSQL & "@URLName='" & ConvertSQLDataType("nvarchar", URLName) & "', "
strSQL=strSQL & "@StoreEntryID='" & ConvertSQLDataType("nvarchar", StoreEntryID) & "', "
strSQL=strSQL & "@EntryID='" & ConvertSQLDataType("nvarchar", EntryID) & "', "
strSQL=strSQL & "@ProjectTitle='" & ConvertSQLDataType("nvarchar", ProjectTitle) & "', "
strSQL=strSQL & "@Subject='" & ConvertSQLDataType("nvarchar", Subject) & "', "
strSQL=strSQL & "@Contacts='" & ConvertSQLDataType("nvarchar", Contacts) & "', "
strSQL=strSQL & "@Company='" & ConvertSQLDataType("nvarchar", Company) & "', "
strSQL=strSQL & "@Employees='" & ConvertSQLDataType("nvarchar", Employees) & "', "
strSQL=strSQL & "@Start='" & ConvertSQLDataType("datetime", Start) & "', "
strSQL=strSQL & "@End='" & ConvertSQLDataType("datetime", EndDate) & "', "
strSQL=strSQL & "@Duration=" & ConvertSQLDataType("int", Duration) & ", "
strSQL=strSQL & "@RequestID='" & ConvertSQLDataType("nvarchar", RequestID) & "', "
strSQL=strSQL & "@BillingCategory='" & ConvertSQLDataType("nvarchar", BillingCategory) & "', "
strSQL=strSQL & "@BillingDuration=" & ConvertSQLDataType("int", BillingDuration) & ", "
strSQL=strSQL & "@Type='" & ConvertSQLDataType("nvarchar", TypeName) & "', "
strSQL=strSQL & "@Categories='" & ConvertSQLDataType("nvarchar", Categories) & "', "
strSQL=strSQL & "@Creator='" & ConvertSQLDataType("nvarchar", Creator) & "', "
strSQL=strSQL & "@CreationTime='" & ConvertSQLDataType("datetime", CreationTime) & "', "
strSQL=strSQL & "@LastModifier='" & ConvertSQLDataType("nvarchar", LastModifier) & "', "
strSQL=strSQL & "@LastModifcationTime='" & ConvertSQLDataType("datetime", LastModifcationTime) & "', "
strSQL=strSQL & "@Body='" & ConvertSQLDataType("ntext", Body) & "'"
Conn.Execute strSQL
Conn.Close
set rec = nothing
set conn = nothing
End Function
I created another function to convert the values dependant upon SQL data type, this is important when passing strings, to ensure that you replace single quotation marks with double one, or convert a date value in to a long date value to ensure you get around any UK/US mixup, anyway heres that procedure:
Function ConvertSQLDataType(DateType,Value)
Select Case DateType
Case "datetime"
ConvertSQLDataType=FormatDateTime(Value,1) & " " & FormatDateTime(Value,3)
Case "varchar", "nvarchar"
ConvertSQLDataType=Replace(Value,"'","''")
Case Else
ConvertSQLDataType=Value
End Select
End Function
This is all well and good, but all it's going to do is save items to the table, I need to ensure that if an item already exists then it's updated as opposed to inserted. My identifiers in SQL are GUID and ID which aren't in Exchange, so I'll use the EntryID to uniquely identify the record.
This is okay on the type of item I'm using, but you may want to consider the new behaviour of Calendar items as described in the following article http://support.microsoft.com/kb/899919, which describes how calendar items may not retain their EntryIDs.
So, I'll need to modify my SQL SaveTime Stored Procedure to check to see if the EntryID exists, if it does then update the record as opposed to inserting it, so I added the following line to the SaveTime Stored Procedure:
IF @EntryID IS NOT NULL
SET @ID = (SELECT ID FROM Time WHERE EntryID=@EntryID)
Which will get the ID of the existing item by checking for EntryID, this is done just before checking the value of ID for NULL to determine if an insert or update is needed.
So in theory at this point I've got a procedure to update or insert items in to the SQL Table to complete this portion I'd better handle deletions as well. This will involved creating a stored procedure to delete the item and the corresponding VB Script to execute it.
The Delete stored procedure is simple enough, I'll pass two variables ID and Entry ID. That way if I'm using SQL I can use ID and if I'm using Exchange I can pass the Entry ID as I won't know the SQL ID. So the stored procedure is as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteTime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteTime]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE DeleteTime
(
@ID int=0,
@EntryID nvarchar (160)=NULL
)
AS
SET NOCOUNT ON
DECLARE
@trncnt int,
@ErrorNumber int
SELECT @ErrorNumber = -1
SELECT @trncnt = @@trancount
IF @trncnt = 0
BEGIN TRANSACTION T1
ELSE
SAVE TRANSACTION T1
IF @EntryID IS NOT NULL
SET @ID = (SELECT ID FROM Time WHERE EntryID=@EntryID)
IF @ID IS NULL
SET @ID = 0
DELETE FROM Time WHERE ID=@ID
IF @@Error <> 0 GOTO ErrorHandler
IF @trncnt = 0
COMMIT TRANSACTION
RETURN (0)
ErrorHandler:
ROLLBACK TRANSACTION
RETURN (100)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
So now we need to created the VB Script to execute this stored procedure, this again is very simple and utilises most of the code already created:
Function DeleteTime(EntryID)
Dim Conn,Rec,strSQL
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.ConnectionString = connectionString
Conn.Open
strSQL="Exec DeleteTime "
strSQL=strSQL & "@EntryID='" & ConvertSQLDataType("nvarchar", EntryID) & "'"
Conn.Execute strSQL
Conn.Close
set rec = nothing
set conn = nothing
End Function
Putting this together, I altered my main script to be able to accept two parameters, an Action and a URL. Then I altered the EventSink to call my script with the appropriate parameters. So the Event Sink now looks like:
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSave(pEventInfo, bstrURLItem, lFlags)
Set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""save"" " & chr(34) & bstrURLItem & chr(34))
Set WshShell = nothing
End Sub
Sub ExStoreEvents_OnDelete(pEventInfo, bstrURLItem, lFlags)
Set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""delete"" " & chr(34) & bstrURLItem & chr(34))
Set WshShell = nothing
End Sub
</SCRIPT>
The code to retrieve these parameters in VBScript is as simple as follows:
Set obArgs = WScript.Arguments
Action = LCase(obArgs.Item(0))
itemURL = LCase(obArgs.Item(1))
So I put it all together and then ran straight in to problems. Whilst the Save event sink was firing and everything worked, the Delete event sink seemed to be doing nothing.
After a lot of troubleshooting I deduced that I would need to utilise the OnSyncDelete event, so that I could retrieve the EntryID and perform the delete. This led me to another problem with the above Event Sink, in that you can't have a Sync and an ASync function in the same sink. So I ended up splitting the Event Sink in to two files, the ASynchronous one for item saves:
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSave(pEventInfo, bstrURLItem, lFlags)
Set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""save"" " & chr(34) & bstrURLItem & chr(34))
Set WshShell = nothing
End Sub
</SCRIPT>
And the Synchronous one for item deletions
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSyncDelete(pEventInfo, bstrURLItem, lFlags)
Const EVT_SOFTDELETE = 16
Const EVT_HARDDELETE = 32
If (lFlags And EVT_HARDDELETE) OR (lFlags And EVT_SOFTDELETE) Then
Set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""delete"" " & chr(34) & bstrURLItem & chr(34),,True)
Set WshShell = nothing
End If
End Sub
</SCRIPT>
As you can see the Synchronous Deletion needed a bit more work, I did a bit of scouring on the net and discovered that I should be checking the flags to make sure we were actualling deleting this item, as per the following article http://msdn2.microsoft.com/en-us/library/ms992700.aspx.
So there we have it, I feel happy to have got this far. I now have both ASynchronous and Synchronous Exchange Event Sinks to cope for Item Saves and Deletions, this triggers some VBScript to Execute the SQL Stored Procedures. In theory we now are mirroning the Exchange folder to an SQL table!!!
Next on the list is to replicate what already exists in the Exchange to the SQL, I can see this being a simple VBScript to iterate through the exisiting items calling the Save function.
I think I've more than caught up for my abscense and that certainly is enough for one day.
As mentioned in the previous post the next step was to create the VBScript to execute the stored procedure to save items.
There were a few mistakes in the previous post which I've since corrected and updated. Then I moved on to the simple task of creating the script to persist the values. This is relatively easy, it's just a matter of opening a connection to the SQL server, generating the SQL script with variables to execute the stored procedure. The following VB Script holds the function to execute the procedure given a bunch of variables:
Function SaveTime(GUID, ID, URLName, StoreEntryID, EntryID, ProjectTitle, Subject, Contacts, Company, Employees, Start, EndDate, Duration, RequestID, BillingCategory, BillingDuration, TypeName, Categories, Creator, CreationTime, LastModifier, LastModifcationTime, Body)
Dim Conn,Rec,strSQL
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.ConnectionString = "Provider=SQLOLEDB;Data Source=ctsnts01;Initial Catalog=Exchange;User ID=sa;Password=************"
Conn.Open
strSQL="Exec SaveTime "
strSQL=strSQL & "@URLName='" & ConvertSQLDataType("nvarchar", URLName) & "', "
strSQL=strSQL & "@StoreEntryID='" & ConvertSQLDataType("nvarchar", StoreEntryID) & "', "
strSQL=strSQL & "@EntryID='" & ConvertSQLDataType("nvarchar", EntryID) & "', "
strSQL=strSQL & "@ProjectTitle='" & ConvertSQLDataType("nvarchar", ProjectTitle) & "', "
strSQL=strSQL & "@Subject='" & ConvertSQLDataType("nvarchar", Subject) & "', "
strSQL=strSQL & "@Contacts='" & ConvertSQLDataType("nvarchar", Contacts) & "', "
strSQL=strSQL & "@Company='" & ConvertSQLDataType("nvarchar", Company) & "', "
strSQL=strSQL & "@Employees='" & ConvertSQLDataType("nvarchar", Employees) & "', "
strSQL=strSQL & "@Start='" & ConvertSQLDataType("datetime", Start) & "', "
strSQL=strSQL & "@End='" & ConvertSQLDataType("datetime", EndDate) & "', "
strSQL=strSQL & "@Duration=" & ConvertSQLDataType("int", Duration) & ", "
strSQL=strSQL & "@RequestID='" & ConvertSQLDataType("nvarchar", RequestID) & "', "
strSQL=strSQL & "@BillingCategory='" & ConvertSQLDataType("nvarchar", BillingCategory) & "', "
strSQL=strSQL & "@BillingDuration=" & ConvertSQLDataType("int", BillingDuration) & ", "
strSQL=strSQL & "@Type='" & ConvertSQLDataType("nvarchar", TypeName) & "', "
strSQL=strSQL & "@Categories='" & ConvertSQLDataType("nvarchar", Categories) & "', "
strSQL=strSQL & "@Creator='" & ConvertSQLDataType("nvarchar", Creator) & "', "
strSQL=strSQL & "@CreationTime='" & ConvertSQLDataType("datetime", CreationTime) & "', "
strSQL=strSQL & "@LastModifier='" & ConvertSQLDataType("nvarchar", LastModifier) & "', "
strSQL=strSQL & "@LastModifcationTime='" & ConvertSQLDataType("datetime", LastModifcationTime) & "', "
strSQL=strSQL & "@Body='" & ConvertSQLDataType("ntext", Body) & "'"
Conn.Execute strSQL
Conn.Close
set rec = nothing
set conn = nothing
End Function
I created another function to convert the values dependant upon SQL data type, this is important when passing strings, to ensure that you replace single quotation marks with double one, or convert a date value in to a long date value to ensure you get around any UK/US mixup, anyway heres that procedure:
Function ConvertSQLDataType(DateType,Value)
Select Case DateType
Case "datetime"
ConvertSQLDataType=FormatDateTime(Value,1) & " " & FormatDateTime(Value,3)
Case "varchar", "nvarchar"
ConvertSQLDataType=Replace(Value,"'","''")
Case Else
ConvertSQLDataType=Value
End Select
End Function
This is all well and good, but all it's going to do is save items to the table, I need to ensure that if an item already exists then it's updated as opposed to inserted. My identifiers in SQL are GUID and ID which aren't in Exchange, so I'll use the EntryID to uniquely identify the record.
This is okay on the type of item I'm using, but you may want to consider the new behaviour of Calendar items as described in the following article http://support.microsoft.com/kb/899919, which describes how calendar items may not retain their EntryIDs.
So, I'll need to modify my SQL SaveTime Stored Procedure to check to see if the EntryID exists, if it does then update the record as opposed to inserting it, so I added the following line to the SaveTime Stored Procedure:
IF @EntryID IS NOT NULL
SET @ID = (SELECT ID FROM Time WHERE EntryID=@EntryID)
Which will get the ID of the existing item by checking for EntryID, this is done just before checking the value of ID for NULL to determine if an insert or update is needed.
So in theory at this point I've got a procedure to update or insert items in to the SQL Table to complete this portion I'd better handle deletions as well. This will involved creating a stored procedure to delete the item and the corresponding VB Script to execute it.
The Delete stored procedure is simple enough, I'll pass two variables ID and Entry ID. That way if I'm using SQL I can use ID and if I'm using Exchange I can pass the Entry ID as I won't know the SQL ID. So the stored procedure is as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteTime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteTime]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE DeleteTime
(
@ID int=0,
@EntryID nvarchar (160)=NULL
)
AS
SET NOCOUNT ON
DECLARE
@trncnt int,
@ErrorNumber int
SELECT @ErrorNumber = -1
SELECT @trncnt = @@trancount
IF @trncnt = 0
BEGIN TRANSACTION T1
ELSE
SAVE TRANSACTION T1
IF @EntryID IS NOT NULL
SET @ID = (SELECT ID FROM Time WHERE EntryID=@EntryID)
IF @ID IS NULL
SET @ID = 0
DELETE FROM Time WHERE ID=@ID
IF @@Error <> 0 GOTO ErrorHandler
IF @trncnt = 0
COMMIT TRANSACTION
RETURN (0)
ErrorHandler:
ROLLBACK TRANSACTION
RETURN (100)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
So now we need to created the VB Script to execute this stored procedure, this again is very simple and utilises most of the code already created:
Function DeleteTime(EntryID)
Dim Conn,Rec,strSQL
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.ConnectionString = connectionString
Conn.Open
strSQL="Exec DeleteTime "
strSQL=strSQL & "@EntryID='" & ConvertSQLDataType("nvarchar", EntryID) & "'"
Conn.Execute strSQL
Conn.Close
set rec = nothing
set conn = nothing
End Function
Putting this together, I altered my main script to be able to accept two parameters, an Action and a URL. Then I altered the EventSink to call my script with the appropriate parameters. So the Event Sink now looks like:
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSave(pEventInfo, bstrURLItem, lFlags)
Set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""save"" " & chr(34) & bstrURLItem & chr(34))
Set WshShell = nothing
End Sub
Sub ExStoreEvents_OnDelete(pEventInfo, bstrURLItem, lFlags)
Set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""delete"" " & chr(34) & bstrURLItem & chr(34))
Set WshShell = nothing
End Sub
</SCRIPT>
The code to retrieve these parameters in VBScript is as simple as follows:
Set obArgs = WScript.Arguments
Action = LCase(obArgs.Item(0))
itemURL = LCase(obArgs.Item(1))
So I put it all together and then ran straight in to problems. Whilst the Save event sink was firing and everything worked, the Delete event sink seemed to be doing nothing.
After a lot of troubleshooting I deduced that I would need to utilise the OnSyncDelete event, so that I could retrieve the EntryID and perform the delete. This led me to another problem with the above Event Sink, in that you can't have a Sync and an ASync function in the same sink. So I ended up splitting the Event Sink in to two files, the ASynchronous one for item saves:
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSave(pEventInfo, bstrURLItem, lFlags)
Set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""save"" " & chr(34) & bstrURLItem & chr(34))
Set WshShell = nothing
End Sub
</SCRIPT>
And the Synchronous one for item deletions
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSyncDelete(pEventInfo, bstrURLItem, lFlags)
Const EVT_SOFTDELETE = 16
Const EVT_HARDDELETE = 32
If (lFlags And EVT_HARDDELETE) OR (lFlags And EVT_SOFTDELETE) Then
Set WshShell = CreateObject("WScript.Shell")
strrun = WshShell.run ("d:\DataApps\Exchange\TimeItem.vbs ""delete"" " & chr(34) & bstrURLItem & chr(34),,True)
Set WshShell = nothing
End If
End Sub
</SCRIPT>
As you can see the Synchronous Deletion needed a bit more work, I did a bit of scouring on the net and discovered that I should be checking the flags to make sure we were actualling deleting this item, as per the following article http://msdn2.microsoft.com/en-us/library/ms992700.aspx.
So there we have it, I feel happy to have got this far. I now have both ASynchronous and Synchronous Exchange Event Sinks to cope for Item Saves and Deletions, this triggers some VBScript to Execute the SQL Stored Procedures. In theory we now are mirroning the Exchange folder to an SQL table!!!
Next on the list is to replicate what already exists in the Exchange to the SQL, I can see this being a simple VBScript to iterate through the exisiting items calling the Save function.
I think I've more than caught up for my abscense and that certainly is enough for one day.
Wednesday, 21 March 2007
SQL Work
It's been a little while since the last post, no excuses, just been busy with other things.
The next step from the last post was to get some SQL work done so that I could pass the changes from the Exchange Event Sink through to SQL.
So I created a DB in SQL 2000 then created the table for the Time items I was going to be persisting. The following SQL script creates the basic table in which the Time items will sit.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Time]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Time]
GO
CREATE TABLE [dbo].[Time] (
[GUID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[URLName] [nvarchar] (256) COLLATE Latin1_General_CI_AS NOT NULL ,
[StoreEntryID] [nvarchar] (160) COLLATE Latin1_General_CI_AS NOT NULL ,
[EntryID] [nvarchar] (160) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProjectTitle] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[Subject] [nvarchar] (256) COLLATE Latin1_General_CI_AS NOT NULL ,
[Contacts] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Company] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Employees] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[Start] [datetime] NULL ,
[End] [datetime] NULL ,
[Duration] [int] NULL ,
[RequestID] [nvarchar] (16) COLLATE Latin1_General_CI_AS NULL ,
[BillingCategory] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[BillingDuration] [int] NULL ,
[Type] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[Categories] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Creator] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[CreationTime] [datetime] NULL ,
[LastModifier] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[LastModifcationTime] [datetime] NULL ,
[Body] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Time] WITH NOCHECK ADD
CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Time] ADD
CONSTRAINT [DF_Time_ID] DEFAULT (newid()) FOR [GUID]
GO
Theres not much special about the above table, it just replicates the fields from the Exchange Public Folder that I reckon I will need in the eventual solution. I suppose the only thing new for me is the use of UniqueIdentifier. I thought I'd better plan this in for replication purposes, how I'm going to use it is a matter I've not thought about yet!
The next thing for me to do was to create a Stored Procedure to update this table. I wanted a simple procedure that I could use to either create a new item or, given an ID, update an item. The following Stored Procedure is what I've come up with:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaveTime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaveTime]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE SaveTime
(
@GUID uniqueidentifier=NULL,
@ID int=0,
@URLName nvarchar(256)='',
@StoreEntryID nvarchar (160),
@EntryID nvarchar (160),
@ProjectTitle nvarchar (256)=NULL,
@Subject nvarchar (256),
@Contacts nvarchar (128)=NULL,
@Company nvarchar (128)=NULL,
@Employees nvarchar (256)=NULL,
@Start datetime=NULL,
@End datetime=NULL,
@Duration int=NULL,
@RequestID nvarchar (16)=NULL,
@BillingCategory nvarchar (32)=NULL,
@BillingDuration int=NULL,
@Type nvarchar (32)=NULL,
@Categories nvarchar (128)=NULL,
@Creator nvarchar (32)=NULL,
@CreationTime datetime=NULL,
@LastModifier nvarchar (32)=NULL,
@LastModifcationTime datetime=NULL,
@Body ntext=NULL
)
AS
SET NOCOUNT ON
DECLARE
@trncnt int,
@ErrorNumber int
SELECT @ErrorNumber = -1
SELECT @trncnt = @@trancount
IF @trncnt = 0
BEGIN TRANSACTION T1
ELSE
SAVE TRANSACTION T1
IF @ID IS NULL
SET @ID = 0
IF @ID=0 --No ID passed so INSERT
BEGIN
SET @GUID=NEWID()
INSERT INTO Time
(GUID, URLName, StoreEntryID, EntryID, ProjectTitle, Subject, Contacts, Company, Employees, Start, [End], Duration, RequestID, BillingCategory, BillingDuration, Type, Categories, Creator, CreationTime, LastModifier, LastModifcationTime, Body)
VALUES
(@GUID, @URLName, @StoreEntryID, @EntryID, @ProjectTitle, @Subject, @Contacts, @Company, @Employees, @Start, @End, @Duration, @RequestID, @BillingCategory, @BillingDuration, @Type, @Categories, @Creator, @CreationTime, @LastModifier, @LastModifcationTime, @Body)
SET @ID=@@IDENTITY
END
ELSE --ID Passed so UPDATE
BEGIN
UPDATE Time
SET
URLName=@URLName,
StoreEntryID=@StoreEntryID,
EntryID=@EntryID,
ProjectTitle=@ProjectTitle,
Subject=@Subject,
Contacts=@Contacts,
Company=@Company,
Employees=@Employees,
Start=@Start,
[End]=@End,
Duration=@Duration,
RequestID=@RequestID,
BillingCategory=@BillingCategory,
BillingDuration=@BillingDuration,
Type=@Type,
Categories=@Categories,
Creator=@Creator,
CreationTime=@CreationTime,
LastModifier=@LastModifier,
LastModifcationTime=@LastModifcationTime,
Body=@Body
WHERE (ID=@ID)
END
SELECT @ID
IF @@Error <> 0 GOTO ErrorHandler
IF @trncnt = 0
COMMIT TRANSACTION
RETURN (0)
ErrorHandler:
ROLLBACK TRANSACTION
RETURN (100)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Not the biggest or most comprenhsive of posts, but it's a move forward, so that's good. The next step I have to do is write the VB Script which calls the Stored Procedure when an item changes.
But considering I'm off to Murcia in Spain on Saturday, then it's going to be at least a couple of weeks until I revisit this.
The next step from the last post was to get some SQL work done so that I could pass the changes from the Exchange Event Sink through to SQL.
So I created a DB in SQL 2000 then created the table for the Time items I was going to be persisting. The following SQL script creates the basic table in which the Time items will sit.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Time]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Time]
GO
CREATE TABLE [dbo].[Time] (
[GUID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[URLName] [nvarchar] (256) COLLATE Latin1_General_CI_AS NOT NULL ,
[StoreEntryID] [nvarchar] (160) COLLATE Latin1_General_CI_AS NOT NULL ,
[EntryID] [nvarchar] (160) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProjectTitle] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[Subject] [nvarchar] (256) COLLATE Latin1_General_CI_AS NOT NULL ,
[Contacts] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Company] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Employees] [nvarchar] (256) COLLATE Latin1_General_CI_AS NULL ,
[Start] [datetime] NULL ,
[End] [datetime] NULL ,
[Duration] [int] NULL ,
[RequestID] [nvarchar] (16) COLLATE Latin1_General_CI_AS NULL ,
[BillingCategory] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[BillingDuration] [int] NULL ,
[Type] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[Categories] [nvarchar] (128) COLLATE Latin1_General_CI_AS NULL ,
[Creator] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[CreationTime] [datetime] NULL ,
[LastModifier] [nvarchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[LastModifcationTime] [datetime] NULL ,
[Body] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Time] WITH NOCHECK ADD
CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Time] ADD
CONSTRAINT [DF_Time_ID] DEFAULT (newid()) FOR [GUID]
GO
Theres not much special about the above table, it just replicates the fields from the Exchange Public Folder that I reckon I will need in the eventual solution. I suppose the only thing new for me is the use of UniqueIdentifier. I thought I'd better plan this in for replication purposes, how I'm going to use it is a matter I've not thought about yet!
The next thing for me to do was to create a Stored Procedure to update this table. I wanted a simple procedure that I could use to either create a new item or, given an ID, update an item. The following Stored Procedure is what I've come up with:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaveTime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaveTime]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE SaveTime
(
@GUID uniqueidentifier=NULL,
@ID int=0,
@URLName nvarchar(256)='',
@StoreEntryID nvarchar (160),
@EntryID nvarchar (160),
@ProjectTitle nvarchar (256)=NULL,
@Subject nvarchar (256),
@Contacts nvarchar (128)=NULL,
@Company nvarchar (128)=NULL,
@Employees nvarchar (256)=NULL,
@Start datetime=NULL,
@End datetime=NULL,
@Duration int=NULL,
@RequestID nvarchar (16)=NULL,
@BillingCategory nvarchar (32)=NULL,
@BillingDuration int=NULL,
@Type nvarchar (32)=NULL,
@Categories nvarchar (128)=NULL,
@Creator nvarchar (32)=NULL,
@CreationTime datetime=NULL,
@LastModifier nvarchar (32)=NULL,
@LastModifcationTime datetime=NULL,
@Body ntext=NULL
)
AS
SET NOCOUNT ON
DECLARE
@trncnt int,
@ErrorNumber int
SELECT @ErrorNumber = -1
SELECT @trncnt = @@trancount
IF @trncnt = 0
BEGIN TRANSACTION T1
ELSE
SAVE TRANSACTION T1
IF @ID IS NULL
SET @ID = 0
IF @ID=0 --No ID passed so INSERT
BEGIN
SET @GUID=NEWID()
INSERT INTO Time
(GUID, URLName, StoreEntryID, EntryID, ProjectTitle, Subject, Contacts, Company, Employees, Start, [End], Duration, RequestID, BillingCategory, BillingDuration, Type, Categories, Creator, CreationTime, LastModifier, LastModifcationTime, Body)
VALUES
(@GUID, @URLName, @StoreEntryID, @EntryID, @ProjectTitle, @Subject, @Contacts, @Company, @Employees, @Start, @End, @Duration, @RequestID, @BillingCategory, @BillingDuration, @Type, @Categories, @Creator, @CreationTime, @LastModifier, @LastModifcationTime, @Body)
SET @ID=@@IDENTITY
END
ELSE --ID Passed so UPDATE
BEGIN
UPDATE Time
SET
URLName=@URLName,
StoreEntryID=@StoreEntryID,
EntryID=@EntryID,
ProjectTitle=@ProjectTitle,
Subject=@Subject,
Contacts=@Contacts,
Company=@Company,
Employees=@Employees,
Start=@Start,
[End]=@End,
Duration=@Duration,
RequestID=@RequestID,
BillingCategory=@BillingCategory,
BillingDuration=@BillingDuration,
Type=@Type,
Categories=@Categories,
Creator=@Creator,
CreationTime=@CreationTime,
LastModifier=@LastModifier,
LastModifcationTime=@LastModifcationTime,
Body=@Body
WHERE (ID=@ID)
END
SELECT @ID
IF @@Error <> 0 GOTO ErrorHandler
IF @trncnt = 0
COMMIT TRANSACTION
RETURN (0)
ErrorHandler:
ROLLBACK TRANSACTION
RETURN (100)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Not the biggest or most comprenhsive of posts, but it's a move forward, so that's good. The next step I have to do is write the VB Script which calls the Stored Procedure when an item changes.
But considering I'm off to Murcia in Spain on Saturday, then it's going to be at least a couple of weeks until I revisit this.
Saturday, 10 March 2007
Getting something useful from bstrURL
In my last post I managed to get an Exchange Event Script working which dumped out the URL of the item when it changed. All well and good, but I need to get a lot more than just the URL.
I'm dealing with extracting information from a Public Folder containing Journal items. As this isn't one of the 'standard' items dealt with by CDO I had to get a lot of the properties using their MAPI Property Tags. It seems as though a lot of people out there have asked for a standard list of these property tags, the closest list being one over at CDO Live, http://www.cdolive.com/cdo10.htm. However this still doesn't provide all the information I needed to get all of the fields I needed. So much like everyone else has, I installed a tool to exam the items via MAPI and provide me with the relevant Property Tags. Microsoft provide the 'Microsoft Exchange Server MAPI Editor' which can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=55FDFFD7-1878-4637-9808-1E21ABB3AE37&displaylang=en
So once downloaded and extracted, I ran the mfcmapi.exe and navigate down to my public folder and then selected an item. This provided a full list of properties with values etc making it easy for me to spot the Property Tags.
The next bit wasn't so straight-forward to arrive at!!! The first thing I needed to do was get the Entry ID for the item from the URL using ExOLEDB, which the next bit of code does:
itemURL = "file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/FW%3A Exclaimer News - November 2006-382499488.EML"
providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders/"
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.Provider = "ExOLEDB.DataSource"
Conn.Open providerURL
Rec.Open bstrURL
byteEntryID = Rec.Fields.Item("http://schemas.microsoft.com/mapi/proptag/x0FFF0102")
strEntryID = OctetToHexStr(byteEntryID)
Rec.Close
set rec = nothing
set conn = nothing
The Item URL is the one we got from the event sink script.
The Provider URL is just a reference to the Public Folder Store.
So we open a connection, then open the record, once we have this we get the EntryID which is Property Tag 0FFF0102.
We have to convert this value, the function to do this will be shown later on.
So now that we have the Entry ID of the Item we can use CDO an MAPI to get to that item and extract some more properties:
'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"
'Get the message based on the message entry ID.
Set objpubstore = objSession.InfoStores("Public Folders")
Set objMsg = objSession.GetMessage(strEntryID,objpubStore.id)
VB handled most of the conversion of fields automatically, the only ones I needed to pay attention to were those that stored more than one value, PT_MV_STRING8, which is array based, all I wanted was to join these to store in a simple field.
So for most of the properties the following worked:
WScript.Echo "Subject :" & objMsg.Fields(&H0037001E).Value
And for multi-value fields I simply did the following:
MultiValue=objMsg.Fields(&H819B101E)
WScript.Echo "Contacts :" & Join(MultiValue, ";")
The full script for dumping out the information is as follows:
itemURL = "file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/FW%3A Exclaimer News - November 2006-382499488.EML"
providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders/"
GetJournalItem itemURL
Sub GetJournalItem(bstrURL)
On Error Resume Next
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.Provider = "ExOLEDB.DataSource"
Conn.Open providerURL
Rec.Open bstrURL
byteEntryID = Rec.Fields.Item("http://schemas.microsoft.com/mapi/proptag/x0FFF0102")
strEntryID = OctetToHexStr(byteEntryID)
Rec.Close
set rec = nothing
set conn = nothing
'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"
'Get the message based on the message entry ID.
Set objpubstore = objSession.InfoStores("Public Folders")
Set objMsg = objSession.GetMessage(strEntryID,objpubStore.id)
WScript.Echo "URLName :" & objMsg.Fields(&H6707001E).Value
WScript.Echo "StoreEntryID :" & objMsg.Fields(&H0FFB0102).Value
WScript.Echo "EntryID :" & objMsg.Fields(&H0FFF0102).Value
WScript.Echo "ProjectTitle :" & objMsg.Fields(&H8308001E).Value
WScript.Echo "Subject :" & objMsg.Fields(&H0037001E).Value
MultiValue=objMsg.Fields(&H819B101E)
WScript.Echo "Contacts :" & Join(MultiValue, ";")
MultiValue=objMsg.Fields(&H813A101E)
WScript.Echo "Company :" & Join(MultiValue, ";")
MultiValue=objMsg.Fields(&H82E1101E)
WScript.Echo "Employees :" & Join(MultiValue, ";")
WScript.Echo "Start :" & objMsg.Fields(&H83100040).Value
WScript.Echo "End :" & objMsg.Fields(&H830F0040).Value
WScript.Echo "Duration :" & objMsg.Fields(&H830B0003).Value
WScript.Echo "RequestID :" & objMsg.Fields(&H82E2001E).Value
WScript.Echo "BillingCategory :" & objMsg.Fields(&H8307001E).Value
WScript.Echo "BillingDuration :" & objMsg.Fields(&H83060003).Value
WScript.Echo "Type :" & objMsg.Fields(&H830C001E).Value
MultiValue=objMsg.Fields(&H80DB101E).Value
WScript.Echo "Categories :" & Join(MultiValue, ";")
WScript.Echo "Creator :" & objMsg.Fields(&H3FF8001E).Value
WScript.Echo "CreationTime :" & objMsg.Fields(&H30070040).Value
WScript.Echo "LastModifier :" & objMsg.Fields(&H3FFA001E).Value
WScript.Echo "LastModifiedTime:" & objMsg.Fields(&H30080040).Value
WScript.Echo "Body :" & objMsg.Fields(&H1000000A).Value
Set objMsg = Nothing
objSession.Logoff
Set objSession = Nothing
End Sub
Function OctetToHexStr(arrbytOctet)
' Function to convert OctetString (byte array) to Hex string.
Dim k
OctetToHexStr = ""
For k = 1 To Lenb(arrbytOctet)
OctetToHexStr = OctetToHexStr & Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
Next
End Function
So I'm now getting some useful information from the URL provided by the event sink. I've hard-coded the URL in the above script because I'm still playing. So there's no link between the event sink and this script as yet, but that should be easy enough to change!
My next step is to get this stuff in to an SQL DB, but that's for another session.
I'm dealing with extracting information from a Public Folder containing Journal items. As this isn't one of the 'standard' items dealt with by CDO I had to get a lot of the properties using their MAPI Property Tags. It seems as though a lot of people out there have asked for a standard list of these property tags, the closest list being one over at CDO Live, http://www.cdolive.com/cdo10.htm. However this still doesn't provide all the information I needed to get all of the fields I needed. So much like everyone else has, I installed a tool to exam the items via MAPI and provide me with the relevant Property Tags. Microsoft provide the 'Microsoft Exchange Server MAPI Editor' which can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=55FDFFD7-1878-4637-9808-1E21ABB3AE37&displaylang=en
So once downloaded and extracted, I ran the mfcmapi.exe and navigate down to my public folder and then selected an item. This provided a full list of properties with values etc making it easy for me to spot the Property Tags.
The next bit wasn't so straight-forward to arrive at!!! The first thing I needed to do was get the Entry ID for the item from the URL using ExOLEDB, which the next bit of code does:
itemURL = "file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/FW%3A Exclaimer News - November 2006-382499488.EML"
providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders/"
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.Provider = "ExOLEDB.DataSource"
Conn.Open providerURL
Rec.Open bstrURL
byteEntryID = Rec.Fields.Item("http://schemas.microsoft.com/mapi/proptag/x0FFF0102")
strEntryID = OctetToHexStr(byteEntryID)
Rec.Close
set rec = nothing
set conn = nothing
The Item URL is the one we got from the event sink script.
The Provider URL is just a reference to the Public Folder Store.
So we open a connection, then open the record, once we have this we get the EntryID which is Property Tag 0FFF0102.
We have to convert this value, the function to do this will be shown later on.
So now that we have the Entry ID of the Item we can use CDO an MAPI to get to that item and extract some more properties:
'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"
'Get the message based on the message entry ID.
Set objpubstore = objSession.InfoStores("Public Folders")
Set objMsg = objSession.GetMessage(strEntryID,objpubStore.id)
VB handled most of the conversion of fields automatically, the only ones I needed to pay attention to were those that stored more than one value, PT_MV_STRING8, which is array based, all I wanted was to join these to store in a simple field.
So for most of the properties the following worked:
WScript.Echo "Subject :" & objMsg.Fields(&H0037001E).Value
And for multi-value fields I simply did the following:
MultiValue=objMsg.Fields(&H819B101E)
WScript.Echo "Contacts :" & Join(MultiValue, ";")
The full script for dumping out the information is as follows:
itemURL = "file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/FW%3A Exclaimer News - November 2006-382499488.EML"
providerURL = "file://./backofficestorage/ctscomputing.com/Public Folders/"
GetJournalItem itemURL
Sub GetJournalItem(bstrURL)
On Error Resume Next
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Conn.Provider = "ExOLEDB.DataSource"
Conn.Open providerURL
Rec.Open bstrURL
byteEntryID = Rec.Fields.Item("http://schemas.microsoft.com/mapi/proptag/x0FFF0102")
strEntryID = OctetToHexStr(byteEntryID)
Rec.Close
set rec = nothing
set conn = nothing
'Log on via CDO 1.21.
Set objSession = CreateObject("MAPI.Session")
objSession.Logon "", "", False, True, 0, True, "CTSNTS01" & vbLf & "Administrator"
'Get the message based on the message entry ID.
Set objpubstore = objSession.InfoStores("Public Folders")
Set objMsg = objSession.GetMessage(strEntryID,objpubStore.id)
WScript.Echo "URLName :" & objMsg.Fields(&H6707001E).Value
WScript.Echo "StoreEntryID :" & objMsg.Fields(&H0FFB0102).Value
WScript.Echo "EntryID :" & objMsg.Fields(&H0FFF0102).Value
WScript.Echo "ProjectTitle :" & objMsg.Fields(&H8308001E).Value
WScript.Echo "Subject :" & objMsg.Fields(&H0037001E).Value
MultiValue=objMsg.Fields(&H819B101E)
WScript.Echo "Contacts :" & Join(MultiValue, ";")
MultiValue=objMsg.Fields(&H813A101E)
WScript.Echo "Company :" & Join(MultiValue, ";")
MultiValue=objMsg.Fields(&H82E1101E)
WScript.Echo "Employees :" & Join(MultiValue, ";")
WScript.Echo "Start :" & objMsg.Fields(&H83100040).Value
WScript.Echo "End :" & objMsg.Fields(&H830F0040).Value
WScript.Echo "Duration :" & objMsg.Fields(&H830B0003).Value
WScript.Echo "RequestID :" & objMsg.Fields(&H82E2001E).Value
WScript.Echo "BillingCategory :" & objMsg.Fields(&H8307001E).Value
WScript.Echo "BillingDuration :" & objMsg.Fields(&H83060003).Value
WScript.Echo "Type :" & objMsg.Fields(&H830C001E).Value
MultiValue=objMsg.Fields(&H80DB101E).Value
WScript.Echo "Categories :" & Join(MultiValue, ";")
WScript.Echo "Creator :" & objMsg.Fields(&H3FF8001E).Value
WScript.Echo "CreationTime :" & objMsg.Fields(&H30070040).Value
WScript.Echo "LastModifier :" & objMsg.Fields(&H3FFA001E).Value
WScript.Echo "LastModifiedTime:" & objMsg.Fields(&H30080040).Value
WScript.Echo "Body :" & objMsg.Fields(&H1000000A).Value
Set objMsg = Nothing
objSession.Logoff
Set objSession = Nothing
End Sub
Function OctetToHexStr(arrbytOctet)
' Function to convert OctetString (byte array) to Hex string.
Dim k
OctetToHexStr = ""
For k = 1 To Lenb(arrbytOctet)
OctetToHexStr = OctetToHexStr & Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
Next
End Function
So I'm now getting some useful information from the URL provided by the event sink. I've hard-coded the URL in the above script because I'm still playing. So there's no link between the event sink and this script as yet, but that should be easy enough to change!
My next step is to get this stuff in to an SQL DB, but that's for another session.
Tuesday, 6 March 2007
Playing with Event Sinks
The first task I had on my list was to understand Exchange Event Sinks and to try and implement a script so that changes made to a public folder triggered 'an event'.
I did a lot of browsing on the web and came across lots of articles that eventually pieced together. The promising starting point came from Glen's Exchange Dev Blog, http://gsexdev.blogspot.com/2004/06/public-folder-rss-feed-event-sink.html. This looked like an ideal place to start and soon led me to a couple of other articles for those vital first steps, one of the best being another of Glen's, http://www.outlookexchange.com/articles/glenscales/wssevtar.asp.
So the first thing I had to do was register the Script Host Sink on my Exchange server as per article http://support.microsoft.com/kb/q264995/. Essentially do the following:
Copy the ESHMTS.VBS from your Exchange CD (mine was located in Setup\I386\Exchange) to your ExchSrvr\Bin directory on the server.
Bring up a command prompt and navigate to your ExchSrvr\Bin folder then enter the following:
regsvr32.exe exodbesh.dll
regsvr32.exe exodbprx.dll
cscript eshmts.vbs install
So now that the COM bit is done we can start playing with some scripts. I need to capture whenever an item is created, changed or deleted. A bit more research discovered that this is just down to two events
ExStoreEvents_OnSave
and
ExStoreEvents_OnDelete
I utilised Glen's example and expanded in to include the OnDelete as well:
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSave(pEventInfo, bstrURLItem, lFlags)
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("D:\DataApps\Exchange\Time.txt",8,true)
wfile.write vbNewLine & "Saved:" & (bstrURLItem)
wfile.close
set fso = nothing
set wfile = nothing
End Sub
Sub ExStoreEvents_OnDelete(pEventInfo, bstrURLItem, lFlags)
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("D:\DataApps\Exchange\Time.txt",8,true)
wfile.write vbNewLine & "Deleted:" & (bstrURLItem)
wfile.close
set fso = nothing
set wfile = nothing
End Sub
</SCRIPT>
All this script is doing is dumping the URL to the item out to a text file whenever either event is fired. All well and good, but what we need to do next is register this script, which we can't do until we have the script to do the registration, which isn't there by default!! We have to download and install the Exchange SDK, which can be found here, http://www.microsoft.com/downloads/details.aspx?FamilyID=32774e09-4984-458e-bdb7-ed2bb356bd27&DisplayLang=en
Once you've downloaded and installed the SDK you can then copy the three files you need from, in my case, C:\Program Files\Exchange SDK\SDK\Support\OLEDB\Scripts to your development folder.
So we have our script and we have the script to register it, so we just need to put the two together. To make mine work I ran the following from the command prompt:
cscript regevent.vbs ADD "onsave;ondelete" ExOleDB.ScriptEventSink.1 "file://./backofficestorage/ctscomputing.com/public folders/customer services/time/evtsnk1" -file d:\DataApps\Exchange\Time.vbs
This registers my script Time.vbs with the OnSave and OnDelete events in the public folder Customer Services\Time
I thought I'd better also find out how to unregister these events, which the following line does:
cscript regevent.vbs delete file://./backofficestorage/ctscomputing.com/public folders/customer services/time/evtsnk1
I then proceeded to create, change and delete some items, which triggered the events and generated the following text file:
Saved:file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/Test-126958582.EML
Saved:file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/Test-126958582.EML
Deleted:file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/Test-126958582.EML
Woohoo, I have an Exchange Event Sink working. I know it's not doing anything useful at the moment, but at least it's a step in the right direction.
Next thing on the agenda is to get some useful information from these links, but that's for another time!
I did a lot of browsing on the web and came across lots of articles that eventually pieced together. The promising starting point came from Glen's Exchange Dev Blog, http://gsexdev.blogspot.com/2004/06/public-folder-rss-feed-event-sink.html. This looked like an ideal place to start and soon led me to a couple of other articles for those vital first steps, one of the best being another of Glen's, http://www.outlookexchange.com/articles/glenscales/wssevtar.asp.
So the first thing I had to do was register the Script Host Sink on my Exchange server as per article http://support.microsoft.com/kb/q264995/. Essentially do the following:
Copy the ESHMTS.VBS from your Exchange CD (mine was located in Setup\I386\Exchange) to your ExchSrvr\Bin directory on the server.
Bring up a command prompt and navigate to your ExchSrvr\Bin folder then enter the following:
regsvr32.exe exodbesh.dll
regsvr32.exe exodbprx.dll
cscript eshmts.vbs install
So now that the COM bit is done we can start playing with some scripts. I need to capture whenever an item is created, changed or deleted. A bit more research discovered that this is just down to two events
ExStoreEvents_OnSave
and
ExStoreEvents_OnDelete
I utilised Glen's example and expanded in to include the OnDelete as well:
<SCRIPT LANGUAGE="VBScript">
Sub ExStoreEvents_OnSave(pEventInfo, bstrURLItem, lFlags)
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("D:\DataApps\Exchange\Time.txt",8,true)
wfile.write vbNewLine & "Saved:" & (bstrURLItem)
wfile.close
set fso = nothing
set wfile = nothing
End Sub
Sub ExStoreEvents_OnDelete(pEventInfo, bstrURLItem, lFlags)
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("D:\DataApps\Exchange\Time.txt",8,true)
wfile.write vbNewLine & "Deleted:" & (bstrURLItem)
wfile.close
set fso = nothing
set wfile = nothing
End Sub
</SCRIPT>
All this script is doing is dumping the URL to the item out to a text file whenever either event is fired. All well and good, but what we need to do next is register this script, which we can't do until we have the script to do the registration, which isn't there by default!! We have to download and install the Exchange SDK, which can be found here, http://www.microsoft.com/downloads/details.aspx?FamilyID=32774e09-4984-458e-bdb7-ed2bb356bd27&DisplayLang=en
Once you've downloaded and installed the SDK you can then copy the three files you need from, in my case, C:\Program Files\Exchange SDK\SDK\Support\OLEDB\Scripts to your development folder.
So we have our script and we have the script to register it, so we just need to put the two together. To make mine work I ran the following from the command prompt:
cscript regevent.vbs ADD "onsave;ondelete" ExOleDB.ScriptEventSink.1 "file://./backofficestorage/ctscomputing.com/public folders/customer services/time/evtsnk1" -file d:\DataApps\Exchange\Time.vbs
This registers my script Time.vbs with the OnSave and OnDelete events in the public folder Customer Services\Time
I thought I'd better also find out how to unregister these events, which the following line does:
cscript regevent.vbs delete file://./backofficestorage/ctscomputing.com/public folders/customer services/time/evtsnk1
I then proceeded to create, change and delete some items, which triggered the events and generated the following text file:
Saved:file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/Test-126958582.EML
Saved:file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/Test-126958582.EML
Deleted:file://./backofficestorage/ctscomputing.com/Public Folders/Customer Services/Time/Test-126958582.EML
Woohoo, I have an Exchange Event Sink working. I know it's not doing anything useful at the moment, but at least it's a step in the right direction.
Next thing on the agenda is to get some useful information from these links, but that's for another time!
Friday, 2 March 2007
First Adventure (Well actually a lie, but it's been a while)
Okay, lets go....
I'm trying to move an ageing Exchange based solution to SQL. Two reasons, 1) It's sloooow, 2) Public Folders will disappear so I better do something!!!
So I thought I'd get back in to a bit of development and try and get this stuff moved over.
Major problem being time, as ever no-one has enough. So I need to be able to develop a solution over time such that the existing public folder mechanism can be used whilst I move on to the SQL version.
Now I've tried doing this stuff in the past using the Outlook based API, but I ran in to problems because I could never trap the deletion of items. So no matter how much I thought I was synching it was real as deletions never happened.
I knew the answer laid in Exchange Event Syncs, but I never had the balls to give it a go. But over the past week or so I thought I'd give it another go.
I've read shed loads of articles on this, mainly due to the fact that I'm lost in a lot of new technologies that I've never touched.
Hopefully over the next few posts I will try and surmise what I've found out about synching a Public Folder with an SQL database. I don't think it would have been so bad if the two folders in question weren't of the 'preferred' type, i.e. one being a Task folder (
) the other being a Journal folder (
).
So to sum up, what I'm trying to achieve is to sync a Task and Journal Public folder with an SQL database, with the view to decommissioning the Public Folders and moving to an all glorious SQL solution. But I do love Exchange so, I'm hoping to move to SQL but sync from SQL to Mailbox Calendars/Messages/Tasks in order to be able to use Outlook and Mobile devices to interact with the DB.
It's a tall task for someone whose day job is support and not development, so it will probably take a while!!
I'm trying to move an ageing Exchange based solution to SQL. Two reasons, 1) It's sloooow, 2) Public Folders will disappear so I better do something!!!
So I thought I'd get back in to a bit of development and try and get this stuff moved over.
Major problem being time, as ever no-one has enough. So I need to be able to develop a solution over time such that the existing public folder mechanism can be used whilst I move on to the SQL version.
Now I've tried doing this stuff in the past using the Outlook based API, but I ran in to problems because I could never trap the deletion of items. So no matter how much I thought I was synching it was real as deletions never happened.
I knew the answer laid in Exchange Event Syncs, but I never had the balls to give it a go. But over the past week or so I thought I'd give it another go.
I've read shed loads of articles on this, mainly due to the fact that I'm lost in a lot of new technologies that I've never touched.
Hopefully over the next few posts I will try and surmise what I've found out about synching a Public Folder with an SQL database. I don't think it would have been so bad if the two folders in question weren't of the 'preferred' type, i.e. one being a Task folder (
) the other being a Journal folder (
).So to sum up, what I'm trying to achieve is to sync a Task and Journal Public folder with an SQL database, with the view to decommissioning the Public Folders and moving to an all glorious SQL solution. But I do love Exchange so, I'm hoping to move to SQL but sync from SQL to Mailbox Calendars/Messages/Tasks in order to be able to use Outlook and Mobile devices to interact with the DB.
It's a tall task for someone whose day job is support and not development, so it will probably take a while!!
Subscribe to:
Posts (Atom)
