Give the New PIVOT and UNPIVOT Commands in SQL Server 2005 a Whirl

Nice article about PIVOT and UNPIVOT in SQL Server 2005

This must be painful

Paul Thurrott wrote an article where he urges us to boycott IE because of it's lack of support for standards. Now someone has written a nice reply to this...

Ouch ouch ouch

Let’s get into standards now. Guess what, Paul? Your site,
WinSuperSite.com currently has 124
validation errors, according to the W3C’s Markup Validation Service.
Even worse, the page which contains your “Boycott IE” story currently has 207
validation errors. Both pages don’t even define the page’s doctype,
which is almost always the first line of the web page.

Page splits

Page splits are not too good for performance and should be closely watched.
One way of determining whether you are having page splits and how many is to use the function ::fn_dblog (@StartingLSN, @EndingLSN). Passing NULL to the two parameters makes sure that the whole log is being read.

eg.

SELECT [Object Name], [Index Name], COUNT(*)
FROM ::fn_dblog(NULL, NULL)
WHERE Operation = 'LOP_DELETE_SPLIT'
GROUP BY [Object Name], [Index Name]
ORDER BY COUNT(*) DESC

You can reduce page splits by specifying a good fill factor. There is no real rule of thumb about fill factors. It all depends on the rowsize and number of rows inserted (between index rebuilds). So specifying a good fill factor and a DBCC DBREINDEX from time to time will minimize the number of page splits.

An unexpected error occurred in Report Processing. (rsUnexpectedError)

I'm creating one of the most important reports for the business and I'm having a weird problem. The report renders just fine in the browser but as soon as I try to export it to a PDF (which is basically what we do with all our reports - of course automatically through the webservice) it crashes with the above mentioned error.

After some surfing I came across an article in the Reporting Services newsgroup where one MattiasT had the same problem and he found the conditions for it to happen.

1. You are using a table.
2. The HideDuplicates property is used on a detail cell in this table.
3. The table contains a subreport.

And sure enough... when I remove the HideDuplicates the report generates just fine.

Unfortunately there seems to be no solution for the time being, except for changing the way you create the report because having all 3 conditions is a prerequisite.

TempDB and missing Device

Just had a nice little incident :-s

One of our development servers was used for tests with a SAN disk. They moved our TempDB to the SAN and after the tests removed the SAN disks. No problem, but they forgot to move the TempDB location to the local disks. Result? No more SQL Server :-x

In order to get it back up we did the following:
Start SQL Server from the command prompt with the following syntax
sqlserver -f -c -T3609 -T4022

-f: minimal configuration mode
-c: not as a service (console mode so you can follow the nice little thingies SQL Server does)
-T3609: skip tempdb creation at startup
-T4022: bypasses automatically started procedures

Then we updated sysdatabases and sysaltfiles (I know it's hardcore) with the correct file locations.

Restart SQL Server and GO GO GO

The Daily WTF (What The F*ck)

For those of you that don't know the site, this is really one you should visit every day ;-)
It's a website that gathers programming stupidity on a daily basis.

Connected Systems Developer Competition - Win $50,000 USD

If you are feeling very lucky ;)
Check out this link.

Mindi Abair

Heard some nice music yesterday. I was chilling with some music on SmoothJazz when I heard a song by Mindi Abair called Make A Wish, very nice indeed! When I was all chilled out I switched to Radio Paradise which is really a very good radio station.

Check them out.

Week @ Home

Aaah, just got back from a little holiday :-s
It's good to be back but I liked it more being home ;-)

CHAR and NULL

CHAR fields always take up the space assigned to them. Now you may think that it wouldn't be the case if it was NULL because that is determined by a bitmask in the row data (this bit is always present in SQL Server 2000 even if your column is not nullable).

This was true in SQL Server 6.5 but not in SQL Server 2000. So if you really need a nullable CHAR field it may be better to define the field as VARCHAR.

Report Packs

If you are working with Reporting Services please don't forget to check here once in a while.
Microsoft provides some nice Report Packs from time to time.

Have fun

New computer - installed

Installed and man does it fly!
I am very pleased with the results although I had some trouble with XP64 and I had to install XP32 again :-(

Kimberly L. Tripp on developing for SQL Server

Check it out here

A bold statement: "Open source could damage the market"

Bob Hayward, senior vice president and chief research officer, Gartner Research Asia-Pacific, said that software developers could be discouraged from creating new software because of the multitude of open source software available for free. This is further driven by major vendors that are making their software available as open source.

Check out the complete article here

Software Patents... No no no

The EU has voted against "Software Patents".
Of the 732 people 648 voted no, a clear vote imho.

I suppose this will not be the end of the whole story...

New computer - update

Yes yes yes :)
It has arrived... only the CPU was out of stock so I have to wait a little bit :'( If all goes well it should arrive tomorrow.

I also decided to buy another case :-)

SAN Phase II

We got to talk with the people responsible for the SAN.
Seems our plans are quite useless, the layout of the disks has changed :-( Anyway, we decided on a new plan. Currently +200 disks are assigned to our development servers. We're waiting for the draft as proposed by them (considering our remarks of course).

Enterprise Library - June 2005 Release

Get it here

Microsoft Baseline Security Analyzer 2.0

Download it here

I assume you all know this tool?

Those dreadful 'Software Patents'

Check out this post by Clemens Vasters