SqlException error numbers for deadlock, connection and command timeouts

I'm posting this here so that I can find it in the future.

When accessing SQL Server via SqlClient (could be ADO.net or Entity Framework), any error is wrapped in an instance of SqlException and the Number property will be set to provide an indication of the specific error condition. In the event of a connection timeout, the Number property will be set to either 2 or 53. A command timeout will set the Number property to -2. And a deadlock will return 1205 in the Number property.

Splitting and combining strings with TSQL

My current work includes a batch process that validates 1000's (and sometimes more) transactions against a dozen+ validation rules. Recently this process became the source of blocking issues, causing a lot of users to become very unhappy.

Previously the rules were written in a very straight-forward manner. Usually something similar to:

UPDATE transactions
SET 
reason = reason + 'A'
, status=6 -- exception
WHERE {some rule}

It's probably obvious where the blocking became a problem.

Our alternate approach was to collect the a list of invalid transactions and the rule that was violated into a table and mark all of the affected transactions with a single UPDATE.

CREATE TABLE #badRows (id BIGINT, rule char(1));

INSERT INTO #badRows (id, rule)
SELECT id, 'A'
FROM transactions
WHERE {some rule}

The next challenge was pivoting and rule-violation codes into a single string so that the reason field of the transaction could be updated, with the additional constraint that any existing rule-violation code had to be maintained. We came up with the following:

;WITH both AS (
-- combine existing violations with any new ones
SELECT t.id, t.reason 
FROM transactions t
INNER JOIN #badRows br ON br.id = t.id
UNION ALL
SELECT id, rule FROM #badRows
)
, ix AS
( 
-- split any strings into single characters
SELECT id, SUBSTRING(reason,Number,1) AS code
FROM both  
INNER JOIN master.dbo.spt_values ON Number BETWEEN 1 AND LEN(reason) AND type='P'
)
, crushedReasonCodes AS (
-- combine multiple rows into a single string
SELECT DISTINCT id, (
SELECT DISTINCT code AS [text()]
FROM ix
WHERE ix.id = o.id
FOR XML PATH ('')) as reason
FROM ix AS o
)
UPDATE transactions
SET reason = crc.reason
, status = 6
FROM transactions t
INNER JOIN crushedReasonCodes crc ON crc.id = t.id

Installing SQL Server “Denali” CTP3 on Windows 8 Preview

I, and I’m sure many other developers, have been eager to begin experimenting with developing on Windows 8 and building Metro style apps. I was fortunate to attend //build and receive one of the Samsung 700T tablets with Windows 8 and Visual Studio 11 already loaded. Unfortunately my wife and daughter also enjoy the tablet so it’s not available to me for long programming sessions.

So in order to make everyone happy, I have installed the Win8 Preview in a VM under VMware Workstation 8. Here’s the configuration:

Host: Windows 7 running VMware Workstation 8
Guest: Windows 8 Preview (build 8102), 2GB RAM, 2 processors and 40 GB SCSI hard drive

I installed Visual Studio 11 without any problem other than the fact that it installed SQL Server without giving me any choice for the configuration. The easiest way to remedy that was to uninstall SQL Server and then reinstall it.

You can probably guess that the reinstall didn’t go smoothly. If it had, there’d be no reason for this post, eh?

I started by download the installer from MSDN. There are a few choices depending on whether you wanted 32bit or 64bit, with or without tools. I pulled down the 64bit package that had the tools.

Then I copied it to the VM.

I double-clicked the .EXE, it unpacked into a temporary folder, a command windows briefly flashed and then nothing. So I did what anyone would have done. I double-clicked the .EXE again and watched the same sequence of events unfold. “Ah!” I said. I must need Admin privileges! No problem, I launched a command window as Administrator, navigated to the folder and ran the .EXE.

Same results as before.

I was confused because SQL Server had already been installed on the machine. I had just uninstalled it!

In the process of poking around on the machine, I ended up in the “Turn Windows features on or off” section of the Uninstall dialog. It was in there that I noticed that .NET 3.5 was not enabled. I enabled it, went back to the command prompt and retried the installer.

Something happen this time:

The installer can’t find .NET 4.0 but 4.5 is on the box! I hit Continue and the SQL Server Installation Center dialog appeared on the screen.

It was easy sailing from this point!

By the way, you’ll see this windows pop up a lot!

The installation continued without any problem and I was able to successfully install SQL Server “Denali” CTP3.

The unhandled exception dialog appeared again when I tried to close the SQL Server Installation Center dialog:

Hitting the Quit button finished closing the dialog.

If you’re curious, here are the SQL Server apps as they appear on the Win8 Start page:

And here’s the new SQL Server Management Studio

Messin’ with the WCF Web API

Back in April, Microsoft released the fourth preview of WCF Web API on CodePlex. The intent of the WCF Web API is to make it easier to expose application data and services over HTTP. I’ve seen a lot of people throw in the REST descriptor but I’m not ready to go there yet.

I’ve spent the last couple of weeks playing with the WCF Web API and I’ve decided to put what I’ve learned out there for others. I’m imagining a series of posts but we’ll have to see where my schedule allows me to go.

In this first post, I want to basically put down the steps that I used to get started; consider this a step above the traditional Hello World program.

It’s all about the data

To get started, I first needed something to serve up. There is always the typical Northwind or AdventureWorks database that everyone is probably already familiar with. Instead, I went for something different. Shawn Wildermuth has been kind enough to to make available a database of XBox games. I’m running SQL Server 2008 so I grabbed the appropriate zip file and attached the database that is contained within it.

Next I need a host. A WCF service can be hosted within a number of application. I going to use an ASP.NET MVC 3 Application.

As long as I’m playing with new tech, I’m going to use the Code First features of Entity Framework 4. If you’re following along at home and Entity Framework isn’t already installed, you can get it via NuGet. The package name is EntityFramework.

The next thing is to create some classes that EF will use to expose the data in the Xbox games database. I’ll create three: Game, Genre and Rating and put them in the Models folder. This seemed like a good enough place to store them for now. I can always move them later.

    public class Game

    {

        public int Id { get; set; }

        public string Description { get; set; }

        public string Developer { get; set; }

        public Genre Genre { get; set; }

        public string Name { get; set; }

        public decimal? Price { get; set; }

        public string Publisher { get; set; }

        public Rating Rating { get; set; }

        public DateTime? ReleaseDate { get; set; }

    }

    public class Genre

    {

        public int Id { get; set; }

        public string Name { get; set; }

    }

    public class Rating

    {

        public int Id { get; set; }

        public string Name { get; set; }

    }

The data is accessed through an instance of DbContext. Mine looks like this:

    public class XBoxGames : DbContext

    {

        public DbSet<Game> Games { get; set; }

        public DbSet<Genre> Genres { get; set; }

        public DbSet<Rating> Ratings { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

        {

            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Genre>().ToTable("Genres", "SimpleGames");

            modelBuilder.Entity<Genre>().HasKey(g => g.Id);

            modelBuilder.Entity<Genre>().Property(g => g.Id).HasColumnName("GenreID");

            modelBuilder.Entity<Rating>().ToTable("Ratings", "SimpleGames");

            modelBuilder.Entity<Rating>().HasKey(g => g.Id);

            modelBuilder.Entity<Rating>().Property(r => r.Id).HasColumnName("RatingID");

            modelBuilder.Entity<Game>().ToTable("Games", "SimpleGames");

            modelBuilder.Entity<Game>().HasKey(g => g.Id);

            modelBuilder.Entity<Game>().Property(g => g.Id).HasColumnName("GameID");

            modelBuilder.Entity<Game>()

                .HasOptional(g => g.Genre)

                .WithMany()

                .Map(m => m.MapKey("Genre"));

            modelBuilder.Entity<Game>()

                .HasOptional(g => g.Rating)

                .WithMany()

                .Map(m=>m.MapKey("Rating"));

        }

    }

The three properties Games, Genres and Ratings are used to access and manipulate the data using Linq. The OnModelCreating() override is the key to the Code First implementation. This method contains the mapping of the tables to the classes and defines the relationships between the tables, in terms of the classes.

The last item is to tell Entity Framework where to find the database. I’ve added the following connection string to the web.config file:

  <connectionStrings>

    <add name="XBoxGames"

         connectionString="data source=.\SQLEXPRESS; Integrated Security=SSPI; database=XBoxGames"

          providerName="System.Data.SqlClient"/>

  </connectionStrings>

 

Resources

The next step is to expose the game data over HTTP. If you were wondering when I’d get to WCF Web API, well, that’s now.

Like EntityFramework, WCF Web API can be added to the project using NuGet. The package to install is WebApi.All

The nice thing about using NuGet to add libraries to your solution is that it will automatically pickup any dependencies that are required. I needed four.

I’ve decided to create a resource called GamesResource and expose it over HTTP.

    [ServiceContract]

    public class GamesResource

    {

        [WebGet(UriTemplate = "")]

        public List<Game> GetGames()

        {

            using (var gamesRepository = new XBoxGames())

            {

                return gamesRepository

                    .Games

                    .AsNoTracking()

                    .OrderBy(g => g.Id)

                    .Include("Genre")

                    .Include("Rating")

                    .ToList();

            }

        }

    }

Accessing this resource will return a list of all the games, sorted by the game ID.

Note: Returning the entire table via HTTP is probably a very unlikely scenario, if for no other reason than you are stressing your network and server resources. The most common mitigation is to provide either filtering or paging capabilities. I plan to add paging in a future post.

The last thing that I have to do is add some configuration that exposes the new GamesResource. Within Global.asax.cs, I’ve changed the RegisterRoutes() method to:

        public static void RegisterRoutes(RouteCollection routes)

        {

            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapServiceRoute<GamesResource>("games");

            routes.MapRoute(

                "Default", // Route name

                "{controller}/{action}/{id}", // URL with parameters

                new { controller = "Home", action = "Index", id = UrlParameter.Optional } // Parameter defaults

            );

        }

The call to MapServiceRoute() exposes the GamesResource as an Uri whose path starts with “games”. For example, the Uri http://localhost/games would be directed to our GetGames() method within GamesResource.

Starting the site with F5 and navigating to the games resource (for me is: http://localhost:1064/MessinWebApi/games) displays the list of games, encoded using Xml!

Returning a single game

I now want to allow the consumer of my service to fetch a single game using an Uri similar to http://localhost/games/12. This is easy and built into the WCF Web API via the UriTemplate parameter of the WebGet Attribute. This will look familiar if you’ve worked with ASP.Net MVC routing.

I’m going to add a new method to GamesResource called GetSingleGame():

        [WebGet(UriTemplate = "{id}")]

        public Game GetSingleGame(string id)

        {

            using (var gamesRepository = new XBoxGames())

            {

                int idToFind = int.Parse(id);

                return gamesRepository

                    .Games

                    .AsNoTracking()

                    .Include("Genre")

                    .Include("Rating")

                    .Where(g => g.Id == idToFind)

                    .FirstOrDefault();

            }

        }

 

 

GetSingleGame() is very similar to GetGames() with the exception that I have defined a token called {id} within the UriTemplate property of the WebGet attribute and I’ve added a parameter to GetSingleGame() to receive the value. The WCF Web API infrastructure will handle parsing the incoming request to get the ID and making the appropriate call to GetSingleGame().

When put into motion, a request for the game with an ID of 12 (http://localhost:1064/MessinWebApi/games/12) results in the following Xml:

This is just the “tip of the iceberg” for WCF Web Api. There is a very extensible pipeline built into the framework. I plan to explore some of these features in future posts.

ALTER COLUMN on XML column results in Error 511

We frequently change the schema that constrains an XML column in one of the tables. When it’s time to update the schema collection and re-type the column, we use the following steps:

1. Remove the type with the ALTER TABLE statement:
   ALTER TABLE <table> ALTER COLUMN <xml-column> XML;
2. Change the schema collection by recreating it.
3. Reset permissions:
   GRANT EXECUTE ON XML SCHEMA COLLECTION…
4. Reapply the schema constraint to the column:
   ALTER TABLE <table> ALTER COLUMN <xml-column> XML(<schema collection>)

Twice I’ve started getting error 511 on step one:

Msg 511, Level 16, State 1, Line 1
Cannot create a row of size 8073 which is greater than the allowable maximum of 8060.
The statement has been terminated.

This thread from the SQL Server XML forum indicates that the behavior is “by design.” There seems to be a limit to the number of times that you can alter a column. The good news is that there is a work-around. Rebuilding the indexes on the table appears to reset the alter-count.

DateTime.MinValue != SQL Server minimum datetime value

I’m sure that most people know this but I’m betting that there are a few that don’t. I say this because I just found a line of code that is trying to insert DateTime.MinValue into a SQL Server datetime column. The result is:

Arithmetic overflow error converting expression to data type datetime.

The value of DateTime.MinValue is 1 Jan 0001 12:00:00am.

The minimum value that you can put into a SQL Server datetime column is 1 Jan 1753. On the other hand, if you’re lucky enough to be using SQL Server 2008 and you have control over the table definitions, you can use the new datetime2 datatype. Datetime2 has an extended date range of 1 Jan 0001 to 31 Dec 9999.

TSQL Gem

This works:

Looking at the BOL, the FROM clause is optional.

I guess I just never paid attention

Generate a sequence of numbers in TSQL

Sometimes you need a quick way to generate a sequence of numbers. The recursive query functionality that was introduced in SQL Server 2005 makes this really easy.

WITH Numbers (val) AS 
(
	SELECT 1 as val
	UNION ALL
	SELECT 1 + val FROM Numbers WHERE val < 100
)

SQL Server XML - Replacing the value of an attribute with a column from the same row

This will hopefully save someone the 45 minutes that it took me to figure it out. I wanted to replace the value of an attribute within an XML column with the value of a column on the same row.

UPDATE myTable
SET xmlColumn.modify('replace value of (/x/y/@a)[1] with sql:column("columnName")')
WHERE ...

When I tried wrapping the call to sql:column with braces (ex. {sql:column("columnName")}, I received error 2224 - An expression was expected.

When I tried enclosing the whole thing in double-quotes (ex. "sql:column("columnName")", I received error 2370 - No more tokens expected at the end of the XQuery expression. Found 'columnName'