Saturday, February 27, 2010

Sort a list of addresses in a walking order

Suppose you need to walk in a street using a list showing the odd numbers ascending first and then the even ones descending, a kind of a postman's walk in a four lanes street.

One way to solve the problem is to reorder the odds or the evens by making them negatives. This requires an extra effort because Excel doesn't like that when sorting lists. A better approach is to subtruct the street numbers from a large enough constant. Let's say that our constant would be the sum of all the street numbers. Applying the formula and concatenating it with the street name will solve the problem.

Here is the original list in columns A:Bwith the formula applied in the extra column C:


Here is the new order sorted by column C:

Monday, February 15, 2010

Speed up SharePoint spin-up and stsadm execution time in sites without access to the Internet

For my own records, I posted excerptions of an article I found. You can find the full article and references here. Jeroen Ritmeijer originally posted it here.


The problem is that when loading signed assemblies the .net Framework checks the Internet based certificate revocation list. As our servers have, like most secure environments, no outgoing connections to the public Internet the connection to times out after what appears to be 30 seconds. It probably does this a couple of times in succession, causing a 2 minute wait when spinning up SharePoint.

After the timeout the assembly is still loaded and the software works as expected, though very slow every time a new signed assembly is loaded for the first time, which happens a lot.

Possible Solutions: (You can try one or more, as appropriate in your environment)

  1. Add to your hosts file and point it to your local machine. Some people have reported success with this, but it didn't work for me.
  2. Allow your servers to directly connect to If your environment dictates the use of a proxy server, configure it using proxycfg.
  3. Disable the CRL check by modifying the registry for all user accounts that use STSADM and all service accounts used by SharePoint. Find yourself a group policy wizard to help you out or manually modify the registry:
    [HKEY_USERS\\Software\Microsoft\Windows\CurrentVersion\WinTrust\Trust Providers\Software Publishing]
  4. Download the CRLs and add them to the server manually (I haven't tested this, but it may work):
  5. Add them:
    certutil -addstore CA CodeSignPCA.crl
    certutil -addstore CA CodeSignPCA2.crl
VBScript to apply registry change: (Contributed by Nik Shaw)

The following script applies the registry change to all users on a server. This will solve the spin-up time for the service accounts, interactive users and new users.

const HKEY_USERS = &H80000003
strComputer = "."
Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\default:StdRegProv")
strKeyPath = ""
objReg.EnumKey HKEY_USERS, strKeyPath, arrSubKeys
strKeyPath = "\Software\Microsoft\Windows\CurrentVersion\WinTrust\Trust Providers\Software Publishing"

For Each subkey In arrSubKeys
    objReg.SetDWORDValue HKEY_USERS, subkey & strKeyPath, "State", 146944

Tuesday, February 09, 2010

Drag and Drop Outlook Email to SharePoint Library

If you need to manually store emails in a MOSS or WSS library, you can drag and Drop items from your MS Outlook. Just open the document library with Windows Explorer from the Actions tab. Once opened, you can drag and drop into it Windows items (files) as well as MS Outlook emails. The emails are stored as MSG type files.

If you try dragging Outlook Express items (EML type) you will get an error message.

An alternate way is to define the library to support imcoming mails (there are many articles on teh subject). This enable storing EML files but in order to open them you first need to save the file in your hard drive.

Saturday, February 06, 2010

Count Even or Odd numbers in Arrays

There are several ways to count even or odd numbers in Excel. The simplects one is probably if you are familiar with Array Formulas.

Let's say you have an array A1:A100 with integers.
Enter the folloring formulas into the result cells, say C1 and D1 :
(the curely braces are automatically entered by Excel afret you type into the formula bar the formula and press Shift+Ctrl+Enter. Do not enter them manually.)

Odds {=SUM((ODD(A1:A100)=A1:A100)*1)}

Evens {=SUM((EVEN(A1:A100)=A1:A100)*1)}

The Odd and Even function is compared to the original value and return True or False.
Then it is multiplied by 1 to transtale the True / False into 1 / 0 respectively.
The SUM function then sums the ones to get the expected result.

If you don't use array formulas just create the formulas in column B1:B100 and count the results using the COUNT function.

Wednesday, February 03, 2010

Hiding all SharePoint List items from the Seach Results page.

Sometimes it makes no sense to show a list's content in WSS or MOSS search results. To hide the list or library you access the Setting / Library or List settings / Advanced Settings page and change the Search attribute to No.

However, be aware that new items won't appear in the search but existing items will continue showing in the search results. To completely clean the list or library you need to run a full crawl. This is done in the central administration area.