Jan Viktor Apel

I do IT

Launching of casamolles.com

Yesterday I launched casamolles.com. A website dedicated to our holiday house in Los Molles Chile. The website was made with WordPress and the awesome theme “propulsion” by Kriesi. For the booking forms and calendars I used another awesome plugin called Booking System Pro by DOTonPAPER.
So if you feel like visiting Chile, don’t miss this beautiful place and off course this beautiful house. (price from 55€/night, minimum 4 nights)
Home - Casa Molles
Soon I will add other languages (spanish, english) to it.

A simple UPSERT SQL Command

Why is it so complicated to create a simple UPDATE OR INSERT statement in Microsoft SQL Server? This whole MERGE thing is too complicated. There should be a simple UPSERT command, if the specific row exists, it should UPDATE, if not, INSERT…

I’d love to see a feature like this in future versions of Microsoft SQL Server.

Collaboration Data Objects and Office 2010


Warning: array_keys() [function.array-keys]: The first argument should be an array in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 2039

Warning: Invalid argument supplied for foreach() in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 2039

Warning: Invalid argument supplied for foreach() in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 2411

Warning: implode() [function.implode]: Argument must be an array in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 3365

Warning: array_keys() [function.array-keys]: The first argument should be an array in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 3388

Warning: Invalid argument supplied for foreach() in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 3388

Warning: Invalid argument supplied for foreach() in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 3429

Warning: array_keys() [function.array-keys]: The first argument should be an array in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 3481

Warning: Invalid argument supplied for foreach() in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 3481

Warning: array_keys() [function.array-keys]: The first argument should be an array in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 3626

Warning: Invalid argument supplied for foreach() in /homepages/14/d13578737/htdocs/www.jan-apel.de/wp-content/plugins/wp-syntax/geshi/geshi.php on line 3626

At the company I work for, we use IBM CommonStore to archive every email sent over Microsoft Exchange Server. Initially our CommonStore installation worked well, although we had to restart the DB2 Server once in a while, because it automatically shut down. But that was not a real problem.

The first problems came with Microsoft Windows 7. The IBM CommonStore Client installer had problems with the Windows 7 GUI, so we had to install it via the console. That, well, was also not really a problem, but kind of annoying.

Anyway the real problems came, as we started to install Microsoft Office 2010 after sucessfully skipping Microsoft Office 2003 and 2007. IBM CommonStore needs Collaboration Data Objects to work with Exchange and soon we had to realize that Collaboration Data Objects (CDO) 1.2.1 was not supported with Outlook 2010.

IBM CommonStore doesn’t delete the E-Mails from the users inbox, it just removes the attachments, stores them somewhere else and adds a link to the stored .msg file to be able to retrieve the email with attachment again. So there is no need to install the CommonStore Client and Outlook addon, if the user does not delete those messages. If the user deletes the messages, there is only one way to retrieve them, install the CommonStore Client and click on the search button, which opens a browser window redirecting to a web gui to search for messages in CommonStore.

We were told that CommonStore would not work on Office 2010 because Office 2010 doesn’t support Collaboration Data Objects anymore.

So, how do I get the emails back? I normally don’t believe these statements until I’ve seen and tested it by myself. So the first thing I did was to google “office 2010 cdo”. And there it was:

Although the Collaboration Data Objects (CDO) 1.2.1 object library could be used with Microsoft Outlook 2010, we do not recommend or support this in any way.

So it is possible! I then just downloaded a copy of Collaboration Data Objects at microsoft and tried to install it, without success:

Collaboration Data Objects 1.2.1 cannot be installed unless Microsoft Office Outlook 2007 is also installed.

But I was not, in any way beaten by this error. I thought it must be possible to modify the msi package and delete this annoying check. So I downloaded Microsoft Orca (e.g. included in the Microsoft Windows Server 2003 SP1 Platform SDK), extracted the msi package from the CDO installation exe file and opened the msi package with Microsoft Orca:

Exchange Collaboration Data Objects MSI opened with Microsoft Orca

And there it was. The table “LaunchCondition” contains the “OUTLOOK12INSTALLED” check. I just deleted it, saved the file and tried to install again. This time I got a lot further, but sadly again unsuccessful. The problem this time, a very typical non-sense msi error:

The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2356.

Oh, right, how could I forget! Error 2356! Off course! No, wait a minute, that makes no sense at all! Lets look it up at microsoft.

So.. Could not locate cabinet in stream… But why? I only deleted a row in the table “LaunchCondition”… Oh wait! It is a stream, so somewhere you have to tell the msi file where the stream starts and where the stream ends. So if I edit the msi file and save my modified version, the stream isn’t in the same address anymore. So my next idea was to extract the cabinet file from the msi package and somehow tell the msi package to use my extracted cabinet file instead of the stream. I found a visual basic script written by Adriaan Westra to do this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
'******************************************************************************
'** Script:    ExtrCab.vbs
'** Version:   1.0
'** Created:   30/01/2009 10:55AM
'** Author:    Adriaan Westra
'** E-mail:         
'**
'** Purpose / Comments:
'**            Extract cabinet files from MSI file.
'**
'**
'**
'** Changelog :
'** date / time         : 
'** 30/01/2009 10:55AM  :   Initial version
'**
'******************************************************************************
 
'*****************************************************************
'** Add Error Handling
on error resume Next
 
'*****************************************************************
'** Make sure the script is started with cscript
If InStr(wscript.FullName, "wscript.exe") > 0 Then
MsgBox "Please run this script with cscript.exe." & Chr(13) & _
"For example : cscript " & WScript.ScriptName & " /?", _ 
vbExclamation, WScript.ScriptName
WScript.Quit(1)
End If
 
'*****************************************************************
'** Get commandline parameters
Set Args = Wscript.Arguments
 
If Args.Count = 0 Then
	strMsiFile = InputBox("Please give the MSI File name " & _
	"to process : ",wscript.scriptname, strPath)
Else
   If InStr(Args(0),"/?") > 0 Or InStr(UCase(Args(0)),"/H") > 0 _ 
   Or InStr(UCase(Args(0)),"/HELP") > 0 Then
      DisplayHelp
      Wscript.quit(0)
   Else
      strMsiFile = Args(0)
   End if
End if
 
Const msiOpenDatabaseModeReadOnly = 0 
Const msiOpenDatabaseModeTransact = 1
Const msiOpenDatabaseModeListScript = 5
Const msiReadStreamAnsi = 2
 
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objMSI = CreateObject("WindowsInstaller.Installer")
Set objMsiDB = objMSI.OpenDataBase(strMsiFile, msiOpenDatabaseModeReadOnly ) : CheckError
 
Set objView =  objMsiDB.Openview("Select Cabinet From Media") : CheckError
objView.Execute : CheckError
Set objRecord = objView.Fetch
Do until objRecord is Nothing
   If InStr(objRecord.StringData(1) ,"#") = 1 Then
      strCabFile = Mid(objRecord.StringData(1),2, Len(objRecord.StringData(1)) - 1)
   Else
      Wscript.echo "No internal cab file in this MSI"
      wscript.quit(1)
   End If
   strSQL = "Select * From _Streams where Name = '" & strCabFile & "'"
   Set objViewCab =  objMsiDB.Openview(strSQL) : CheckError
 
   objViewCab.Execute : CheckError
   Set objRecordCab = objViewCab.Fetch
   If not objRecordCab is Nothing Then
      binData = objRecordCab.readstream(2, objRecordCab.datasize(2), msiReadStreamAnsi )
      If InStr(strCabFile,".cab") = 0 Then
         strCabFile = strCabFile & ".cab"
      End If
      Set objCabFile = objFSO.CreateTextFile(strCabFile, True)
      objCabFile.Write binData
      objCabFile.Close
       wscript.echo "Created : " &  strCabFile
   End If  
   Set objRecord = objView.Fetch 
Loop
 
Set objRecord = nothing
Set objMsiDB = nothing
Set objMsi = nothing
 
Wscript.Quit 0
 
Sub CheckError
  Dim message, errRec
  If Err = 0 Then Exit Sub
  message = Err.Source & " " & Hex(Err) & ": " & Err.Description
  If Not installer Is Nothing Then
  	Set errRec = installer.LastErrorRecord
  	If Not errRec Is Nothing Then message = message & vbNewLine & errRec.FormatText
  End If
  Wscript.Echo message
  Wscript.Quit 2
End Sub

Then in command line:

ExtrCab.vbs ExchangeCdo.MSI

And voila! The extracted file (Data.cab) is there! Now we have to tell the msi to use the extracted cabinet. So I opened Orca again, searched for “Data.cab” and found this:

Microsoft Orca finding the reference to the cabinet file

Still didn’t know how to tell the msi package to use the extracted cabinet file, so I googled more and more, and found an interesting site with information about working with cabinet files.

If the cabinet name is not preceded by the number sign #, the cabinet is stored in a separate file located at the root of the source tree specified by the Directory Table. The cabinet file must use the short file name syntax consisting of an eight character name, a period, and a three character extension. The Cabinet data type cannot use the short|longname syntax for file names. If the cabinet file is stored as a separate file, the name of the cabinet file is not case-sensitive.”

So I just deleted the number sign, saved the msi file and tried again and…. tadaaaaaa!

You have successfully installed Microsoft Collaboration Data Objects 1.2.1 on this computer.

I don’t know the reasons why it isn’t supported anymore, but I found a blog post at msdn.com explaining it a bit more:

Collaboration Data Objects (CDO) 1.2.1 is a client library that provides a thin wrapper over Extended MAPI functionality. CDO does not install with Outlook 2010, and is available only as a download. For more information, see Collaboration Data Objects, version 1.2.1 in the Microsoft Download Center. CDO is not guaranteed to work correctly in an environment with multiple Exchange accounts. CDO 1.2.1 is a 32-bit client library and will not operate with 64-bit Outlook 2010. Therefore, CDO 1.2.1 is not supported for use with Outlook 2010. Most of the CDO 1.2.1 functionality has been incorporated into the Outlook 2010 object model.”

So it won’t work on 64bit installations of Microsoft Office 2010 and it is not guaranteed to work correctly in an environment with multiple Exchange accounts.

After all I was able to install the CommonStore client and it works perfectly. Maybe it works for you too?

EDIT 05.08.2014: As Charlie states in the comments it seems to be possible to trick Microsoft into thinking one had Office 2007 installed by modifying some registry keys:

Hi Jan,

I came across your article and it got me thinking/experimenting.

As it turns out 2 changes in the registry will allow CDO to be installed with Outlook 2010 WITHOUT having to modify the MSI file.

In HKEY_CLASSES_ROOT\Outlook.Application\CurVer change the (Default) value from Outlook.Application.14 to Outlook.Application.12

Now rename HKEY_CLASSES_ROOT\Outlook.Application.14 to HKEY_CLASSES_ROOT\Outlook.Application.12

And voila, the MSI has been tricked into thinking that Outlook 2007 is installed, and will now install.

Don’t forget to rename both the key and (Default) value back when finished

Charlie

Thank you  Charlie!