Years ago we developed simple application in MS Access for keeping different requests from the users in one place, we chose MS Access as a front end and one existing MySQL server for the back-end (our decision to use MySQL was a deliberate one – anyway, it well might be any other RDBMS, it’s not important fact in the context of this memo).
Basically the design of the application was very simple, when someone from DBA team received an e-mail request from the user he/she entered some meta information in the database plus Word document that was attached to the message by simply copy/pasting word document in MS Access. The inserted document was stored in MySQL blob field as OLE document (doc with some binary meta data envelope). Here is the screenshot:

The small preview image of the “inserted” Word document for example is stored in MySQL BLOB field as part of the OLE envelope. As long as we’re happy with the MS Access as a front end we can always extract the doc by double clicking the preview image or by right clicking the image and selecting Open from the context menu.

We plan to rewrite the application, most likely with Oracle Apex on top of Oracle XE. We faced the problem of how to migrate our data from MySQL to Oracle XE. Word documents stored in blob fields are really not true (binary) .doc files any more, but OLE objects (some binary header plus doc). We thought that finding official documentation about OLE object header would be easy and that all we’ll have to do is to cut off the OLE header from each document file stored in blob field. We’re wrong.
To make long story short, we found the solution on Stephen Lebens site. He wrote MS Access application that can extract (any) OLE content from the mdb file and save the document in native format. You can download ExtractInventoryOLE.zip from here.

First we imported table with OLE objects from MySQL to local mdb file and then extracted the word documents with above application. Several hundreds Word documents where dumped without a hitch in less than a minute.

Here is a screenshot from ExtractInventoryOLE to get a filling:

You can select unique field (in our case it was a primary key for the table; a numeric sequence) that is used to name the documents. The good news is that you can extract the OLE objects even if OLE Server that was used to insert the document is not present/installed on the machine (for example MS Word, Excel etc.).

I’m not sure why Microsoft doesn’t ship compiled version of ascmd with SQL Server 2005!? Ascmd as you might know is command lined tool that you can use to execute xmla scripts in addition to MDX queries and DMX statements.
My only interest at present is to use ascmd to backup OLAP mode database from command line (if you’re storing data as ROLAP or HOLAP, then make sure you read Backup Strategies with SQL Server 2005 Analysis Services).

Essential references:

• Readme For Ascmd Command-line Utility Sample
• I found compiled version of ascmd at Project Real homepage, more precisely in package ProjectREALRefImpl_10.zip. Unpack ProjectREALSampleCodeAndTools.msi from zip file and install the package, then search for ascmd.exe. I picked up binary for x64.

Copy ascmd to your OLAP server (like ?:\MSSQL\MSSQL.1\OLAP\bin). All that you need now is xmla script that can be generated within MS SQL Server Management Studio and simple batch file, such as:

SET PATH=?:\MSSQL\MSSQL.1\OLAP\bin;%PATH%

ascmd -i BackupOLAP.xmla

if errorlevel 0 echo Backup BACKUPOLAP= OK    > ?:\OLAPBackup\BackupOLAP.log
if errorlevel 1 echo Backup BACKUPOLAP= ERROR > ?:\OLAPBackup\BackupOLAP.log

and BackupOLAP.xmla might look like this:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  
  <File>?:\OLAPBackup\BackupOLAP.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
</Backup>

…replacing ? with the appropriate drive letter, of course.

It’s a matter of time when Master Boot Record (MBR) partitioning schema will be replaced by more scalable, flexible and robust GUI Partition Table (GPT) partitioning schema. I found very good Microsoft FAQ article about GPT on Windows OS. I’m sure I’ll need to refer to this article from time to time, so I think it deserves this short note here.

I’m in the middle of a process to prepare our venerable SAN disk subsystem for retirement. It served us well for the last six years. Due to confidentiality of data once stored on this system I opted to erase data by first formatting all logical volumes at OS level, followed by erasing data with specialized tool that writes random data with the choice of several runs and finally formatting all volumes low-level with SAN management tool. Step two and three are time consuming parts of the process, that will take approx. 3 full days. And I’m wiping out a mere 1.6TB system!
If you’re looking for specialized tool for destroying data I suggest that you start here. Depending of the platform choose the most appropriate tool. I tried Eraser for Windows and it performed well. I think this is another tool to add to my list of the free open source software that I use.

I was looking for a tool that can help me search Event Log’s on the number of servers for particular event. My first thought was to do an “exercise” by writing python script, when a colleague of mine sent me a link to Microsoft article How to use the EventCombMT utility to search event logs for account lockouts. EventCombMT is part of the “Account Lockout and Management Tools” but it can be used for generic log event searches. After downloading the pack and starting the EventCombMT, my first impression was that this will not suite my need as it’s a GUI and I would prefer a command line version of the tool. Hopefully, I checked the Help before dumping the tool, where I found out that we can easily use the tool from the command line as well.

Let’s see EventCombMT in action:

We want to search Oracle server (ORASERVER1) Event log for critical error that indicates that Fibre Channel path connected to our SAN is down. I’m searching for Event 5 with the text similar to this one: “Path1 removed from multipath device nn by MPIO”.

cmd> EventCombMT /s:oraserver1  /evt:"5" /et:we /log:sys /outdir:"C:\temp" /t:1 /after:01152008120000 /before:02122008120000 /start

The entire line must be executed as a single cmd line!

Short explanation of the switches:

• server /s (we can specify file with the server names instead)
• we’re searching for event #5 (/evt:”5″) that is associated with the error, such as:
  sdddsm “Path1 removed from multipath device nn by MPIO”
• we’re interesting in two event types; Warning or Error (/et:we)
• we want to limit the search to System part of the event log (/log:sys)
• how many threads should EventCombMT use for the search (/t:1)
• time interval for the search (/after, /before) in the format MMDDYYYYHHMMSS (this format is mandatory)
• we want to execute the search from the command line (/start)

The command will produce two files, the first one is called EventCombMT.txt and it’ll look something like this:

Find Events After: Tue Jan 15 12:00:00 2008 
Find Events Before: Tue Feb 12 12:00:00 2008 
Searching System Logs
Event IDs:   5
No Event Text specified.
No Event Source specified.
No Between Event IDs specified.
Will Search the following servers:
oraserver1
To find these events we'll need a search running. It has already begun....
 
Spawning Thread for: oraserver1
Thread Running for: oraserver1
Opening: C:\temp\oraserver1-System_LOG.txt
Number Of Records for the System log on oraserver1 is 1248
Total Bytes Read ending with the System log on oraserver1: 189124
C:\temp\oraserver1-System_LOG.txt contains 22 parsed events.
Exiting thread for: oraserver1
All threads Scheduled to run are running.
Total events searched: 1248
Total matches found: 22
Servers/Logs Searched: 1
DLL Cache Contained: 0
SID Cache Contained: 0
Start time: Tue Feb 12 16:12:51 2008 
Finish time: Tue Feb 12 16:12:51 2008 
True records per second: 1248.00

and the second file oraserver1-System_LOG.txt that will contain events that were found in the system log:

5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 13 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 12 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 11 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 10 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 9 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 8 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 7 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 6 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 5 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 4 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 3 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 2 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 1 
5,WARNING,sdddsm,Sat Jan 19 18:57:41 2008,No User,  1 0 
5,WARNING,sdddsm,Fri Jan 18 16:25:52 2008,No User,  1 13 
5,WARNING,sdddsm,Fri Jan 18 16:25:52 2008,No User,  1 12 
5,WARNING,sdddsm,Fri Jan 18 16:25:51 2008,No User,  1 11 
5,WARNING,sdddsm,Fri Jan 18 16:25:51 2008,No User,  1 10 
5,WARNING,sdddsm,Fri Jan 18 16:25:51 2008,No User,  0 13 
5,WARNING,sdddsm,Fri Jan 18 16:25:51 2008,No User,  0 12 
5,WARNING,sdddsm,Fri Jan 18 16:25:50 2008,No User,  0 11 
5,WARNING,sdddsm,Fri Jan 18 16:25:50 2008,No User,  0 10 
C:\temp\oraserver1-System_LOG.txt contains 22 parsed events.

At this point I can write a simple script (with python, of course;-) that’ll check for a presence of certain event in the logfile and send SMS alert to my friendly Motorola Tamagotchi.