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.
Monday 9 April 2007
Subscribe to:
Posts (Atom)
