From the outside in

Feature Pack for Microsoft SQL Server 2005, February 2007

Iain — Wed, 09 Jul 2008 08:13:00 GMT

I've recently written an ASP.NET web application to programmatically execute SQL Server 2000 DTS packages and jobs and report on the outcome. I should really have the link below bookmarked given the amount of times I've used it.

Feature Pack for Microsoft SQL Server 2005, February 2007

Newcastle SharePoint User Group

Iain — Thu, 03 Jul 2008 13:33:00 GMT

I'm heading over to Newcastle for the SharePoint User Group tonight.

It's a whiteboard session on MOSS Architecture.

Newcastle SharePoint User Group

Hope to see you there if you are going.

UPDATE....

Big thank you to John Timney (Cap Gemini), Brian English (Cap Gemini) and Phil Hunter (BT) for an excellent user group meeting. There was a mix of people from different industries, lots of round table discussion and good information shared.

I'm currently implementing an intranet/extranet MOSS architecture for 4500 users and found the whiteboard session invaluable.

Once again, many thanks.

SUBST, GAC and SharePoint

Iain — Tue, 06 May 2008 12:58:00 GMT

Working with SharePoint, sometimes you just have to install assemblies to the GAC. An example of this I have had to deal with recently was writing a webpart to access methods in the UserProfileManager class.

Debugging GAC installed assemblies can be a bit frustrating, hopefully the following steps will make it alot easier and obvious....

1. Install the assembly into the GAC

2. Map a drive using SUBST to %SYSTEMROOT%\assembly

SUBST maps a drive to a path - http://www.microsoft.com/technet/archive/msdos/07_refer.mspx?mfr=true

A mapped drive does not use the Assembly Cache Viewer and will show you the true folder structure.

3. Locate and open the GAC_MSIL folder in the mapped drive

4. Locate and open the folder containing your assembly. The name of the folder is the same as the assembly name.

5. Copy the pdb (program database) file from your project build folder to your assembly folder in the GAC. Because you have mapped a drive to %SYSTEMROOT%\assembly you should be able to do this easily with Windows Explorer.

6. Do an iisreset /noforce (this is not really required but usually helps - general rule of thumb for working with SharePoint).

7. Go to Visual Studio and attach the debugger to the w3wp.exe (remember there may be more than one depending on how many app pools you have).

You should now be able to hit the breakpoints you have set.

VBUG Newcastle: VS2008, SQL Server 2008 and Win 2008 Launch Event

Iain — Wed, 30 Apr 2008 11:54:00 GMT

I'm presenting tonight on SQL Server 2008, along with Rik who is presenting on Windows Server 2008 and Richard who is presenting on Visual Studio 2008.

VBUG Newcastle: VS2008, SQL Server 2008 and Win 2008 Launch Event

Hope to see you there.

Developer Day Scotland

Iain — Mon, 17 Mar 2008 14:09:00 GMT

I'm going, are you?

This is a community event, by the community for the community.

Get yourself there, registration is now open.

Silverlight 2.0

Iain — Thu, 28 Feb 2008 15:03:00 GMT

As Ferris Bueller said "Life moves pretty fast".

Silverlight 1.0 was released last September, the first public beta of Silverlight 2.0 ain't that far away...check out Scott Guthrie's blog post here.

Calculation Settings in Excel

Iain — Wed, 27 Feb 2008 17:02:00 GMT

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 -

"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

Iain — Tue, 26 Feb 2008 10:01:00 GMT

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

Iain — Wed, 30 Jan 2008 11:53:00 GMT

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 ;-)

Microsoft Boy announces his school homework

Iain — Mon, 28 Jan 2008 15:58:00 GMT

Very funny.

Granting permissions on the GAC

Iain — Mon, 28 Jan 2008 14:26:00 GMT

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

Iain — Fri, 25 Jan 2008 10:09:00 GMT

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

Iain — Thu, 20 Dec 2007 13:42:00 GMT

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 -

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

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

Gary Short Presentation

Iain — Tue, 18 Dec 2007 14:38:00 GMT

Gary Short is speaking at Glasgow Caledonian University, 7pm, 16th January 2008. I saw him speak at DDD 6 and he was excellent. Well worth a trip to see.

Link to presentation details

SQL Server Status

Iain — Fri, 07 Dec 2007 15:30:00 GMT

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)	state
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 http://msdn2.microsoft.com/en-us/library/ms187997.aspx.