From the outside in

The blog of Iain Angus

Calculation Settings in Excel

Sometimes Excel doesn't behave as you expect and you learn something. Excel is complex as Joel Spolsky pointed out last week.

Quite often clients will send data updates in Excel spreadsheets as this is the format they are used to working with; even if you as a programmer are not. One such workbook I have worked with this week had a worksheet with formulas to increment id's. The problem was the id's were not incrementing, even though the formula was correct.

After some investigation it turned out that the Calculation was set to manual (Tools -> Options -> Calculation Tab in Excel 2003), as shown below -

 Excel Calculation SettingsExcel Calculation Settings

"Automatic calculation" mode means that Excel will automatically recalculate all open workbooks at each and every change, and whenever you open a workbook - which is what I was expecting and wanted (if you are in "manual calculate" mode pressing F9 will update the formulas).

This "manual calculate" behaviour is by design and Excel tries to help you realise this by updating the status bar with the word "Calculate". In fact, as this behaviour is set by the first workbook opened, it may seem to randomly change depending on the sequence the workbooks are opened, and their settings.

As Joel Spolsky said in his article...

"Every checkbox, every formatting option, and every feature in Microsoft Office has to be represented in file formats somewhere."

Diagnosing referential integrity using TSQL

Quite often you work on databases that you didn't write. Getting up to speed on the structure of the tables and the relationships between them is not something you relish. The GUIs provided by SQL Server 2000 or 2005 don't always provide the best way, particularly with regard to complex referential integrity.

I've always used a few TSQL procedures to accelerate the process, and hopefully reduce the pain...

sp_help, returns information about a database object (any object listed in the sysobjects table), a user-defined data type, or a data type supplied by Microsoft SQL Server. There are quite a few "sp_helps", they are worth checking.

sp_fkeys, returns all tables that include a foreign key to the given table

sp_pkeys, returns primary key information for a single table.

Accessing User Profile Information in WSS 3.0

MOSS and WSS 3.0 handle user profiles differently. In MOSS, user profile information is stored centrally and can be shared across site collections. Profiles can be manipulated using the UserProfileManager class found in Microsoft.Office.Server.dll.

Life just isn't the same when working with WSS 3.0. When developing a workflow, for example, it may be useful to access a property of the user profile. You can't use UserProfileManager as it doesn't exist and SPUser doesn't expose most of the profile information. In WSS 3.0 the user profile information is stored in a hidden list specific to an individual site collection. If you have multiple site collections, a different hidden list is used for each site collection. The User Profile Information is just another list and can be treated as such -

SPList userProfileList = web.Lists["User Information List"];

foreach (SPListItem user in userProfileList.Items)
    Console.WriteLine(user["Name"] + " " + user["Department"];

The cut down C# above lists all the user names and departments.

Accessing the user profile information in WSS 3.0 is straightforward, once you know how ;-)

Granting permissions on the GAC

Normally you don't encounter permission errors when installing to the global assembly cache (GAC). Today I did, even though I was logged on as a member of the administrators group (not that being an administrator is a guarantee of much).

If you navigate using Windows Explorer to %windir%\assembly, what you see is the view displayed by the assembly cache viewer. If you go up a folder and right click the assembly folder and click properties you won't see the security tab you normally see. The assembly cache viewer (Shfusion.dll) is a Windows shell extension that affects how Windows Explorer lets you view the folder.

You have two options at this point -

1. Alter the behaviour of Windows Explorer with regard to the GAC. Hannes Pavelka gives a nice post on how to do this. 

2. Open a command window and use Cacls. Cacls allows you to both view and alter the permissions on any folder, and subfolders, for any account, including the GAC, as long as you are running it as an account that has permission to do so. Sounds like a russian doll of permissions, doesn't it?

I chose option 2 as I don't like altering the expected behaviour. This can cause confusion further down the line.

In order to get round the installation issue I ran the following at a command window -

CACLS %WINDIR%\assembly /e /t /p administrators:W

this gave members of the administrators builtin group write permissions on the GAC, and the subfolders, and sorted out the installation issues.

SharePoint User Group - Newcastle

Big thank you to Penny Coventry and Steve Smith for presenting at the SharePoint User Group UK Newcastle meeting on Wednesday night. Both were excellent sessions.

Penny gave an overview of SharePoint Designer, which if not already, will probably become your default tool for design and basic development of SharePoint sites.

Steve covered Forefront Security for SharePoint. There are not too many choices when it comes to anti-virus solutions for SharePoint. Some vendors have had a less than successful record in this area and Forefront looks like a very interesting solution. Forefront offers real time and manual, multi engine scanning, as well as keyword content filtering.

SharePoint 2007 "Friendly" Error Messages

SharePoint 2007 displays a nice "friendly" error message when anything goes wrong. This "friendly" error message usually provides no useful information about why the error has occurred (sometimes you get an HRESULT). It may be friendly but isn't usually very helpful, not for a developer.

You can alter this default behaviour by making two changes to the web.config for the site you are debugging.

CallStack is set to false. Change the CallStack setting to true, as shown below -

<SafeMode MaxControls="200" CallStack="true" DirectFileDependencies="10" TotalFileDependencies="50" AllowPageLevelTrace="false">

customErrors mode is set to On. Change the mode to Off, as shown below -

<customErrors mode="Off" />

Hey presto, helpful error messages. Now if I could only fix the error... ;-)

SQL Server Status

In SQL Server 2000 you could query the sysdatabases table to find out the "status" of a database. This field was actually a bit mask and the sysdatabases table is only available in SQL Server 2005 for backwards compatability. The sysdatabases table "maps" to the sys.databases system view. The old sysdatabases "status" column now spans multiple columns in the new sys.databases system view...

sysdatabases sys.databases
1 = autoclose (ALTER DATABASE) is_auto_close_on
4 = select into/bulkcopy (ALTER DATABASE using SET RECOVERY) log_reuse_wait
8 = trunc. log on chkpt (ALTER DATABASE using SET RECOVERY) log_reuse_wait
16 = torn page detection (ALTER DATABASE) page_verify_option
32 = loading state
64 = pre recovery state
128 = recovering state
256 = not recovered state
512 = offline (ALTER DATABASE)


1024 = read only (ALTER DATABASE) is_read_only
2048 = dbo use only (ALTER DATABASE using SET RESTRICTED_USER) user_access
4096 = single user (ALTER DATABASE) user_access
32768 = emergency mode state
4194304 = autoshrink (ALTER DATABASE) is_auto_shrink_on
1073741824 = cleanly shutdown is_cleanly_shutdown

...if I've got any wrong please let me know. 

Link to mapping SQL Server 2000 system tables to SQL Server 2005 system views