[Issue] – SQL Server Installer

Helping out a collegue I was asked to install features from SQL Server 2008R2 on an existing box. That sounded like something I could do with both hands tied and a blindfold on. But Boy was I in for a surprise.

The server, which had some features (SSAS mainly) from SQL Server 2012 SP2 installed, was to run SSIS packages from a SQL Server 2008R2 instance. Don’t ask why, just accept it 🙂

Initially I downloaded an iso file from MSDN, that would contain SQL Server 2008R2. Btw. if you ever need to mount an iso and do not have the feature in the OS, like in my example Windows 2008, visit Slysoft and download the Virtual Clone Drive (Free).
Running the installation from that media resultet in an unusual error, one which I haven’t seen before – and to be honest, I don’t get why this would ever be a problem. The error message tells you, that the chached file is missing and you have to copy the original, and rename it in the process, to C:\Windows\Install (which is a hidden folder). Let’s repeat that, a cached file is missing… WTF!

SQL Server 2012 Install ErrorNote that the cached file in question is related to the SQL Server 2012 installation, but this was SQL Server 2008R2…

Going by the link described in the error message, I am directed to a page that describes this error in detail, for a good deal of previous versions of SQL Server (2005, 2008, 2008R2 & 2012). The KB provides a script that lets you identify which files are actually missing. The output format of this script is questionable – some would say chatty. So to get the details that actually matter, a bit of regular expression comes in handy. This may well be one of the most simple regular expressions I have ever written, and I think everyone can understand without further introduction:

Copy ".+

This little beauty will pull out all the rows that begin with “Copy ” followed by any number of characters. The result from this query, can be pasted directly into a txt file, which then can be renamed to have .bat as postfix. Please note that you may need to do additional cleansing of the paths in the txt file before running it and you need the install media mounted on the box in question. One final note is to run a cmd prompt in elevated mode, and then kick off the bat job. This will copy the missing files to the Windows\Install folder.

Going through the output of the bat job, I can verify that all files have been copied succesfully. So you can imagine the surprise as I ran the installer once again and received below error message:

SQL Server ErrorOhhhkay… As the aware reader you are, you will by now have noticed the reference to same KB as above.

Alrighty ThenI had to find the file on the SQL Server install media, copy the file to my desktop, rename it and then copy/move that file into the Windows\Install directory.

Getting back in installation mode, I kicked off the installation once more, selecting all the required features (no by default option) and then got the install going. Only to be met by a new error message:

SQL Server ErrorIf you see keyboards flying, you know why…

And yes, computer screens used to look like that 😉

Loading

[Issue] – The Mystery of the Hidden Column

Yesterday I was trying to setup a new Power BI dashboard for our SQL Server Analysis Services logging data.
We are running traces on most of our servers, in order to keep up with what is going on – this mainly because all other access to the boxes is restricted.

Now, having the data in a SQL Server database, the connection to Power BI Desktop is of course supported out of the box, no issues here what-so-ever, one should think. I however, came across creating a particular view on top of a table, the just didn’t compute in the Power BI Desktop Designer. As depicted below, the view in question would just not be possible to load/edit. Microsoft is looking into this.

View Error

I managed to get around the view issue by persiting the data in another table, consolidating and filtering to only have relevant data there.

Loading the data, from the table into Power BI Desktop Designer is easy, you just point to the server, pick the table and you are presented with two options, either to load or to edit. In this case I opted to load the data. I started playing around with the data and quickly discovered that I was missing out on some attributes in the data. For one thing I needed to convert my date to day, month and year (would have loved some basic week functionality as well, but maybe later eh?).
This functionality is actually build into the tool (Power BI Desktop Designer), so you can generate those extra columns by selecting “Add column” in the ribbon, select the desired date (if you have more) and choose from one of the many options in the “date convert picker”

Month Date Selector

– apologies for the screenshot in Danish.

The above selection will yield the Column you see in same screenshot, named [Month No], and as you can see, it returns a number. This is not very end user friendly, so a little convertion is in order.
The normal Excel IF(test, success, fail) does not work in the Power BI desktop Designer, we need to write it a little bit differently – this is how I convert month number into text:

if Text.Range([Month No],0,1) = "1" then "Jan" else 
if Text.Range([Month No],0,1) = "2" then "Feb" else 
if Text.Range([Month No],0,1) = "3" then "Mar" else 
if Text.Range([Month No],0,1) = "4" then "Apr" else 
if Text.Range([Month No],0,1) = "5" then "May" else 
if Text.Range([Month No],0,1) = "6" then "Jun" else 
if Text.Range([Month No],0,1) = "7" then "Jul" else 
if Text.Range([Month No],0,1) = "8" then "Aug" else 
if Text.Range([Month No],0,1) = "9" then "Sep" else 
if Text.Range([Month No],0,2) = "10" then "Oct" else 
if Text.Range([Month No],0,2) = "11" then "Nov" else 
if Text.Range([Month No],0,2) = "12" then "Dec" else 
"Unkn"

as a custom column:

User Defined Column MonthPlease note the little green check mark indicating everything’s Dandy (b|l|t)… No not that Dandy….

But to my great surprise, the column does not show up in the data source view (or whatever they call it in Power BI Desktop Designer) – Look for [Month Name] as I renamed the column from [Month] in a later step.

Data Source View
WTH is my column?

Loading

Installing MS SQL Denali CTP3 failed

While I was on vacation, the news of the Denali CTP3 release (ref) reached me. When I got back to work I immediately downloaded the package. From there on it would be a smooth ride through the wizard, or at least so I imagined it. Only once before have I met problems installing a version of SQL Server on a machine, that was when I tried to install SQL Server 2005 Dev Ed. on an ASRock HT330. That didn’t work out too well. That aside, I wasn’t expecting any problems, since I was installing the CTP3 on a brand new stationary machine (Windows 7 Ent.) with lots of power.

Power
Current desktop

This isn’t a power issue though, going through the steps of the wizard, I’ll explain my actions and the outcome.

First the welcome screen, we you can view all kinds of requirements and documentation.

Welcomescreen

Selecting ‘Install‘ to proceed with the installation, and choosing the ‘New SQL Server stand alone…‘ option

Install

Click ‘OK’ on the Setup Support Rules dialog

Setup Support Rules

Click ‘Next’ on the Product Update dialog

Product Updates

The Install Setup Files dialog will show up (not clicking anything here)

Installing Files

Going through the Setup Support Rules, inspecting the Firewall warning. Since this is for local testing only this issue is not solved and should cause any problems.

Setup Support Rules

The details of the Windows Firewall warning

Firewall warning

Clicking ‘Next’ brings us to the Product Key dialog, where we will select ‘Evaluation’ as free edition

Product Key

Onwards to the License Terms dialog, where we will accept and choose to send any information to Microsoft

License Terms

On to features selection on the Setup Role dialog, where we will select ‘All Features With Defaults’

Setup Role

A detailed view awaits on the Feature Selection dialog, where ‘All Features With Defaults’ appears to not include ‘Reporting Services Add-in for Sharepoint products’ and ‘Master Data Services’, I wonder why…

Feature Selection

Now, the Installation Rules dialog is where I can’t get any further…

Install Issue

Errormessage

I’m stuck and can’t get any further. There was SQL Server 2005 client tools installed, but I uninstalled those the first time I was hit by the issue. I’ve tried this: http://bit.ly/pyt7mm and suggestions like it, w/out any effect.  I’ve tried to tweet about the issue and was by guided to: http://cot.ag/9OUV5A and http://cot.ag/qnzZid by @MicrosoftBI. None of those two provided any solution to the issue at hand.

How to get past this rather frustrating installation rule? And why does SQL Server 2005 Express tools block Denali CTP3?

Just as documentation, no SQL Server Express installed, allthough ‘Microsoft SQL Server “Denali” CTP3 Setup (English)‘ is listed twice…:

Programs and Features

There is a forum post on the issue: http://bit.ly/qjLTsJ

UPDATE:

I’ve tried to run the installer with the parameter /skpirules=Sql2005SsmsExpressFacet as show in picture, but the error persists:Command prompt

Error page #2

Loading