Base Table

I’m now using Wordpress to host my blog. You can find all my tumblr content duplicated there. Thanks, tumblr!

Bye, tumblr. I want people to comment on my posts. I want to have more control over how the content is presented. I want to be able to cross-post to Facebook.

Access a share on the network using different credentials

To stop Explorer raising errors when you try to access the network share or when you try to start Explorer using different credentials, you have to do some command link magic first.

Open a new command shell and run this command if the share is accessibly using only your admin credentials:

net use \\SERVER\Share * /USER:\DOM\admin_user

Type the password at the prompt:

You should see output indicating success:

The command completed successfully.

Now when you try to access \SERVER\Share in Explorer, it should just work!

Effect of `SET NOCOUNT ON` on `@@ROWCOUNT`

From the @@ROWCOUNT documentation:

Statements such as USE, SET , DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION or COMMIT TRANSACTION reset the ROWCOUNT value to 0.

Which means that if you get into the good habit of beginning all your stored procedure bodies with SET NOCOUNT ON;, you’ll be in for a nasty surprise when you code a trigger with short-curcuit logic.

For example, in this audit trigger, the INSERT statement will never execute:

CREATE TRIGGER dbo.Wibble_InsertAuditRow
ON dbo.Wibble
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
  SET NOCOUNT ON;

  IF @@ROWCOUNT = 0
  BEGIN
    RETURN;
  END;

  INSERT INTO Audit.WibbleHistory (
    Action,
    WibbleCode,
    WobbleSpec,
    GibletCount
  )
  SELECT
    'INSERT',
    WibbleCode,
    WobbleSpec,
    GibletCount
  FROM INSERTED
  UNION ALL
  SELECT
    'DELETE',
    WibbleCode,
    WobbleSpec,
    GibletCount
  FROM DELETED
END;

(Let’s assume that Audit.WibbleHistory has extra columns with default values that audit when and by whom each change is made.)

Because SET NOCOUNT ON sets @@ROWCOUNT to zero, the do-nothing code path will always execute.

To avoid this, make sure to check @@ROWCOUNT before setting NOCOUNT.

sp_change_users_login and ALTER USER

Crusty old SQL Server DBAs know all sorts of stored procedures for fixing up databases. A very useful one is sp_change_users_login, which you can use to map a database user to a server login after restoring a database from a backup.

You will need to do this when rebuilding a database server from scratch, because the backup does not store information about logins or any of the other server-level assets.

If you have to map the server login appuser to the SuperApp database user appuser, and you want to stay crusty, you can use

EXECUTE SuperApp.dbo.sp_change_users_login
  @Action = 'Auto_Fix',
  @UserNamePattern = N'appuser';

But you should prefer the new T-SQL language constructs, because the stored proc is going to be removed in a future version of SQL Server, and it is confisingly overloaded - it can change mapping or report on mapping depending on what value you pass to the @Action parameter.

The shiny new way top do the same is the ALTER USER statement:

USE DATABASE SuperApp;
ALTER USER appuser WITH LOGIN = appuser;

"Select Star: Ingeniously Pale Ale. We select only the finest ingredients to create Select Star IPA. In our view, you can count a number of distinct flavours, which coalesce to make this a unique ale. Just be careful not to drink until you drop!"

Red Gate

PostgreSQL Windows 7 Installation Instructions

Incomplete.

ADMIN:

Create a postgres user account. Prompt for a password.

net user postgres * /add

Download the PostreSQL 9.1.4 Windows 32-bit binaries from http://get.enterprisedb.com/postgresql/postgresql-9.1.4-1-windows-binaries.zip.

Unzip to %PROGRAMFILES%\PostgreSQL\9.1.4\.

Add bin directory (%PROGRAMFILES%\PostgreSQL\9.1.4\bin) to the system path.

Create directory %PROGRAMFILES%\PostgreSQL\9.1.4\data.

Grant read, execute, and write for data to postgres.

icacls data /grant postgres:(OI)(CI)RXW

POSTGRES:

Initialize a new PostgreSQL database cluster at %PROGRAMFILES%\PostgreSQL\9.1.4\data. Call the superuser postgres. Prompt for a superuser password on initialization.

initdb --username postgres --pwprompt --pgdata "%PROGRAMFILES%\PostgreSQL\9.1.4\data"

ADMIN:

Grant read, execute, and write for all files in data to postgres.

TODO

POSTGRES:

C:\Program Files\PostgreSQL\9.1.4>pg_ctl register -N postgres-9.1 -U postgres -D

“%PROGRAMFILES%\Postgres9.1\data”

Fails:

pg_ctl: could not open service manager

“I’m getting permissions errors when installing/running initdb”

The PostgreSQL wiki has a helpful section on Common Installation Errors:

I’m getting permissions errors when installing/running initdb

Make sure the PostgreSQL service account has permissions on the directories leading up to the one you have installed into. The installer will set permissions on the install directory but not on parent directories of it.

You may also see related errors show up as The database Cluster initialisation failed during the One Click installer. Check your install-postgresql log but it is usually related to permission errors. The following thread may help,

http://forums.enterprisedb.com/posts/list/2044.page#7503

Or alternatively, you can fix up the directory permissions and then manually restart the initcluster.vbs script like this for v9,

cscript //NoLogo "<install_path>/installer/server/initcluster.vbs" "postgres" "postgres" "<password>" "<install_path>" "<data_path>" 5432 "DEFAULT"

The recommended fix is basically ‘do the installer’s job yourself’. The install script, initcluster.vbs, is pretty broken. It tries to use the icacls utility to set the correct permissions, but uses the wrong syntax. On my machine, the script invokes the command:

icacls "C:\Program Files\PostgreSQL\9.1\" /grant Sco:(RX)(NP)

The command fails with the following error:

Invalid parameter "Sco:(RX)(NP)"

From the icacls documentation:

Perm is a permission mask that can be specified in one of the following forms:

• A sequence of simple rights:

  • F (full access)
  • M (modify access)
  • RX (read and execute access)
  • R (read-only access)
  • W (write-only access)

• A comma-separated list in parenthesis of specific rights:

  • D (delete)
  • RC (read control)
  • WDAC (write DAC)
  • WO (write owner)
  • S (synchronize)
  • AS (access system security)
  • MA (maximum allowed)
  • GR (generic read)
  • GW (generic write)
  • GE (generic execute)
  • GA (generic all)
  • RD (read data/list directory)
  • WD (write data/add file)
  • AD (append data/add subdirectory)
  • REA (read extended attributes)
  • WEA (write extended attributes)
  • X (execute/traverse)
  • DC (delete child)
  • RA (read attributes)
  • WA (write attributes)

Inheritance rights may precede either Perm form, and they are applied only to directories:

• (OI): object inherit
• (CI): container inherit
• (IO): inherit only
• (NP): do not propagate inherit

Inheritance rights precede perms, and simple perms do not require parenthesis. So, the syntactically correct parameter is Sco:(NP)RX.

Almost makes me want to use SQL Server Express instead. But I’ll sleep on it now and persevere tomorrow.

PostgreSQL installs with errors, even as Administrator

When I first tried to install PostgreSQL, it failed. I assumed the problem was caused by not running the installer with administrative privileges.

I uninstalled PostgreSQL and tried again, this time running the installer as Administrator.

The installation has failed again, this time with a different set of errors. A dialog box appeared at the end of the installation:

Problem running post-install step. Installation may not complete correctly. The database cluster initialization failed.

From the log file:

Error running cscript //NoLogo "C:\Program Files\PostgreSQL\9.1/installer/server/initcluster.vbs" "postgres" "postgres" "****" "C:\Program Files\PostgreSQL\9.1" "C:\Program Files\PostgreSQL\9.1\data" 5432 "DEFAULT" : Program ended with an error exit code
Problem running post-install step. Installation may not complete correctly
 The database cluster initialisation failed.

The log provides more detail. This first error looks like it’s passing a bad parameter to icacls.

Ensuring we can read the path C:\Program Files\PostgreSQL\9.1 (using icacls) to Sco:
    Executing batch file 'rad86B3F.bat'...
    Invalid parameter "Sco:(RX)(NP)"

The second error looks like a permissions issue.

fixing permissions on existing directory C:/Program Files/PostgreSQL/9.1/data ... initdb: could not change permissions of directory "C:/Program Files/PostgreSQL/9.1/data": Permission denied

The first error could be as a result of the second. I’ll investigate the script initcluster.vbs to see if I can work out what’s going wrong.

Uninstall PostgreSQL cleanly

The Windows uninstaller of PostgreSQL 9.1 does not remove postgres service account or the server data directory, for which the default value is C:\Program Files\PostgreSQL\9.1\data.

To completely remove all PostgreSQL artefacts, run the uninstaller, open an elevated command prompt, and issue the following commands:

net user /delete postgres
rmdir /s /q "C:\Program Files\PostgreSQL"
del %temp%\install-postgresql.log
del %temp%\uninstall-postgresql.log
del %temp%\bitrock_installer*.log

This performs the following cleanup tasks:

• Remove the postgres service account.
• Remove Postgres’s program files directory, which implicitly removes the data directory as well.
• Delete all log files created during the installation and uninstallation processes. The last command uses a glob because the BitRock installer generates a new file every time it is run.

PostgreSQL

This was originally posted at the scotunes blog. I wrote this before I tried to install PostgreSQL.

In my day job I use SQL Server. But I’ve always wanted to learn another database system, because I believe knowing more than one will make me a better developer on both platforms.

When selecting a database platform for my latest data analysis project, I identified the following requirements: automatic source control, simple builds and deployment, easy unit testing, and spatial support, and an affordable total cost.

The PostgreSQL community seems at least as active as the SQL Server community in providing these features. I guess you get more options in the open source world. For each tool class below, the example tool is just the first I heard a reasonable recommendation for - there is at least one more. All the PostgreSQL tools are open source.

For source control, builds and schema deployment, apgdiff. Compare SQL Compare in the SQL Server world, which is great, but too expensive for personal use.

For a unit testing framework implemented within the database, pgTap. Compare tSQLt for SQL Server. tSQLt is open source, but immature.

For spatial support, PostGIS. SQL Server understands spatial data out the box. (It’s hardly a core component of a relational database system, but I can only applaud, because spatial queries are very powerful and look cool.)

Because I’ve not implemented the database part of my project yet, I’ve got nothing to lose by choosing an unfamilar technology except a couple of drunken weekends. And there is so much to gain by learning a new system.

That’s why I’ve chosen PostgreSQL.

PostgreSQL installed with errors

PostgreSQL 9.1 asks less questions during install than SQL Server 2008. That sure makes it seem easier, but it’s academic if it installs with errors:

Error dialog one said:

A non-fatal error occurred whilst loading database modules. Please check the installation log in C:\Users\Sco\AppData\Local\Temp

Error dialog two immediately followed to say:

Problem running post-install step. Installation may not complete correctly Error reading file C:/Program Files/PostgreSQL/9.1/data/postgresql.conf

The text of each was not copyable or clickable.

There are two files in the Temp directory: bitrock_installer.log and install-postgresql.log. They appear to be identical to each other.

According to the files, the PostgreSQL service started successfully:

Script output:
 Starting postgresql-9.1
Service postgresql-9.1 started successfully
startserver.vbs ran to completion

In Windows Services there is a new service called postgres-9.1, startup type Automatic, logging on as .\postgres.

Appartently the install script failed to load some additional SQL modules:

Loading additional SQL modules...
Executing cscript //NoLogo "C:\Program Files\PostgreSQL\9.1\installer\server\loadmodules.vbs" "postgres" "****" "C:\Program Files\PostgreSQL\9.1" "C:\Program Files\PostgreSQL\9.1\data" 5432
Script exit code: 2

Script output:
 Installing the adminpack module in the postgres database...
    Executing 'C:\Users\Sco\AppData\Local\Temp\radA7A3D.bat'...
psql: could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (::1) and accepting
    TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
    Is the server running on host "localhost" (127.0.0.1) and accepting
    TCP/IP connections on port 5432?

Failed to install the 'adminpack' module in the 'postgres' database
loadmodules.vbs ran to completion

Script stderr:
 Program ended with an error exit code

Error running cscript //NoLogo "C:\Program Files\PostgreSQL\9.1\installer\server\loadmodules.vbs" "postgres" "****" "C:\Program Files\PostgreSQL\9.1" "C:\Program Files\PostgreSQL\9.1\data" 5432 : Program ended with an error exit code

And there was an error to read what looks like a configuration file:

Problem running post-install step. Installation may not complete correctly
 Error reading file C:/Program Files/PostgreSQL/9.1/data/postgresql.conf

When I attempt to connect to the new instance using the command line client psql, my connection is refused:

C:\Program Files\PostgreSQL\9.1\bin>psql
psql: could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5432?
could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (127.0.0.1) and accepting
        TCP/IP connections on port 5432?

It’s late, so I’m going to sleep on it. My suspicion is that I should have run the installer under an Administrator account rather than just running the exe straight out of Opera. If Administrator rights are required for successful installation, this should be made clear at the beginning of the installation process.

Whatever the cause of failure, the error dialogs could be more helpful, by showing me the text of the error directly instead of making me type out a directory name into Explorer to look for an unspecified log file.

Fiddler System.OutOfMemoryException

I use Fiddler to trace and analyze my HTTP traffic. It’s a simple and visual way to debug a web scraping application.

Fiddler v2.4.0.0 can raise an out-of-memory exception when you try to save a lot of sessions in one SAZ file.

In my case, I tried to save 6,704 sessions in one SAZ file after recording a nine-hour site scraping session.

Fiddler got more than halfway through the list of sessions before halting with this exception:

As a workaround, I selected the first 3352 sessions and saved them in a “Part 1” SAZ file. For more than a minute the status bar looked like this, the number incrementing quickly from 0 to 3,352:

3,352 / 6,681   Collecting data...  #1408 (ID: 1408)

The operation completed successfully. With no errors I saved the next 3352-ish sessions to a “Part 2” SAZ file. Slightly less than 6,704 sessions were saved in total. I don’t know why.

The possibility of an OutOfMemoryException is a known issue on the Fiddler website:

Fiddler works by storing the entire request and response in memory. If you are performing a huge download (hundreds of megabytes) it’s possible that Fiddler cannot find a free memory block large enough to hold the entire contiguous response, and hence you’ll run into this out of memory problem. It’s also possible that if you have thousands of sessions in the Fiddler session list, even a relatively small memory block will not be available to store a response a few megabytes in size. You can reduce the incidence of this problem by clearing the session list (CTRL+X) or configuring it to automatically trim to the most recent two hundred sessions (Click the Filters tab, and click the “Keep only the most recent sessions” option at the bottom).

This doesn’t completely explain to me why saving a an SAZ file of a large trace should do the same. It’s as if Fiddler is trying to allocate all the space for the SAZ file in memory as one contiguous block.

Seems like the quick fix would be to run Fiddler on a 64-bit version of Windows:

Fiddler2 now supports running on 64bit computers. If you’re on a 64-bit machine, you’ll never hit a problem.

64-bit Windows has a much larger address space than 32-bit Windows.

But wouldn’t it be great if Fiddler could stream the session to disk in small sequential chunks instead of saving one monolithic chunk? Because of the workaround I used here (two monolithic chunks), my trace is now forever cut into two files. Not a big deal in practice, but it would be neater to have all the related data in one file.

"Learn Mercurial, trust Mercurial, and figure out how to do things the Mercurial way, and you will move an entire generation ahead in source code control. While your competitors are busy taking a week to resolve all the merge conflicts they got when a vendor updated a library, you’re going to type hg merge and say to yourself, “Oh gosh, that’s cool, it just worked.” And Mike will chill out and share a doobie with the interns, and soon it will be spring and the kids at the nearby college will trade in their heavy parkas for skimpy A&F pre-torn T-shirts, and life will be good."

Joel Spolky, Subversion Re-education

Create a new file at the command line

As far as I know, Windows lacks a utility like Unix’s touch to create a new empty file from the command line.

However, you can use the copy utility to perform the same function, by copying from the NUL device to a new file :

copy NUL EmptyFile.txt

This should produce output indicating success:

1 file(s) copied.

You can confirm the file now exists and is empty by inspecting the output of the dir utility. Use the following command to inspect the file:

dir EmptyFile.txt

The output confirms that the file was recently created (relative to the time of writing), and that it is zero bytes long:

 Volume in drive C has no label.
 Volume Serial Number is 9ACB-79DF

 Directory of C:\Users\Sco

01/07/2012  20:43                 0 EmptyFile.txt
               1 File(s)              0 bytes
               0 Dir(s)  32,823,578,624 bytes free