How to create new SQL Server Instance

1. Run the full setup.exe. Ignore the version warning message is Service Pack is installed.
2. Add a new DB instance there
3. Upgrade to the current SP version

T-SQL Pivot / UnPivot Command

This is a very useful and efficient command for pivoting data.

E.g.

select * from dbo.tbl_BuildInformationField
pivot (
min(FieldValue)
for
FieldName in ([Name],[Message],StartTime,FinishTime)
) as p
where name = 'FirstTarget'

More information at:
http://www.unboxedsolutions.com/sean/archive/2004/08/30/302.aspx
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=PIVOTData

Also UNPIVOT is equally handy in certain situations:
http://msdn.microsoft.com/en-us/library/ms177410.aspx

Here is how to unpivot multiple columns:
http://pratchev.blogspot.com/2009/02/unpivoting-multiple-columns.html

Take Advantage of the Calcuated Field

Get most of the detail level calculation done using the Calcuated Field helps simplify the Layout design.

Fixing The Custom MDX Lost in Report Designer Problem

Recently I encountered a problem with the SSRS 2005 Report Designer. When I opened a report with custom MDX code and go the data tab. The dataset view screen automatically went to the design view and wiped out the custom MDX code. The problem started occuring when I uninstalled SQL 2005 Express edition and installed the Enterprise edition. I found a post on the Internet that talks about similar situation. (http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/fca63464-cffb-4208-99e4-c0a53c3686a0/)

Because of the issue mentioned in the post, the Report Designer couldn't parse the MDX code. It automatically went back to the design mode when that happends because that's the "Previouse State" of the data set.

Installing SQL Server 2005 SP2 fixed this problem.