Xerratus

.ToLongDateString() showing incorrect format on international server (Windows 2008 running IIS7)

John McGuinness — Sun, 03 Aug 2008 18:20:58 GMT

Over the weekend I helped launch a website that was located in the US but opted to host the site on a Canadian dedicated server. One of the first things I did while setting up the server was to change the regional settings from "en-CA" to "en-US" (control panel -> regional and language options). After pushing the site up to the server, I noticed during my testing that everywhere I was displaying dates (using the built-in .ToLongDateString() function of the DateTime object), the incorrect format was showing up. Further more, transaction dates were being entered into the database incorrectly as well.

en-CA formats:

Short date: "dd/MM/yyyy"
Long date: "MMMM-dd-yy"

en-US formats:

Short date: "M/d/yyyy"
Long date: "dddd, MMMM dd, yyyy"

So, the computers region was setup correctly but the incorrect dates were still being shown. Since the dates were being driven by a .NET object, I next looked at the .NET Framework (I'm running 2.0). The solution I found was to modify the globalization element of the web.config by adding the following to the <system.web> element:

<globalization

   fileEncoding="utf-8"

   requestEncoding="utf-8"

   responseEncoding="utf-8"

   culture="en-US"

   uiCulture="en-US"

/>

Furthermore, since this was a dedicated server and all of the sites were going to be US sites we moved this from the web.config to the machine.config so that all new sites would not run into this problem.

Fix for invalid character in sproc calling sp_xml_preparedocument that has valid vs.NET xml

John McGuinness — Thu, 31 Jul 2008 18:34:09 GMT

Here's one I just had to solve. I had an xml string that was being passed to a sproc that used sp_xml_preparedocument to insert the values using OPENXML. Problem was that the sproc was throwing the following error:

'An invalid character was found in text'

The odd thing was that the xml in question was valid in Visual Studio before it passed it to the sproc and did not have any illegal characters. What I found was that by simply changing the input value from varchar(max) to nvarchar(max) sp_xml_preparedocument was able to parse the xml just fine.

For a quick refresher, read up on nchar and nvarchar in T-SQL.

Visual Studio 2005 xml formatting "an error occurred loading this property page"

John McGuinness — Fri, 28 Mar 2008 15:57:33 GMT

The other day, Visual Studio 2005 threw an odd error, seemingly out of the blue, that stated something to the effect "Error loading property page" (sorry didn't grab a screen shot of it because I didn't think it was anything) while trying to open a config file. After clicking "OK", the file opened without any formatting, just black and white text. To say the least, I was a little bummed; I love formatting.

Unfortunately, the day it happened, I was a bit swamped with work. In all actuality, this happened over two weeks ago, I've just been too busy to look into it. This morning however, I decided to take a few minutes before my day got started and look into fixing this problem. First off, I wasn't sure what to Google and the vague things I could come up with were yielding little useful results. I then decided to see if I could see if Visual Studio could tell me anything further.

I opened up "options", selected "Text Editor" then "XML". The general property page seemed fine. I then clicked on the "Formatting" option and there it was: "an error occurred loading this property page". I now had something to search for.

Going back to Google, I searched for visual studio 2005 options xml formatting "an error occurred loading this property page" and the first result was this forum post which held the answer by Benson Yu - MSFT.

The issue was resolved by running “devenv /setup” in Visual Studio 2005 Command Prompt.

Nice! So, I tried it out.

Note: I did this while Visual Studio was open and yes, it worked without needing to close it.

After the command prompt ran (which did take a minute or two, so be patient) I went back to Visual Studios XML formatting option and saw that my options were there again.

The web.config file I had opened was still black and white, so I closed and reopened it and my formatting was returned.

All is well again... now, back to work.

Green Beer-a-licious

John McGuinness — Mon, 17 Mar 2008 17:36:27 GMT

Happy St. Patty's Day...

Drink up!

Changing Visual Studios Default Debugging Browser

John McGuinness — Thu, 17 Jan 2008 23:22:16 GMT

Recently, I uninstalled Vista and reinstalled XP on my work laptop. Upon doing so, I had to reinstall all of my developer tools. One of them was Visual Studio 2005. Oddly enough, When I installed Firefox I opted to have it run as my default browser (when I was on Vista I had IE). It didn't occur to me that this would cause any problems. That was until I started debugging applications.

At first, all seemed fine. Actually I thought it was going really good because after stopping the debugger, the web page remained open, something that didn't happen with IE. But, as time went on, I found that my system slowed down once I started debugging anything. Looking at the processes, I found that Firefox was using about 2 GB's of RAM. Try sucking that into Visual Studio to debug. Yeah, that'll cause some slow downs.

So, I decided that, as much as I like Firefox, I'll set IE as my default browser. Unfortunately, after doing this, Visual Studio kept debugging with Firefox and causing havoc with my machine. So, I did a quick search and found this obscure forum thread that explained how to reset Visual Studio's browser. And it's pretty simple.

First, open a web project. Find any .aspx page and right click it and select "Browse with.."

There you should see a list of all available browsers plus the built in one.

Select IE from the list and click "Set as Default".

Close either by clicking "Browse" or "Cancel". It doesn't matter which because the setting has been saved by this point.

Debug your application and notice that IE now opens up. And much faster I might add.

A better way to create a dynamic SQL stored procedure

John McGuinness — Fri, 11 Jan 2008 20:31:59 GMT

This one has been plaguing me for years: What is the best way to create a dynamic stored procedure that allows null inputs to filter results? To solve this, over the years, I've tried a few things, below are just a couple:

Build a SELECT statement string together concatenating non-null values then execute the string.
Use IF statements to narrow down the type of filtering requested and run the pre-built SQL statement with the corresponding non-null values.

The problem with the above solutions is that neither rely on one solid select statement.

Today, while working on another request to come up with a dynamic stored procedure for a specific report a client wants, I had an epiphany. If I use isnull() in conjunction to with the actual value, I can get the desired results, no matter what input(s) are null -if any or all.

Here's the magic line that allows just that:

WHERE D1.MarketID = isnull(@MarketID, D1.MarketID)

Now, we all know that isnull() returns the first non-null value. So, if @MarketID is null, then we just return the actual value of the row in question; D1.MarketId. For a fact, D1.MarketID will ALWAYS equal D1.MarketID. That's it!

So, if we create a sproc that takes in five different values (filters), all of which can be null, utilize the above isnull() trick, we can let out business layer decide which results to return simply by passing in an actual value or a DBNull.Value to the call. The stored procedure finally becomes dumb and all of the logic is finally in the hands of the business logic, where it should be, in a non-kludgie (pronounced "Hacked") way.

Here is a quick example:

CREATE PROC Reporting_Impressions
   @MarketID int = NULL,
   @StartDate datetime = NULL,
   @EndDate datetime = NULL,
   @Page varchar(25) = NULL,
   @LocationID uniqueidentifier = NULL
AS

SELECT D1.LocationId, D1.LocationName, D1.MarketId, D1.MarketName,
D1.DisplayedOnPage, count(D1.DisplayedDate) AS TotalImpressions
FROM PremiumMerchant_Displays AS D1 WITH(NOLOCK)
INNER JOIN PremiumMerchant_Displays AS D2 WITH(NOLOCK)
ON D1.LocationId = D2.LocationId AND D1.MarketId = D2.MarketId AND
D1.DisplayedOnPage = D2.DisplayedOnPage AND D1.ImpressionId <> D2.ImpressionId
WHERE D1.MarketID = isnull(@MarketID, D1.MarketID)
AND D1.DisplayedOnPage = isnull(@Page, D1.DisplayedOnPage)
AND D1.LocationId = isnull(@LocationID, D1.LocationId)
AND D1.DisplayedDate >= isnull(@StartDate, D1.DisplayedDate)
AND D1.DisplayedDate <= isnull(@EndDate, D1.DisplayedDate)
GROUP BY D1.LocationId, D1.LocationName, D1.MarketId, D1.MarketName, D1.DisplayedOnPage

GO

And here are some various ways to call this procedure:

--Specific market id
EXEC Reporting_Impressions 4, NULL, NULL, NULL, NULL

--All, no filters
EXEC Reporting_Impressions NULL, NULL, NULL, NULL, NULL

--Date range
EXEC Reporting_Impressions NULL, '1/1/2008 7:00:00 AM', '2/15/2008 11:00:00 AM', NULL, NULL

--Specific page
EXEC Reporting_Impressions NULL, NULL, NULL, 'Home', NULL

--Specific Location
EXEC Reporting_Impressions NULL, NULL, NULL, NULL, 'B40B82C9-A41A-49DA-8950-3CBFF4488AEA'

--Specific market id and page for a given date range
EXEC Reporting_Impressions 4, '1/1/2008 7:00:00 AM', '2/15/2008 11:00:00 AM', 'Directory', NULL

As you can see, there is only one stored procedure with many different results, all of which are dependent on the number of non-null inputs and snuggled within a nice, clean, single select statement.

SQL Random Sorting

John McGuinness — Thu, 10 Jan 2008 18:42:53 GMT

Nice little trick if you want to return results sorted randomly from a SQL 2000/2005 database:

SELECT * FROM Customers ORDER BY newid()

So say you want to return 10 random customers from the database, you'd modify the statement as such:

SELECT TOP 10 * FROM Customers ORDER BY newid()

For those who don't know, newid() generates a new guid, just as Guid.NewGuid() does in .NET. By ordering a uniquely generated guid, we're guaranteed random results.

Uninstalled Vista and reinstalled XP

John McGuinness — Thu, 20 Dec 2007 16:44:10 GMT

Well it was bound to happen. Everyone in my office had made the switch back, I was the last holdout. I finally had it with Vista!

I really wanted to like it, I really did. It looked sharp... hmm, come to think of it, that's all I can really say about it. I looked sharp. But in the end, after installing some updates, it became unstable and I failed to figure out what the cause was. Even with uninstalling said updates, it still randomly spiked the cpu to 100% for 5 or so minutes at a time, leaving me helpless to do any work.

So, before I left work yesterday I repartinioned my C:\ drive and reinstalled XP. Up until 11pm last night I was trying to get my machine back to par for work. I have to admit, after making the switch from XP to Vista then back to XP, I do notice that XP is considerably faster. I guess making it look "sharp" had it's disadvantages.

Quick note to Microsoft: WTF MAN! How could you screw up an OS so badly? I want Vista, but I want it STABLE. I don't want to have to cross my fingers before installing updates as I had to ever since I installed it. And yes, I have had to uninstall updates in the past.

Vista VPN Needs Active Connection BUT An Active Connection Already Exists

John McGuinness — Tue, 11 Dec 2007 21:23:24 GMT

Here's another WTF that Vista threw at me today. I had to come home early today and decided to work the rest of the day from home. Now, because I can't undock my laptop because Vista freezes, before I leave each day, I put it to "sleep". So, first thing I needed to do before I could resume working was to connect to my works VPN. Now, normally this is not an issue, but sometime (I'd say every 1 out of 4 times) Vista displays "This connection requires an active Internet connection" in the "Connect to a Network" dialog even though, directly below it, it clearly shows that I'm connected via my wireless connection.

To get around this, simple do the following:

Click on the "Set up a connection or network" link on the above dialog box.

Scroll down the list of connection options and select "Connect to a workplace".

Choose "Yes, I'll choose an existing connection" on the next screen and Vista will connect to the VPN.

Not sure why this happens but I've seen it enough times to warrant this post.

Three Christmas Trees

John McGuinness — Sun, 02 Dec 2007 19:25:41 GMT

Yesterday, my wife and I went out to our favorite christmas tree farm and picked out three lovely trees. Yes, I said three. We do this.

The farmer was nice enough to let my wife cut down the smallest of them.

Exhilarating! Her first confirmed kill.

After all that hard work, here she is in her "pose" under a warm cup of Peet's Coffee.