Monday, December 07, 2009

Moving the C:\WINDOWS\INSTALLER Directory

Often servers or workstations will run low of disk space on the system partition.  This is especially true I’ve found with older Dell servers that shipped with a 12GB system partition.

The c:\windows\installer directory often occupies several gigabytes of storage.

While Windows does not provide a mechanism to re-point this directory via the registry, you can use NTFS junctions/reparse points to re-point the directory to a different local volume.  Re-pointing to UNC paths is not supported.

  1. Obtain junction.exe from

    I found it handy to add the executable to c:\windows\system32 for easy access

  2. Create the destination Installer folder on the new volume (ie: D:\Installer)

  3. Move all content from c:\windows\installer to your new directory

  4. Make sure to close any explorer windows with these folders opened.  Junction.exe requires that there are no open file handles to these directories

  5. Create the junction

    junction.exe c:\windows\installer d:\installer

Monday, November 30, 2009

Blackberry Enterprise Server - “The personal redirection folders are unavailable”

After upgrading our corporate Blackberry Enterprise Server (BES) from 5.0 to 5.0SP1, all of our users started complaining that most if not all of their contacts were now missing.

From the Web Desktop Manager, and choosing the “Redirection Folders” section (where you configure which folders are redirected), the following error message was displayed at the top.

The personal redirection folders are unavailable. Contact your system administrator.

Great, they want me to contact myself.  Typical.

The Desktop Manager did not show any errors but also would not allow me to select any folders or my default private contact folder.

A glance through some of the BES logs showed messages like:

javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.SQLGrammarException: could not insert: [com.rim.bes.basplugin.synchronization.entity.SyncFolderListEntity]

[org.hibernate.util.JDBCExceptionReporter] [ERROR] Cannot insert duplicate key row in object 'dbo.SyncFolderList' with unique index 'IX_SyncFolderList_GUIDLoc'.

I happen to be a DBA, so I decided to dig right in.

First and foremost, before you perform any type of manual database manipulation against your BESMgmt database, TAKE A FULL BACKUP.  Did I mention you should take a FULL BACKUP?   Did you take a FULL backup? Ok good.  Also, if you are not familiar with SQL or simply don’t want to risk  bringing down your environment, I recommend you contact RIM support.

There are two database tables that control the bulk of this.  [dbo].[SyncFolderUserList] and [dbo].[SyncFolderList].  The [dbo].[UserConfig] table also contains all of the users, and you will need to reference it for this operation.

First, I looked up my user id from the UserConfig table.
SELECT *  FROM [dbo].[UserConfig]. 
I’m ID 47.

I then looked up my folder list from the SyncFolderUserList table.

SELECT * FROM [dbo].[SyncFolderUserList]
WHERE [UserConfigId] = 47

This yielded two records for me.  Note the data in the SyncFolderListId table.  For my scenario, I had two records, one with ID 60 and the other 147.

I then queried the SyncFolderList table for those rows.

SELECT *  FROM [dbo].[SyncFolderList]
WHERE [Id] IN (60, 147)

While I was expecting two entries, to my surprise there was only one entry.   Bingo, a rogue entry!

Seeing how it was only two folders, rather than deleting the rogue one and seeing if it worked, I opted to delete all of my data from both the SyncFolderList and the SyncFolderUserList tables. 

To my expectation, I was now able to select the folders again from either Web Desktop Manager or the Desktop Manager.

As a number of other users in our environment were experiencing the same issue, I decided to delete these tables all together.  All users reported they were now able to select folders to sync, and upon selecting them, their devices properly synchronized.

Drop a comment if this helped you!

Friday, October 09, 2009

What’s in your BBIM Barcode?

New with Blackberry Instant Messenger (BBIM) 5.0 is the ability to add contacts by scanning a barcode which BBIM can emit through the “my profile” feature.

It’s a cool feature, but I was obviously curious what was being sent.

The barcode is a QR Barcode, a two dimension barcode and ISO standard.  You can read all about them online.

Using the Barcode Reader for .NET library, I scanned mine in.  Here’s what I found

bbm:x07x8cce7b313673Steven Berkovitz

First, we’ve got a BBIM  identifier, “bbm”.

Followed by my PIN,”x07x8cce” (I’ve replaced a few characters).

The third set, “7b313673” I have not yet identified but it does differ between barcodes.  I’ll update this post when I find out.

At the end is my display name (not your name, I’ve verified this with some other barcodes), “Steven Berkovitz

Monday, July 13, 2009

C# – Converting IP’s to Numbers and Numbers to IP’s in 2 lines of code

I don’t know why everywhere I searched had such complex implementation of this, but converting from a dotted IP to a number (integer) and back is quite straight forward with the help of the IPAddress and BitConverter classes.

Also, keep in mind that IP addresses are 32 bit integers; I’m not sure why people feel it’s necessary to use a decimal or double for this when a uint will work just fine!

/// Converts the IP address to an integer.

/// The IP address in dotted form.
public static uint ConvertIpToInteger(string ipAddress) {
return BitConverter.ToUInt32(IPAddress.Parse(ipAddress).GetAddressBytes().Reverse().ToArray(), 0);

/// Converts the integer to an IP address.

/// The IP address in integer form.
public static string ConvertIntegerToIp(uint ipAddress) {
return new IPAddress(BitConverter.GetBytes(ipAddress).Reverse().ToArray()).ToString();

Thursday, April 23, 2009

Using SQL PIVOT with non-aggregate column

I was banging my head on my desk for a while over this one, hopefully this will save you the pain…

I wanted to use SQL 2005’s PIVOT function except the data I was trying to PIVOT was a text column, not an aggregate of a column.  However, the business rule for this table was a 1:1 rule so there’d never be anything to aggregate anyways (even when the data is numeric).

What got me at first was the “Incorrect syntax near the keyword 'FOR'.” error message which didn’t make a whole lot of sense until I realized that added a SUM(1) resolved the problem (hence, the requirement for an aggregate column).

So how, might you ask, do you work around this?  Well, you don’t – the PIVOT function only takes an aggregate value after all.  That being said, our friend MAX and MIN don’t require a numeric value to be passed to them – they are perfectly happy accepting a varchar or nvarchar value.

So instead of trying

PIVOT([MyTextColumn] FOR [MyHeader] IN ([List],[Of],[Columns]))


PIVOT(MAX([MyTextColumn]) FOR [MyHeader] IN ([List],[Of],[Columns]))

Tuesday, April 07, 2009

Roomba Surgery: Replace the Bumper Articulating Arm Optical Sensor Set

A few weeks ago my iRobot Roomba started spinning in circles and failing with a 9-beep error.  This error is related to the two bumper sensors located at 10 and 2 o’clock.  In some cases there might simply be debris behind the bumper interfering with the sensors.  I ruled this out with a disassembly and clean.

If your unit is under warranty you would likely be sent a replacement unit for this case, but that being said, mine was not.  Plus I was secretly looking forward to a full disassembly.

I followed a combination of instructions I found on the net to diagnose and replace the faulty part.

The problem is caused by one or two faulty “Bumper Articulating Arm Optical Sensors” in your robot.  These sensors consist of a plastic arm that swings between two IR sensors.  My readings on the working sensor showed a range of 0-5 volts.

The first sensor I tested functioned correctly switching between  0 and 5 volts.  The second sensor was fixed at 0v.  I had read about shorting out the IR to half blind your Roomba but I was not in the mood to solder fragile wires.  With that said, I managed to find a replacement part from Protech Robots. 

A week and a bit later (the item was shipped USPS ground) my part arrived and it was time to put Roomba back together again.

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 002
Remove the face plate

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 004
Remove the side brush, bottom plate, battery and the bumper (after removing the plate that holds the bumper on, the bumper gently lifts off.

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 006

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 007
This is one of the Articulating Arm Optical Sensors

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 009
Remove the top of the robot (don’t remove the screws for the handle)

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 011
Disassemble and remove the display

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 012
Gently remove the plastic sheet on top of the PCB

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 013
Disconnect all of the connections from the top of the PCB and remove the screws securing it.

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 016
Gently lift up the PCB towards you (the front of the robot) exposing the bottom connections.  Gently disconnect all of the connections including the bumper.

2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 020

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 021
Gently lift out the IR sensor array

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 024
Remove the 2 screws securing each articulating arm sensor and remove the sensors from the robot.

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 027

 2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 029
Here comes the trickiest of the parts.  You need to follow all of the wires from the sensors to the connector located in the bottom right of this photo, ignoring the white wire that connects the two sensors together.  Carefully remove any material securing the cables together.  I highly suggest you draw a visual of the connector and keep track of the individual wires as you remove them.  To remove the individual wires from the connector you need to gently lift up the plastic the secures the wire, and pull at the same time.  I used a small paper clip to lift the plastic.2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 034

Once the connector is reassembled the rest of the steps are just the re-assembly of the robot and testing.


2009-04-07 Roomba Articulating Arm Optical Sesnsor Set Replacement 046
It works!

Tuesday, February 10, 2009

How to query your BES database for a list of users

I was asked to generate a list of all our BES users, including their name, phone number, PIN, IMEI and device number.  Sure, I could have copied/pasted this data out of the BES management utility, but what fun is that?

It turns out that it’s really easy to query the BESMGMT database for this list.

[dbo].[SyncDeviceMgmtSummary] sdms
[dbo].[UserConfig] uc on sdms.[UserConfigId] = uc.[Id]