Wednesday, September 27, 2006


Unzip your files

I really don't like the unzip feature that comes with the Windows shell. Terribly slow if there are many files in the arrchive.
I also don't like 7zip, which I've seen used around. It frequently isn't able to read zip files.
I do like ICSharpCode.SharpZipLib, an open-source compression library.

Here's a little PowerShell script that will extract zip files for you:

Param($source, $destination=".")

$sourceFile = get-item $source
$destDir = get-item $destination

function using-library {
param($ns = $(throw "Enter Namespace to import"), [bool]$SuppressOutput = $false)

trap { throw "Could not import Namespace $($ns)" }

if ($SuppressOutput) {
} else {

using-library "ICSharpCode.SharpZipLib" $true

$unzip = new-object ICSharpCode.SharpZipLib.Zip.FastZip
$unzip.ExtractZip($sourceFile.FullName, $destDir.FullName, "Always", $null, "", "")

You'll have to add the assembly to the GAC. Open a Visual Studio SDK command window and type the command,

c:\>gacutil /i <path_to_ICSharpCode.SharpZipLib.dll>

Also, notice that in this script I am still instantiating objects using fully-qualified names. Can anyone tell me how to import namespaces into the global symbol table within a PowerShell script?


Adding Custom Logging Properties to your Log4Net configuration

In the Log4Net release notes there's a note about now to create your own custom pattern element in the logging configuration, so that, e.g., your conversion pattern might look like:

"%-5level %logger - %myConverter - %message%newline"

Maybe someone can comment on why this would be better than what I will recommend. Anyways, if you take this approach and want to include information about the actual logging event, you should know two things:
1) subclassing PatternLayoutConverter instead of PatternConverter will give you a Convert method that includes a LoggingEvent object.

2) The state object passed to PatternConverter.Convert is actually a LoggingEvent object!

I found it easier to create a new appender, which simply added objects to the LoggingEvent's properties collection.

Here's the appender class:

public class PropertiesExpanderAppender : ForwardingAppender
protected override void Append(LoggingEvent loggingEvent)
if (null != HttpContext.Current)
HttpContext context = HttpContext.Current;
loggingEvent.Properties["USER_IP"] = context.Request.UserHostAddress;
loggingEvent.Properties["REQUEST_URI"] = context.Request.Url.PathAndQuery;

if (null != UserProperties.CurrentUser)
UserProperties userProperties = UserProperties.CurrentUser;
loggingEvent.Properties["USER_EMAIL"] = userProperties.UserName;
loggingEvent.Properties["USER_NAME"] = userProperties.Name;
loggingEvent.Properties["USER_ID"] = userProperties.UserId;

// Delegate to base class which will forward

Here are the important snippets from the log4net configuration:

<appender name="PropertiesExpanderAppender" type="NameSpace.PropertiesExpanderAppender">
<appender-ref ref="RollingLogFileAppender" />
<appender name="RollingLogFileAppender" type="log4net.Appender.RollingFileAppender">
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date ... %P{USER_IP} %P{REQUEST_URI} %P{USER_EMAIL} %P{USER_NAME} ..." />
<level value="ALL" />
<appender-ref ref="PropertiesExpanderAppender" />

Note the %P{USER_IP} pattern elements, which correspond to the properties added by the forwarding appender. Now you can add as many logging elements as you want, without having to add a bunch of classes.


Getting your Log4Net AdoNetAppender running w/ Sql Server

After reading through Michael Flanakin's post, Log4Net is Crap4Crap, and after some further hardship getting the AdoNetAppender to work, I have my own list of recommendations if you're trying to get it to work.

1) Don't rely on log4net to give you feedback. If anyone can explain this, please do. I did set log4net.Util.LogLog.InternalDebugging = true, and still didn't get any help coming through my RollingLogFileAppender. If you can help with this, please do, for posterity.

2) Start a SQL Profiler and watch all login, failed login, and start command events.
Then you'll know whether the Appender is actually managing to execute the command and, if so, you'll get to have a look at what's wrong with it.

3) Don't let your numeric values be set to null. Log4net will die. And be ready for null strings to actually be sent as the string value, "(null)". This might only be an issue if you're using custom logging properties.

4) Start simple. I wound up succeeding by adding defaults to the parameters on my stored procedure, and then adding stored parameters one by one into the AdoNetAppender configuration.

Thursday, September 21, 2006



Trying to get a self-signed SSL certificate installed on Windows Server 2003, sp1? Good luck.

I spent a few hours trying to use selfcert.exe to install a certificate on an x64 machine, 2003 Server R2, sp1. Every time I made a request to a URI other than the root, the response I got from the server was HTTP ERROR: 400. Period.

Here's what worked for me:

1) Start->Programs->IIS Resources->SelfCert.exe.
type selfcert.exe to create a certificate on the default web server.

2) Open MMC and add the Certificates snap-in. Specify the Computer Account when asked which certificates to open.

3) Find the newly created certicate under Personal, and export it to a pfx file. Be sure to specify a password as windows won't let you import the certificate without entering something into the password field.

4) On the Windows Server, Start->Run->Inetmgr to open Internet Services Manager.
under Directory Security on the web site properties, import the file from pfx.

That worked!

By the way, don't use CN= if you want SQL Server to continue working. Use a different canonical name, such as the hostname of the site you're trying to protect. See: if you're using self-cert and suddenly you're getting SSL errors connecting to sql server.

Wednesday, September 20, 2006


Anatomy of a PowerShell script

if you do stuff, you should get PowerShell. Download it here.

I just spent a while learning some ins and outs of the scripting language, so let me try to spare you some trouble, and meanwhile talk about some of the sweet features of PowerShell scripts.

# The following starts the script and declares the parameters that it takes.
# From the command line you can query the script for its parameters,
# And this will also save you the trouble of coding in error messages when
# Args aren't provided. The familiar $argv is also there in case you want to
# handle any number of parameters.
Param([String]$server="localhost", [String]$database)

# Get-Item returns an object corresponding to a File System object.
# That in turn has all the properties that you'd hope for.
$basedir = get-item($basedirectory)

# Note that the argument to UseTheseUpgradeDirs is a call to dir, the familiar
# directory listing command. It's actually an alias for the scriptlet
# get-childitem, which returns a collection of child objects, this time
# of the "Upgrade Scripts" directory.
$upgradeDirs = UseTheseUpgradeDirs(dir "Upgrade Scripts")

# A simple loop. Note the syntax for calling static members on a class.
foreach ($dir in $upgradeDirs)
RunScriptsIn($dir, [DateTime]::MinValue)

# Here's a function declaration, with the parameter declaration below.
# push-location can also be referred to as pushd. Any scriptlet can be aliased
# Just see the user manual.
# Note the ease in filtering, where I set $files equal to the collection of
# File System objects named as such, with LastWriteTime greater than something.
function RunScriptsIn
Param([String]$dir, [DateTime]$modifiedAfter)
$files = dir *.sql, *.prc, *.viw, *.udf | '
where {$_.LastWriteTime -gt $modifiedAfter}

# write-host is like ECHO in batch language.
# $null is the null value. Note the strange binary operator ('=' is always
# an assignment operator here. Get used to -lt, -gt, -not, etc. Why? I don't
# know, but this script sure was easy to include in an html page.
if ($files -eq $null) {
write-host No new database changes in $dir
# Note the ease in shelling out to OSQL.EXE. Just like a true shell language.
foreach($file in $files)
write-host $
$results = osql -n -S $server -U $user -P $password -d $database -i $
write-host $results

# Then there's the .Net Framework support. See my earlier post for an example.
Next, if you want to run powershell scripts at all you have to relax the system
Security just a bit. Then you need to create certificates for yourself and start signing.

# This command will give you an article answering questions about signing.
# I'll give you the quick and easy though.
get-help about_signing

# The following command will tell your system to run scripts that have been
# Digitally signed so that you know who wrote them. If you feel pretty certain
# That I wrote and signed a script that you have, you can tell your system to
# Trust me as a code publisher, and subsequent scripts from me will be
# Recognized as safe.
Set-ExecutionPolicy AllSigned

# The following two commands will set up certificates for PowerShell and you.
# Don't forget to set your name under CN. You must run the makecert.exe
# That comes with .Net Framework 2.0 SDK.
makecert -n "CN=PowerShell Local Certificate Root" -a sha1 `
-eku -r -sv root.pvk root.cer `
-ss Root -sr localMachine
makecert -pe -n "CN=Jane Programmer" -ss MY -a sha1 `
-eku -iv root.pvk -ic root.cer

# Now sign your first script, a script that will sign future scripts!
# The following is the content of the script.
# In order to sign it, create the text file, then enter PowerShell interactive
# Shell, set the $file variable yourself, and then run lines 2 and 3 in the
# shell.
param([string] $file=$(throw "Please specify a filename."))
$cert = @(Get-ChildItem cert:\CurrentUser\My -codesigning)[0]
Set-AuthenticodeSignature $file $cert

There's a very nice tutorial of PowerShell Right here.

Monday, September 18, 2006


A real command shell from Microsoft

I wish I'd have known about PowerShell a while ago. I had long given up on this and turned to cygwin, dreamed about carving out enough spare time to augment IronPython to have a handy windows scripting api.

Anyhow, it's a little awkward but I think I like it. Better to show than tell, here's a script that does a little communication with SQL Server via osql.exe and the SqlClient api:

function main
$basedir = "D:\prj"


$upgradeDirs = UseTheseUpgradeDirs(dir "Upgrade Scripts")

push-location("Upgrade Scripts")
foreach ($dir in $upgradeDirs)

RunScriptsIn('Stored Procedures')


function RunScriptsIn($dir)
$files = dir *.sql, *.prc, *.viw, *.udf
foreach($file in $files)
write-host $
$results = osql -n -S localhost -E -i "$file"
write-host $results

function UseTheseUpgradeDirs( $dirs )
$useThese = new-object System.Collections.ArrayList

$conn = new-object
$server, $database, $user, $password))


foreach($dir in $dirs)
$sql =
"select count(1) from version where build >= '" + $ + "'"
$cmd = new-object System.Data.SqlClient.SqlCommand($sql, $conn)
$reader = $cmd.ExecuteReader()
$foobar = $reader.Read()
if ($reader[0]>=1)



Friday, September 08, 2006


Amy Goodman speaking in Northern California

Amy Goodman, who has been called a National Treasure by Michael Moore, is touring the US with her new book. Coming through northern california, she'll speak in Oakland, Santa Cruz and Sebastopol.

Amy is most noteworthy for her presence as the host of DemocracyNow!, a newswire that has been broadcasting independent news since 1996.

for more info see

WHERE:Veterans Hall, 282 S. High St., Sebastopol, CA
WHEN: Sun 9/17/06 @ 10 AM
DESCRIPTION: A benefit for KRCB with Amy Goodman and David Goodman, co-sponsored by Copperfield's Books.
TICKETS:$10 at the door, $5 for KRCB members

Thursday, September 07, 2006



Microsoft have released a .Net implementation of the Python programming language, complete with an interactive shell. Good move, Microsoft.
Get it here.


Mozart On Wheels

You'll want to check this out (it's only 43 seconds long): The most rockin' rollerblader I ever did see.

Wednesday, September 06, 2006


Ruthless Database Administration

I like to keep a special database on my server, called 'admin', wherein I store nasty little procedures like this one. So, you thought you were going to restore the database from backup, but after waiting about 30 seconds the server complains that there are users in the database.

If you're trying to restore the database from backup, who cares if you ruin a query that was in progress? Get them out of there! The following sproc will kill all processes running in the specified database, except for the current process. No questions asked.

USE [admin]

* sproc: kill_processes
* author:
* This code is provided as is, and the author is not responsible for data damaged or lost
* through its use.
ALTER PROCEDURE dbo.kill_processes (@database_name SYSNAME)
DECLARE test_cursor CURSOR
select spid from master..sysprocesses p inner join master..sysdatabases d
on p.dbid = d.dbid
where name=@database_name

DECLARE @spid int, @cmd varchar(50)
OPEN test_cursor

FETCH NEXT FROM test_cursor INTO @spid
WHILE (@@fetch_status <> -1)
IF (@@fetch_status <> -2)
IF @spid <> @@spid
PRINT 'Killing process ' + CONVERT(VARCHAR, @spid) + '.'
EXEC (@cmd)
FETCH NEXT FROM test_cursor INTO @spid

CLOSE test_cursor
DEALLOCATE test_cursor

Tuesday, September 05, 2006


Regex Workbench, worth getting

Get Eric Gunnerson's Regular Expressions Workbench Here.

This is an excellent piece of software for developers. So handy for use in writing regular expressions, that for me it sometimes predicates a decision to go ahead and write a robust expression, rather than a quick and easy set of string comparisons. The latter work but require more code and frequently lead to a reduced set of expressions that the software will handle.

Thanks, Eric.

Monday, September 04, 2006


IE7 Doesn't Suck

Download Internet Explorer 7. It doesn't suck. I don't know whether Microsoft have fixed the security problems that lead to the popularization of A truly excellent browser, but the new features include tabbed browsing and IMO the most well-designed Full Screen feature to date.


Trying out SQL Server 2005

SQL Server 2005 Express Edition comes with almost everything you need to do development work. The lack of a Profiler tool is going to have me purchasing the developer edition very soon, but download the free management tools from Microsoft's Download Site, and find everything else you'd hoped would carry over from SQL Server 2000 Enterprise Manager.

Also, check out Red Gate's SQLPrompt. It plugs into your Query Analyzer and offers object name and keyword completion a via familiar CTRL-Space-induced drop-down list. It's clumsy at first but helpful if you have too many objects in your database to remember all the names.

This page is powered by Blogger. Isn't yours?