SQL Server SP3 released

Service pack 3 for SQL Server 2005 was released last week. In it, they have fixed a curious bug that I reported back in January.

The bug occurs when trying to delete rows from a table that has a NULL value for an image column. This works fine normally, but if there is a foreign key referencing the table (to any of its columns), any rows that have had their image column updated to be NULL fail to be deleted. This SQL demonstrates the problem:

-- create two linked tables
CREATE TABLE [dbo].[TableA]
    [Identity] [int] NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [TableB_Identity] [int] NULL

CREATE TABLE [dbo].[TableB]
    [Identity] [int] NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [DATA] image NULL

    FOREIGN KEY ([TableB_Identity]) REFERENCES [TableB] ([Identity])

-- insert some data

-- this delete works successfully
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- insert some data

-- update the data to be have a NULL value

-- this delete doesn't work
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- this delete doesn't work
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

-- this delete does work successfully
        SELECT * FROM [TableB] AS TB
        AND TB.[Identity] = [TableB].[Identity]
SELECT COUNT(*) AS Remaining_Count_Should_Be_0 FROM [TableB]

Not all of the delete queries work correctly. The output of the script is four result sets with the count of how many rows are in the table at each point. All of them should be 0 (as is the case on SQL Server 2000), but in SQL Server 2005 without SP3 they are actually 0, 3, 3 and 0.

The simple delete query:


does not delete any rows after the values for the Data column have been updated to NULL, even though a similar select query:


Notably, if either the foreign key is removed, or the:


query is not performed, the script behaves as expected. Additionally, using text or ntext instead of image does not work as well, but using the new varchar(max), nvarchar(max) or varbinary(max) data types does work.

Apparrently, the distinction between NULL values stored as a result of an insert or an update has precendece in the WRITETEXT command:

If the table does not have in row text, SQL Server saves space by not initializing text columns when explicit or implicit null values are added in text columns with INSERT, and no text pointer can be obtained for such nulls. To initialize text columns to NULL, use the UPDATE statement. If the table has in row text, you do not have to initialize the text column for nulls and you can always get a text pointer.

This points to the “text in row” option having a bearing on this behaviour. Indeed, altering this option after creating the tables:

sp_tableoption N'TableB', 'text in row', 'ON'

results in the script working as expected. Useful as a potential workaround.

The bug is present in all versions of SQL Server 2005, but not in SQL Server 2000 or 2008.

A full list of what’s changed in SP3 can be found here, with a full list of the bugs fixed here.

Useful diagnostics tools

I recently visited a customer site to diagnose some problems with an application deployed on a server. Because I was effectively “visiting blind” in not knowing what was wrong or even if I would have internet access, I had to pre-empt any potential problems and take whatever tools I would need to diagnose them with me.

The following is a list of the tools I took:

  • Active Ports
    This is an equivalent to running netstat -nabv 5 from the command line, but wraps a nice GUI around it with the ability to look up the host names for connected IP addresses.

  • BareTail
    This is a simple log file viewer that can “tail” a running log and apply highlighting based on custom searches.

  • CorFlags
    This is a tool that comes as part of the Visual Studio SDK and enables a .Net application to be forced to run as 32-bit on 64-bit hardware. Existing applications can be tweaked without re-compilation.

  • Culture Launcher
    This is one of my own tools that can launch an .Net application using a different culture/language. The culture and UI culture can be set independently of each other.

  • Error Lookup
    This is a small tool that comes with Visual Studio (when you install the C++ components). It enables Win32 error codes to be translated into “meaningful” English error messages.

  • Managed Stack Explorer
    This is a tool that can preiodically capture stack traces from running .Net applications. It also shows a variety of information about the managed processes and threads running on a machine.

  • Red Gate Diagnostics Tool
    This is a tool from Red Gate that collects lots of system information from a computer. It is very useful because of the amount of data that it collects all in one place.

  • Snippet Compiler
    This is like a cut-down version of Visual Studio. It has an IDE-like editor (with only basic intellisense) and can compile and run .Net applications. The biggest plus is that it requires no installation.

  • SpaceMonger
    This is a tool that gives a visual representation of disk usage for a whole drive. This version is an older version of the tool, but is the last version that is free.

  • SysInternals
    This is the famous SysInternals Suite of tools, now owned by Microsoft, but still occasionally updated with new features and bug fixes. This contains lots of file, disk, network, process, registry and system utilities.

This toolset (along with a few custom-written SQL scripts) provided me with everything I needed to collect all the information I needed to get to the bottom of the problems.

Setting a proxy server for Windows updates on Windows Vista

I’ve recently been trying to get automatic Windows updates working on Vista. Every time it tried to fetch the updates, it reported an error code of 80070057. After getting more detailed information from the WindowsUpdate.log in the Windows directory, the problem turned out to be the proxy server in our office. Whilst my user profile has the correct proxy server settings, the Background Intelligent Transfer Service (BITS) that is used to download Windows updates doesn’t. The solution is to set the proxy server for the system.

To see the current proxy settings, run from the command line:

netsh winhttp show proxy

If it says “direct”, there are no proxy settings and Windows update probably will not work.

To set the proxy settings, run from the command line (you will probably need to run this with administrative permissions):

netsh winhttp set proxy proxy-server="yourproxy:port" bypass-list="<local>"

This will set the proxy server on the system to allow the BITS service to connect to the Windows updates servers.

Preventing accidental schema changes to the master database

If you have ever run some SQL within SQL Server Management Studio only to realise that you’ve run it against the master database by mistake, you’ll know that it can sometimes be hard to undo the damage.

A simple way to stop these accidental changes is to create a database trigger that will prevent any schema changes to the master database:

USE master

    RAISERROR(N'Do you really modify the master database?', 16, 1) WITH NOWAIT

Any time you attempt to change the master database, SQL Server will fail with an error. If you do want to make a schema change, simply disable the trigger and then re-enable it once the schema change is complete.

(NB. this only works with SQL Server 2005 and above)

Box selection in Visual Studio

I was editing a large SQL script inside Visual Studio today and needed to insert several spaces into multiple lines to make the script more readable.

Turning something like this:

some text on line a
some text on line b
some text on line c
some text on line d


some text      on line a
some text      on line b
some text      on line c
some text      on line d

I thought of doing it manually by hand, but as there were lots of lines to alter, I thought there must be a better way. I then remembered a feature of a word processor called ProText that I had many years ago on the Atari ST that had a feature called “Box Selection”. This enabled you to select text across multiple lines without having to select the whole line (a bit like drawing a box with the mouse).

A quick search later and I found the instructions on how to do box selections in Visual Studio in an article on Sara Ford’s blog.

Just hold down the Alt key whilst selecting text with the mouse and Visual Studio will switch from its normal “stream selection” mode into “box selection” mode. Once selected, you can indent the text using the tab key as normal and it will insert space to get the desired effect.

Blinking cursor in Firefox

I have recently re-installed Firefox, and was getting more and more annoyed by what seemed to be a blinking cursor appearing in web pages. After a bit of searching, I found out what it was thanks to Rishi who has had the same problem.

It is a feature called “Caret Browsing” which places a cursor in web pages so that text can be selected using only the keyboard. To turn the feature off, just press the F7 key or change the accessibility.browsewithcaret option from the about:config page.

Building solutions without Visual Studio

When working with several source control branches, especially with a large solution with many projects, it is not always practical to open Visual Studio to perform a quick build. Using NAnt is one alternative solution, but this requires creating and maintaining a build script. Using MSBuild from the command line is another option, but this involves getting the command line arguments correct, and working with command line output is not easy to visually filter. The same goes for using Visual Studio from the command line.

Gaston Milano has created a simple tool called Build Console capable of loading both MSBuild and Visual Studio solution files, and building any of the available build targets.

Build console

It’s main features are:

  • The ability to choose which target/project to build.
  • A build report in a tree structure to show the status of each project built.
  • The ability to choose the verbosity of the build output.
  • A coloured build output log to distinguish different types out log output.
  • A ‘quick history’ to load recently built solutions.

Whilst a little rough around the edges, it comes in very handy for those times where you just need to compile quickly without the overhead of loading Visual Studio.

Browser toolbars for web development

I’ve been doing a lot of HTML and CSS recently, and checking that pages appear and behave the same in different browsers can be a bit of a pain. Fortunatley, there are several toolbars that can be used to make this process easier.

Internet Explorer

  • Developer Toolbar
    This is a toolbar for Internet Explorer versions 6 and 7 that adds a DOM and CSS explorer and editor, as well as tools for viewing pages structures and various type of validation.

  • Web Development Helper
    This is similar to the Developer Toolbar above, but geared more for ASP.Net. It features several browsers for view state, caches, header and response details and call stacks, as well as a DOM explorer.


  • Firebug (also here)
    This is an extension that adds a CSS, HTML, Javascript and DOM monitor and editor to the browser, as well as a request monitor and a element inspector.

  • Web Developer Toolbar (also here)
    This is a toolbar that adds lots of utilities and tools, including validation and page information and outlining.

  • HTML Validator (also here)
    This is an extension that adds an HTML validator to the browser that validates pages in real time and displays warnings and errors in the page status bar.

  • Dust-Me Selectors
    This is an extension that enables you to inspect CSS style sheets for selectors and styles that are loaded but not used when browsing pages. Useful for consolidating style sheets after a site redesign. NOTE: this didn’t install properly using the xpi file, but worked following a manual install.

  • Modify Headers (also here)
    This is an extension that enables the viewing and modification of HTTP headers.

  • User Agent Switcher (also here)
    This is an extension that allows the configuration of custom user-agent strings and enables switching them whilst browsing.

  • IE Tab (also here)
    This is an extension that allows the viewing of pages using the Internet Explorer rendering engine, but within the Firefox application.

  • IE View
    This extension is similar to the IE Tab extension above, but will open Internet Explorer as a separate window instead of embedding it inside FireFox.

  • IE View Lite (also here)
    This extension is a rewrite of the IE View extension above, but written to be more compact and lightweight.

  • Opera View (also here)
    This extension is similar to the IE View extension above, but will open pages in Opera instead of Internet Explorer.


  • Web Development Toolbar & Menu
    This toolbar and menu set add a set of menus and a toolbar. The menus contain quick links directlry to HTML, CSS, DOM, JS and Unicode reference information. The toolbar adds tools for inspecting page contents, validation, page source viewers and form manipulation.

  • Opera Developer Console
    This tool adds a button onto a toolbar that when clicked opens a windows containing a DOM, JS, CSS and HTTP browser for the current page.

Fixing database logins after a restore

For several years now I’ve been moving development databases between SQL Servers using backup and restore. When you restore the database on the target server, the logins for the database are invariably broken with the database user having an empty login name, meaning that they cannot log in to the database. My usual fix is to delete the database user and re-add it. Paul Hayman however pointed out a useful stored procedure to fix broken logins:

sp_change_users_login 'Auto_Fix', 'username'

where username is the name of the account to fix.

The Auto_Fix option will attempt to match the broken login with an existing user with the same name.

More information on this can be found in the MSDN documentation. Specific things to note are that it only works with SQL Server and not Windows logins, and that you must be a member of the sysadmin fixed server role for it to work.

Running the OUTPUT clause from C#

SQL Server 2005 introduced a new feature called the output clause. This enables INSERT, UPDATE and DELETE queries to be run, with the original information which has been changed being returned. This is particularly useful if you want to run a query and know what has been changed by it by returning the identites of the modified rows.

The full documentation for the output clause can be found in SQL Server 2005 Books Online.

In trying to use this feature, I could get it to work in a query window, but when trying it using C# and ADO, it was not obvious how to execute the query and return the results because the ExecuteNonQuery() method of SqlCommand only returns the count of the number of rows that have been updated. After a bit of unsuccessful searching, I came across a post by Keyvan Nayyeri with something that gave me an idea:

OUTPUT clause works like a SELECT statement but its usage differs in INSERT, UPDATE and DELETE commands

Switching my code around to run the update query using the ExecuteReader() method of SqlCommand as would be used for a SELECT query proved to be fruitful, enabling the returned result set to be read.