SELECT [softwareThoughts] from Tyler

SQL Formatting

2007-07-03T16:48:00.000-07:00

You can buy the RedGate Sql Prompt bundle and it will format your SQL code for you. You can also do it for free at:

http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

SQL Developers: Download SQLPrompt

2006-10-25T15:24:00.000-07:00

RedGate, the providers of several completely excellent SQL Server-related software products, have a free download called SQLPrompt.
Very well-done, intellisense for your SQL Server connection. Works in Query Analyzer, VS.Net, SSMS.

An example of its robustness: having typed "FROM myTable INNER JOIN ", I'll automatically get a list of tables with foreign keys referencing myTable, or with primary keys that myTable references. Having selected one, then continuing to the ON phrase of my join, SQLPrompt will offer the right expression to complete the join.

If you go into the advanced options you'll find such goodies as "automatically change keywords to uppercase as I type."

It gets annoying some times when the software becomes a little confused, but not too often, and then I'll just reach down to the system tray and turn it off.

/T

Idempotence in idempotence in dropping tables, columns

2006-10-20T13:45:00.000-07:00

When working on a database with a team of people all making changes, it's a great idea to make all change scripts idempotent, which in this case means "always having exactly the same output as the first time it was run." See Phil's article for a great demonstration of the principle and practice.

I have found that using the object_id() function and the system tables allows me to write a little less code than the information_schema view require - I'm too lazy to type information_schema.referential_constraints too many times. All tables, indexes, check constraints, default values, and foreign key constraints can be tested for existence using object_id('objectName'), which returns null if the object isn't there in the database.
The syscolumns table can be used to test for column existence.

Note that in SQL Server 2005, the tables have been renamed to sys.objects and sys.columns. I haven't tested this with those tables, but with my SQL Server 2005 still allows me to use the table names from SQL Server 2000.

Be careful with check constraints and foriegn key constraints that are autogenerated by the database system: the names given by one instance of SQL Server will be different from that given by another, so that if you're checking for existence of a constraint named 'FK__tblMyTab_O_9t5325', your script might only be idempotent on one machine. In fact, it may throw errors on any other instance of the database.

Two ways to deal with this:
1) When creating database objects, always name your constraints. This has a slightly higher overhead but can make it much easier to change the thing using DDL later on. Here's an example of how to achieve that:


IF object_id('dbo.Apples') is null
 CREATE TABLE [dbo].[Apples](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Description] [nvarchar](50) ,
 CONSTRAINT [PK_Apples_Id] PRIMARY KEY CLUSTERED ([Id] ASC) ,
 CONSTRAINT [UKey_Apples] UNIQUE NONCLUSTERED ([Description] ASC)
 )
GO

IF object_id('dbo.Oranges') is null
BEGIN
 CREATE TABLE [dbo].[Oranges](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Description] [nvarchar](50) ,
 CONSTRAINT [PK_Oranges_Id] PRIMARY KEY CLUSTERED ([Id] ASC) ,
 CONSTRAINT [UKey_Oranges] UNIQUE NONCLUSTERED ([Description] ASC)
 )
END
GO

IF NOT EXISTS (SELECT 1 from syscolumns
  where id = object_id('Apples')
  and name='OrangeId')
BEGIN
 ALTER TABLE Apples
  Add OrangeId int not null
  constraint DF_Apples_OrangeId default 1 WITH VALUES
END

IF object_id('FK_Apples_Oranges') IS NULL
BEGIN
 ALTER TABLE Apples
 ADD CONSTRAINT FK_Apples_Oranges
 FOREIGN KEY (OrangeId)
 REFERENCES Oranges (Id)
END

IF NOT EXISTS (SELECT 1 FROM syscolumns
  WHERE id = object_id('Apples')
  and name = 'PickedOn')
BEGIN
 ALTER TABLE Apples ADD PickedOn DateTime
 CONSTRAINT DF_Apples_PickedOn DEFAULT GetDate()
END

--Note that if I try to drop the PickedOn column at this point the command will fail
--because of the default value. Luckily, there's a name associated with that constraint:

IF object_id('DF_Apples_PickedOn') is not null
BEGIN
 ALTER TABLE Apples
 DROP DF_Apples_PickedOn
END

if object_id('UKey_Apples') is not null
BEGIN
 alter table Apples drop constraint UKey_Apples
END

if object_id('PK_Apples_Id') is not null
BEGIN
 alter table Apples drop constraint PK_Apples_Id
END

2) If you already have objects with weird names, you can still find them using the
information_schema views:


declare @constraint_name sysname,
 @cmd varchar(1000)
while exists(    select 1
     from information_schema.referential_constraints
    where constraint_name like 'FK__tblTask%')
begin
 SELECT @constraint_name = (
   select top 1 constraint_name
     from information_schema.referential_constraints
    where constraint_name like 'FK__tblTask%')
 PRINT 'Dropping constraint ' + @constraint_name
 SELECT @cmd = 'ALTER TABLE dbo.tblTask_Object
    DROP CONSTRAINT ' + @constraint_name
 EXEC (@cmd)
end

Performance Follow up: SqlHelper, object[] vs SqlParameter[]

2006-10-18T14:59:00.000-07:00

On a Q&D performance test as suggested by Phil, performance seems to differ only negligably, but on average, I (and the report I cited as well) was wrong.

Calling the methods included in a previous post, 5000 times each,

With parameters took 1203.125 milliseconds.
With objects took 1250 milliseconds.
Objects took -46.875 milliseconds less.

20000 times each:

With parameters took 4859.375 milliseconds.
With objects took 5015.625 milliseconds.
Objects took -156.25 milliseconds less.

If this test is accurrate, it costs pretty much a constant 0.0078 ms to use the SqlHelperParameterCache object to look up the parameters.

Thanks Phil! I think if we go by Haacked's recommendations on optimization, then in this case we should clearly go with the option that renders code most easy to read, write, and maintain.

Code Sample: Passing object[] to SqlHelper

2006-10-18T13:15:00.000-07:00

Thanks, Steve. Of course there should be some code. Here:
Apologies for the lost indentation: that I can't use CopyAsHtml well at blogger.com is probably the biggest reason that I should be blogging with Subtext.

// Call SqlHelper with the 'params SqlParameter[] overload'

public void GetWithSqlParams(SystemUser aUser)

{

SqlParameter idParam = new SqlParameter("@id", aUser.Id);

SqlParameter nameParam = new SqlParameter("@name", aUser.Name);

SqlParameter emailParam = new SqlParameter("@name", aUser.Email);

SqlParameter loginParam = new SqlParameter("@name", aUser.LastLogin);

SqlParameter logoutParam = new SqlParameter("@name", aUser.LastLogOut);

SqlHelper.ExecuteNonQuery(

Settings.ConnectionString, CommandType.StoredProcedure, "User_Update",

idParam,

nameParam,

emailParam,

loginParam,

logoutParam);

}

// Call SqlHelper with the 'params object[] overload'

public void GetWithObjects(SystemUser aUser)

{

SqlHelper.ExecuteNonQuery(

Settings.ConnectionString, "User_Update",

aUser.Id,

aUser.Email,

aUser.Name,

aUser.LastLogin,

aUser.LastLogOut);

}

// Which is semantically identical with:

public void GetWithObjectArray(SystemUser aUser)

{

SqlHelper.ExecuteNonQuery(

Settings.ConnectionString, "User_Update",

new object[]

{

aUser.Id,

aUser.Email,

aUser.Name,

aUser.LastLogin,

aUser.LastLogOut

});

}

Pass Parameters as Objects to MS Data Access Application Blocks

2006-10-18T11:08:00.000-07:00

Intuition seems too state that it's better to call the SqlHelper overloads that take SqlParameter[] for the parameters to a stored procedure or query.

But in fact, you'll get a performance boost in addition to easier coding, by calling the overload that uses object[]. That's because the parameters that get created on the first call to that proc or query get cached, so that the SqlHelper object doesn't have to create the SqlParameter objects anew, but only set their values and then give them to the SqlConnection object.

Here's a snippet from the Informit .Net Reference Guide:

SqlHelperParameterCache

Many of the SqlHelper methods query the database to discover stored procedure parameters. This is a huge benefit in that it prevents you from having to write code that creates SqlParameter arrays, but it's woefully inefficient if you do it for every stored procedure call. The SqlHelperParameterCache is a way to provide the convenience of parameter discovery while minimizing the cost. When you call one of the methods that does parameter discovery, SqlHelper checks the parameter cache to see if there has already been a call to that particular stored procedure on the supplied connection or connection string. If so, the parameter types are retrieved from the in-memory cache. If not, the SqlHelperParameterCache object discovers the parameters and then caches them. The result is that the first call to a given stored procedure incurs the cost of discovering the parameters, but subsequent calls get them from the cache, almost free. The cost of getting the parameters from the cache is less than creating the parameter array yourself for each call. The initial hit is expensive, but amortized over the application's lifetime, it's not expensive at all.

Stopping the War Problem

2006-10-16T11:58:00.000-07:00

First, an anecdote from Michael Nagler, department chair of Peace and Conflict Studies at UC Berkeley:

A woman was in Washington, DC, doing work for an upcoming Anti-War action near the capitol. She happened to wind up in an elevator standing among four men in colorful military outfits.
Feeling a little uncomfortable, she made polite smalltalk: "What are you gentlemen doing?" One of them proudly responded to the effect of, "We are lobbying for funds to be allocated for a new missle defense system. And what are you doing?"
"Protesting the actions of the united states military."
Uncomfortable silence followed. Then, as the men filed out of the elevator, one of them turned to her and said quietly, "Hurry up!"

In Nagler's words: This story is indicative of the current state of the United States population. Out of five:
One is probably sold that war is the right way to handle today's conflicts.
Two are probably unsure of what to do and are going along, perhaps because they haven't thought it through all the way.
One is probably actively doing something to stop the proliferation of unjust/illegal war.
...And at least one knows that the war is wrong, but is depending on others to pave the way.

The message: It does take a little courage to start speaking out against actions that have been proclaimed to be matters of national security. But if you find yourself among those who agree with the pundits claiming that, in addition to the tragic deaths of many thousands of United States, Afghani and Iraqi citizens, in addition to the deception that is given credit for the US ever gaining support for this war, the current war and war plans pose severe threats to U.S. security, then by engaging in some dialog, we might all find ourselves in the company of others who agree but who haven't yet known who to talk to about it.

And if you have talked much on this topic, then you may have been placed in the difficult position of not knowing how to answer the question, "You say the U.S. should pull out of Iraq, how do you think that should be done?" The current issue of Harper's Magazine, includes a very well conceived overview of a plan, complete with cost estimates and a very realistic outlook.

This issue of Harper's also includes some very good food for thought regarding the U.S. military plans regarding Iran.

Unzip your files

2006-09-27T16:26:00.000-07:00

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) {
        [void][System.Reflection.Assembly]::LoadWithPartialName($ns)
    } else {
        [System.Reflection.Assembly]::LoadWithPartialName($ns)
    }
}


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

2006-09-27T10:56:00.000-07:00

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!

Great.
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
      base.Append(loggingEvent);
  }
}

Here are the important snippets from the log4net configuration:


<appender name="PropertiesExpanderAppender" type="NameSpace.PropertiesExpanderAppender">
 <appender-ref ref="RollingLogFileAppender" />
</appender>
<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} ..." />
 </layout>
</appender>
<root>
 <level value="ALL" />
 <appender-ref ref="PropertiesExpanderAppender" />
</root>

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

2006-09-27T10:42:00.000-07:00

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.

HTTP ERROR: 400

2006-09-21T10:27:00.000-07:00

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: http://support.microsoft.com/kb/302409/EN-US/ if you're using self-cert and suddenly you're getting SSL errors connecting to sql server.

Anatomy of a PowerShell script

2006-09-20T00:22:00.000-07:00

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)
 push-location($dir)
 $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
}
else
{
#   Note the ease in shelling out to OSQL.EXE.  Just like a true shell language.
foreach($file in $files)
{
 write-host $file.name
 $results = osql -n -S $server -U $user -P $password -d $database -i $file.name
 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 1.3.6.1.5.5.7.3.3 -r -sv root.pvk root.cer `
        -ss Root -sr localMachine
    makecert -pe -n "CN=Jane Programmer" -ss MY -a sha1 `
        -eku 1.3.6.1.5.5.7.3.3 -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.

A real command shell from Microsoft

2006-09-18T17:03:00.000-07:00

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"

 push-location($basedir)

 $upgradeDirs = UseTheseUpgradeDirs(dir "Upgrade Scripts")

 push-location("Upgrade Scripts")
 foreach ($dir in $upgradeDirs)
 {
  RunScriptsIn($dir)
 }
 pop-location
 
 RunScriptsIn('Views')
 RunScriptsIn('Functions')
 RunScriptsIn('Stored Procedures')
 
 pop-location
}




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



function UseTheseUpgradeDirs( $dirs )
{
 $useThese = new-object System.Collections.ArrayList
 
 $conn = new-object System.data.SqlClient.SqlConnection(
  [String]::Format("Server={0};Database={1};User={2};Password={3};",
    $server, $database, $user, $password))
    
 $conn.open()
 
 foreach($dir in $dirs)
 {
  $sql = 
   "select count(1) from version where build >= '" + $dir.name.SubString(5) + "'"
  $cmd = new-object System.Data.SqlClient.SqlCommand($sql, $conn)
  $reader = $cmd.ExecuteReader()
  $foobar = $reader.Read()
  if ($reader[0]>=1)
  {
   $useThese.Add($dir)
  }
  $reader.close()
 }

 $conn.close()
 return($useThese)
}

main

Amy Goodman speaking in Northern California

2006-09-08T12:29:00.000-07:00

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 http://tour.democracynow.org/2006/09/santa_cruz_ca.html

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
MORE INFO:http://krcb.org/amy_goodman.htm

pyWin

2006-09-07T17:45:00.000-07:00

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

2006-09-07T17:00:00.000-07:00

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

Ruthless Database Administration

2006-09-06T21:21:00.000-07:00

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]

GO

/*************************************************************

*
* sproc: kill_processes
* author: tgannon@gmail.com
* 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)
AS
DECLARE test_cursor CURSOR
READ_ONLY
FOR
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)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @spid <> @@spid
BEGIN
PRINT 'Killing process ' + CONVERT(VARCHAR, @spid) + '.'
SELECT @cmd = 'KILL ' + CONVERT(VARCHAR, @spid)
EXEC (@cmd)
END
END
FETCH NEXT FROM test_cursor INTO @spid
END

CLOSE test_cursor
DEALLOCATE test_cursor

Regex Workbench, worth getting

2006-09-05T17:02:00.000-07:00

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.

IE7 Doesn't Suck

2006-09-04T12:56:00.000-07:00

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

2006-09-04T12:04:00.000-07:00

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.