Thursday 12 December 2013

Data Loader Java Heap Space Error

Exporting or inserting attachments using Apex Data Loader via the command line will often fail with an 'out of memory' error due to a lack of java heap space. This can fixed by expanding the heap size in the process.bat file in the bin folder using the -Xms and -Xmx switches:

..\_jvm\bin\java.exe -cp ..\DataLoader.jar -Xms32m -Xmx128m -Dsalesforce.config.dir=%1 com.salesforce.dataloader.process.ProcessRunner %PROCESS_OPTION%

When not specified, the heap size defaults to 1MB but will increase to 16MB as needed. The above code increases the allotted memory to 32MB and allows for expansion to 128MB if needed.

See this article by Brett Spell for more on setting heap sizes. Thanks also to Anna478 and Demand Chain Sysytems.

Tuesday 12 November 2013

How to query a stored procedure in SQL

If you have ever needed to query an existing stored procedure in an ad-hoc SQL query then probably the most common method is to create a temp table (or table variable), execute the sp, and insert the result set into the temp table (see stack overflow article here). But this involves manually specifying the columns, and if your result set has more than a few columns it can be tedious and off-putting.

Fortunately there is another solution that allows a direct query of a stored procedure's result set without having to build temp tables (thanks to Erland Sommarskog for the info).

OPENQUERY allows you to query a stored procedure in the same way you query a base table, similar to querying a query in MS Access, and even allows joins between different sps.

For example, if I have two stored procs on my local server, PaymentUpsert and MilestoneUpsert, I can run the following select statement using OPENQUERY:

SELECT p.Status__c
,m.Status__c
FROM OPENQUERY(LT010, 'EXEC StagingArea.dbo.PaymentUpsert') AS p
JOIN OPENQUERY(LT010, 'EXEC StagingArea.dbo.MilestoneUpsert') AS m
ON p.[Milestone__r:External_Id__c] = m.External_Id__c
WHERE p.Status__c = 'Completed';


If you get an error telling you that your server instance is not configured for data access then you can run the following query to allow access:

EXEC sp_serveroption 'LocalServerName', 'DATA ACCESS', TRUE;

Be aware that setting your local server data access to true may have security implications for you and your network. See data acess article on technet and also article on openquery.

Tuesday 8 October 2013

Change Facebook profile picture without notifying everyone

If you want to change your profile picture on Facebook without announcing it to the world then you need to set the privacy on the chosen picture to "Only Me".


Selecting a private photo as your profile picture will not be announced as an activity and you can change as often as you like without annoying your friends with constant updates.

Note this does not work for cover photos which are always public by default.

Tuesday 12 February 2013

Case sensitive VLOOKUP search in MS Excel

VLOOKUP is not case sensitive and when searching for 15 character Salesforce Ids, you must force Excel to match on exact case. The simplest way to return an exact case-sensitive match is by using an IF(EXACT... formula, but this will return #N/A if the first ID found is not an exact match.

The best way to enforce a case sensitive search in Excel is to use the INDEX / MATCH combo as follows.

Wednesday 16 January 2013

Solar azimuth - how the sun moves across the sky

The solar azimuth angle is the angular measurement of the sun in a clockwise direction from north. When building a house it is important to think about the solar azimuth because it affects which sides of the house receive morning or afternoon sunlight at various times of the year. Most homes are built with north-facing windows to optimise the low winter sun elevation, but few homes are built with the summer azimuth angles in mind.