An Awfully Big SQL Adventure

Problems Using GIF Images In SilverLight

2011-05-04T06:44:00.000-07:00

When first getting to grips with Silverlight, I had some problems with images.

I was creating a new Silverlight game for Play-Free-Games.com ("Panda Football" - yet another variant on the same old "Panda's playing football" theme one sees so often), and I was ready to start showing my Panda GIF images on screen.

No matter what I tried, the images were not displayed. I figured I must be specifying the path incorrectly, so I went through a long and tedious process of trying every possible relative or absolute method for defining the image path. I was on the point of tears when I tried displaying another image I happened to have to hand (a picture of a sheep - no further details required). To my intense joy, the image loaded up exactly as expected.

My initial assumption was that SilverLight does not support images of pandas, only farmyard animals. I was about to put this to the test by trying some goat, duck and chicken images when I realised that there was another key difference - my sheep pic was a jpg.

"Surely the problem isn't simply that Silverlight does not support the GIF format", I thought to myself, but not for the first time that day I was so wrong:

http://forums.silverlight.net/forums/p/39235/112333.aspx

So, I converted my GIFs to PNGs and all was well. Cashback!

Importing data into SQL Server from a text file using BULK INSERT

2011-03-13T14:24:00.000-07:00

As a .Net / SQL Server developer it is often necessary to import data in to your databases from other sources, such as text files. Ideally you would leave this type of data manipulation activity to the kind of weirdos who enjoy it (DBAs). Sadly however this is not always possible.

Therefore, in the old days of SQL Server 2000 I often created DTS packages to suck in my data. DTS was OK, especially the funky grey arrows, but it was a little time consuming. Then - disaster - DTS was phased out in favour of SSIS. At No Frills Corp our DBAs have not been able to give me a server that SSIS actually works on, so I was a bit stuck.

It was then that I came across the beatifully simple SQL command, BULK INSERT.

Just point a BULK INSERT command at your text file, tell it where to put the data, and you are all set. It can't be that easy can it? Yes, it really can:

BULK INSERT Chimps
   FROM 'D:\primates\chimps.csv'
   WITH 
      (
         FIELDTERMINATOR =',',
         ROWTERMINATOR ='\n'
      )

Cashback!

Finding the active node in a SQL Server Cluster

2011-03-04T13:39:00.000-08:00

DBAs are an eccentric breed aren't they?

At my place of work, No Frills Corp, we have 2 types of DBA: DBAs who are perpetually on the edge of a nervous breakdown and will happily go a whole day without saying anything except "No", "I'm busy" and "Must be a problem with your app", and DBAs whom you should not engage in the simplest of conversations unless you have a couple of hours to spare.

I much prefer the taciturn DBAs, but in both cases the best policy is not to talk to them unless you really have to. So, what if you just need to know something simple but important, like which node of our active-passive cluster is currently active?

The other day I came across the very simple answer to this question:

SELECT ServerProperty('ComputerNamePhysicalNetBIOS')

Bosh!

Dormant Full Text Index in SQL Server

2011-02-28T14:31:00.000-08:00

I recently created a full text index to improve search performance on some large text fields. The resulting performance was great, so I took it to show my boss (an IT "Bruno").

I had to wait a few minutes before I could see him, and then when I fired up my lovely new search page disaster struck in the form of a timeout - making me look like an imbecile.

I covered by showing him something shiny, and hastily hit refresh. The search came back in less than a second, as did each subsequent search. Happy boss.

When I got back to my desk I did another search and got another timeout. It seems my Full Text Index is going to sleep if not used for 5 minutes, and then takes 30 seconds or more to wake up. Rubbish!

So, I have had to set up a job to hit the FTI with a simple query every 5 minutes, keeping it "awake".

Something like this:

SELECT * FROM Dreams WHERE CONTAINS(SubText, 'fear of trombones')

Doesn't feel great having to do that, but now my Full Text Index is always ready for action.

Optional Search Parameters in SQL Server Stored Procedures

2011-02-28T14:08:00.000-08:00

I often create Stored Procedures in SQL Server for searching - but what is the best way to handle optional search parameters?

In the old days I wrote queries like this:

SELECT *
FROM Chimps
WHERE (@Age = 0 OR Age = @Age)
AND (@Weight = 0 OR Weight = @Weight)

... defaulting @Age and @Weight to zero if not specified.

However, I later found that this performs a lot better:

SELECT *
FROM Chimps
WHERE Age = ISNULL(@Age, Age)
AND Weight = ISNULL(@Weight, Weight)

... with the params defaulting to NULL if not specified. Lovely.

But wait! What if there is a chance that some of our chimps have a NULL value for Age or Weight? Then we would have to go a bit further:

SELECT *
FROM Chimps
WHERE ISNULL(Age, 0) = COALESCE(@Age, Age, 0)
AND ISNULL(Weight, 0) = COALESCE(@Weight, Weight, 0)

This still performs well in most of my scenarios, but let's face it - ideally our chimps will not have a NULL weight. Weigh those chimps!