Trying to move Exchange to SQL: Create Table

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 :-(

Trying to move Exchange to SQL

Tuesday, 24 April 2007

Deja Vu

Wednesday, 21 March 2007

SQL Work

Blog Archive

About Me

StatCounter

Technorati