Install Oracle Drive on Microsoft OS

Here is what I found out:

"
I ran into the same issue and this is how I resolved it. Go to http://www.oracle.com/technology/software/products/database/oracle10g/htdocs/winsoft.html. Save Oracle Database 10g Release 1 (10.1.0.2) into your dard drive. In the saved directory under "Disk1" you will find an install directory. Under install, click on setup.exe. This will download the latest Oracle Universal Installer in your machine. This will only update your Oracle Installer to the latest version but will not install the latest database. Hope this helps. "

More info on the oracle forum
http://forums.oracle.com/forums/thread.jspa?threadID=285669

SSAS Process Error

I encountered an error when trying to process a SSAS cube. I haven't found out the root cause of it yet. But turning off the error reporting allowed me to get around it and get the cube processed. Details at:
http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/971e3e6b-a1a7-4a19-95af-78ef21d11678/

SQL Server Linked Server Windows Authentication Config

There is a good blog post about Winodws Authentication config for double-hop linked server.
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

If Windows Authentication setup is too much trouble, an alternative would be using SQL Server Authentication.
http://itknowledgeexchange.techtarget.com/itanswers/linked-server-error-message-on-a-sql-server-2005-machine/

Performance Improvement on Recursive CTE

One big finding for today is that when using recursive CTE, the leaner the recursive member table is the better. This basically means getting only the fields that are necessary for the recursive CTE. Any other fields such as Tile, Description are to be pulled after the recursive CTE. It also means doing the recursive as early as possible in the big overall query.

This practise brought a SQL query running time from 30 seconds to 1 second. Huge, huge difference!

Convert UTC Time to Local Time

To convert a UTC time to local time, we first find out the difference between the UTC and local time by getting the current UTC time and local time.

DECLARE @UTCtoLocalTime AS INT
Set @UTCtoLocalTime = DATEDIFF(hour, GETUTCDATE(), GETDATE())

Then we just need to add the difference back to the UTC time to get the local time.
DateAdd(hour, @UTCtoLocalTime, [UTC TIME])

The benifit of this approach is that the US daylight saving is considered.

Multi-Selection Parameter Reset to Blank

I encountered a situation where a multi-selection parameter is reset to blank sometimes by the Reporting Services.

Here is the reason that the Feature parameter was reset to blank or giving out error message:

Somehow the Reporting Service re-evaluates and invalidates the parameter values when a parameter is dynamically generated based on other parameter. In our case, Feature list is dynamically generated based on Primary Workstream selection. The work around is to use a table variable instead of straight SQL query to generate the dataset that populates the parameter values. For some reason the Reporting Services doesn’t re-evaluate the values if a table variable is used.

After some digging, I found a work around:
http://stackoverflow.com/questions/684250/ssrs-asp-net-reportviewer-parameters-reset-to-default-when-clicking-view-report

The following query is used:

DECLARE @FeatureList TABLE (Feature NVARCHAR(256))
INSERT @FeatureList
SELECT DISTINCT Cobalt_Common_Feature AS Feature FROM dbo.[Work Item]
WHERE Cobalt_Common_Feature IS NOT NULL
AND (@PrimaryWorkStream='_All Workstreams' OR Cobalt_Common_PrimaryWorkstream=@PrimaryWorkStream)
UNION SELECT 'ZZZ Unknown' AS Feature

SELECT * FROM @FeatureList ORDER BY Feature

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