Adding a Windows Explorer Context Menu for the Web Development Server

I wanted to be able to launch a single folder – the results of an ASP.NET WebForms build – in a Web Development Server without having to run up Visual Studio. The result looks like this :

And the solution… add the following to the registry :

Windows Registry Editor Version 5.00

[HKEY_CLASSES_ROOT\Directory\shell\webdev]
@="Launch in WebDev Server"

[HKEY_CLASSES_ROOT\Directory\shell\webdev\command]
@="\"c:\\windows\\microsoft.net\\framework\\v2.0.50727\\WebDev.WebServer.exe\" /port:54321 /path:\"%V\""

I would like to change that so it uses %windir% instead of C:\Windows but I must have got the syntax wrong because Explorer wouldn’t recognise the command. I’d also like to add an option for .aspx files that launches the parent folder in WebDev then launches an explorer window for the selected aspx file.

One last thing… this isn’t launching the server with any elevated permissions. If that’s required then you’ll need a tool such as Wintellects Elevate command which can be downloaded with full C# source code.

Generating a list of numbers in T-SQL

I wanted to create a list of numbers so I could cross join them to another set of data to generate some test data quickly. There are ways to generate numbers using a CTE (Common Table Expression) or if you’re in a real hurry you can make us of a fairly undocumented table called master..spt_values. Seeing as this table isn’t well documented don’t go using it for production code since it might change but for quick one-off tasks it seems fine.

The following SQL creates a list of numbers from 0 to 2047 :

select [number]
from master..spt_values
where type = 'P'

ASP.NET Page Life Cycle Diagram

I saw this months ago and wondered where it had gone so when I came across it by chance whilst googling I thought I would save myself a link to it. Many thanks to Raymond Lewallen who credits Leon Andrianarivony!

Sproc Compile Locks

I came across an article on Microsofts support site which has raised a point which I’d never considered before. It basically says that if the user that executes a stored procedure is not the owner of the procedure then locks are acquired during the process to find a cached plan that may lead to blocking. It also states this could be avoided by fully qualifying the sproc name (e.g. dbo.mysproc) when calling it to avoid the extra lookups. This has been a problem for me in the past since the projects I’ve worked on had been setup with an account running as dbowner, however, if I was setting up a database from scratch I’d set up a specific user(s) for accessing the database and give it just the permissions it requires (sproc execute permissions for example.) I think, therefore, this would fall into these problem. However, I would also make use of the schema prefix (e.g. Sales.Order table) rather than leaving it as dbo and therefore avoid this problem altogether.

Conditional Aggregate Totals in SQL

I’ve been working on a SQL Server Reporting Services report which had to display several counts on each row. Each row represented a project and each project could have several tasks that were either ordinary tasks or milestones and could also be escalated. The report need to show something like this :

Project Name	Milestone Counts					Task Count					Escalated Task Count
	1st Week	2nd Week	3rd Week	>=4th Week	Total	1st Week	2nd Week	3rd Week	>=4th Week	Total

As you can see a task initially has to break down into two major colours of milestone and ordinary task counts but then is broken down into counts in the 1st, 2nd, 3rd and greater than 4th week totals including the total itself.

Initially this seemed tricky when thinking in terms of using count() but there’s an incredibly simple technique that solves this but which isn’t immediately obvious. The Sum() aggregate function can contain an expression and in this example that expression could compare the task date against late week dates and return either a 1 or 0 :

select ProjectName,
   sum(case when IsMilestone = 1
      and TaskDate < @LateDate
      and Taskdate >@OneWeekLateDate
      then 1 else 0 end) as Week1LateMilestoneCount
from Project
group by ProjectName

I’ve only included one example there but I think it’s enough to get the idea.

Selecting a result set from a stored procedure in SQL Server

Selecting data from a stored procedure in SQL Server is already a documented feature and here’s an example:

insert into #systables exec sp_executesql N'select * from Northwind.sys.tables'

…problem is this example doesn’t run without first creating the temp table and therefore knowing all the column definitions. When I’m running quick queries this isn’t exactly convenient. I’ve seen blogs posts using a linked server but there’s another way :

select * into #systables
from openrowset(
   'sqlncli',
   'server=.;trusted_connection=Yes',
   'sp_executesql N''select * from Northwind.sys.tables'''
)

I wouldn’t necessarily use this as a day-to-day process on a production environment but for administration or scripting installations I think it fits the bill.

Quick Application Launching in Windows (Vista and XP)

Here’s a quick tip for launching applications with a simple keypress rather than having to hunt it down in the program files menus (although I really like the search facility in the Vista start menu!)

First up, I created a Keyboard Shortcuts folder in my program files folder and added shortcuts for each of the applications I want to launch from a keypress. I’ve also included the shortcut key I’ve assigned in the name for simple reference. It’s important to be able to track down which keys are assigned to which apps because you can re-assign the key without realising you’re overwriting a previous one. Here’s my shortcuts in the start menu…

Then it’s just a simple matter of assigning the keypress in the properties for each shortcut…

The compatibility tab also lets you run programs automatically as admin if that’s required.

The only issues I’ve noticed when I’ve done this is I’ve re-mapped a keypress that already exists in another application. This usually isn’t a problem if I choose a shortcut of CTRL + SHIFT + ALT (which is only two key presses on a keyboard – ALT GR and SHIFT) since that’s a pretty unusual shortcut for an application. But it just so happened at one point that Visual Studio on my machine had assigned a keypress of CTRL + SHIFT + ALT + E which meant that the one I had defined took priority!