Reference external java beans with absolute path

I run a bunch of batch file exports from Salesforce calling Data Loader from the command line, with multiple process-conf.xml files defining various tasks. Originally I stored the SFDC credentials in each bean, then shifted the credentials to variables, still within the same xml file, using java.lang.String classes with constructor-arg properties:

Case sensitive text searches in SQL Server

Salesforce unique ids are only case insensitive when using the 18 character version with the three additional characters at the end which specify the case of all the previous characters. So when searching for and matching SFDC Ids in SQL Server using the 15 character ids, the result set will often include two or three matches where you were expecting only one.

Calculate total length of all cells in a row or column

In Excel the LEN function returns the length of any given cell, but it errors if asked to find the length of an array of cells. E.g. =LEN(A2:Q2) will not return the length of all cells in the given range. In order to find the length of an array of cells you need to enter the formula as an array formula.

Type the same formula into a cell  (=LEN(A2:Q2)) but before hitting [ENTER] hold down the [CTRL] and [SHIFT] keys simultaneously. Excel will bracket the formula in curly braces automatically, you do not need to type the braces in. The formula will look like this:

{=LEN(A2:Q2))} and the cell will display the total number of characters in the selected array of cells.

Big thanks to Guy Parrington for the heads up!

Notebook security - password protecting your hard drive

As a mobile worker for a government agency, I am constantly aware of the vulnerability of my notebook. The portability that I value so highly also makes it an easy target for thieves in places such as airports, cafes, and even serviced apartments and shared offices. Not only is the actual device more vulnerable than a desktop PC, the data stored on the hard drive needs to be secured as well.

Convert dates to Salesforce format in T-SQL

When preparing csv files for import through the data loader, it is best practice to format the datetime values in the full yyyy-mm-ddThh.mm.ss.sssZ format required by Salesforce.com. This prevents any complications with Data Loader or other migration tools which may have different settings depending on the environment or installed software (for example, dates can be scrambled if your data loader has 'Use European Date Format' checked, but your client's data loader does not.

CONVERT float to nvarchar

Attempting to compare company numbers (e.g. ABN) stored as datatype float to text strings is complicated by the fact that the default display for float numbers over 6 digits is scientific notation. To work around this you need firstly to convert the float to a bigint, then convert to nvarchar.

Spring bean variables with data loader command line

If you use Apex Data Loader from the command line, you will know that changing usernames, passwords, and environments is a bit painful if you have more than one bean in your process-conf.xml file. Adding variables should be easy but getting the syntax right without an IDE is not. I couldn't find much help on the web so a Java guru colleague came through with the following fix.

Using Google Analytics in webs.com

The latest version of the Analytics tracking code offers an improved way to track website visitors with Google Analytics. It takes advantage of browser support for asynchronous JavaScript to enhance the speed in which the tracking code is loaded.

Differences between views and temp tables

In a relational database like SQL Server, views provide a way of giving users a way to work with specific portions of a larger schema. Temporary tables are another means of providing end users with a subset or collation of data from base tables, and at first glance it seems that these are similar methods of achieving the same end. So it is important to understand what the difference is between a view and a temp table.

T-SQL not equal to NULL

This is a well known problem addressed elsewhere, but I want to log it here anyway. When joining two tables and checking for records where a value in one does not equal a value in the other, T-SQL fails to find records where the reference table has recorded a NULL value. Presumably this is because a NULL value is unknown, so there is no way of knowing whether the two values are equal or not.

CONVERT CAST dates in T-SQL

Sometimes you need to compare dates as text rather than in date format, and this can be especially useful when comparing dates from different systems using different data types. But T-SQL makes it easy to format different dates using the CONVERT function.