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.

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.

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.

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.

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!