[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:

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 😉

Posted in Programming | Tagged , | Leave a comment

[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:

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?

Posted in Programming | Tagged , , | Leave a comment

#SqlSaturdayDenmark Re-Cap

Finally able to sit down, in quiet peace and write about my experience this weekend. Since Friday morning, the last couple of days have been rather hectic.
It started out with me moderating an excellent session by Margarita Naumova (b|l) on Top 8 Reasons for Your Transaction Performance Problems in the PASS Summit Preview of 24 Hours of PASS. Rest of the day do normal work chores, only to set up the family for the weekend, so I could go with peace at mind.

Friday evening I attended the Speaker Dinner at Copenhagen Street Food, which was quite a unique experience. Although I didn’t find that the food matched the prices, there was a distinct buzz to the place, that I doubt will be found anywhere else (at least not in Copenhagen). The company was brilliant, and that was what mattered. I met a good handful of people I knew from Twitter in advance, so meeting face to face was long overdue.


Just home in time for som hours of sleep, before heading to the venue, early Saturday morning. Having dealt with all of the attendees, we sent them off to the first batches of sessions – enjoying a moment of tranquility. Lesson for next year: Reconsider speed pas.

During the morning hours there was only some minor issues that needed attention, so all in all I had good time to allow for preparation for my own session on MDX. Slides and Code examples are available from the SQLSaturday site. The session went well, at least according to my own sence, but I am hoping the attendees will be evaluating the session, if they attended. Evaluations are really important, in order to grow as a speaker.

After my session, things began to happen quite rapidly. Due to logistic challenges in the house hold, I had to pick up the #sqlbrew at home. I also had to be present, because the guy with the hotdog stand, expected me to be there. Kenneth M. Nielsen (b|l|t) allowed me to use his car, and there I was, for the first time with automatic drive. Maybe this could get ugly, with all the beers and all. Somewhere in my mind I began piecing together, the story for the Cops, if they should find me crashed with 400 bottles of beer… I must say, my left foot treated the brakes as clutch on the way out, to much amusement of pedestrians… But the beer arrived safely. After the full schedule of sessions was over, everyone gathered for the big sponsor raffle – great prices in the pool: Several drones, licenses, books etc.

In the end, I think everyone was happy – the volunteers and organisers had done as smashing job on creating a great event, yet another one. According to leaked info on session feedback, we have quite a high average rating – that is really exiting.

Late Saturday we sent everyone home with hotdogs and our own #sqlbrew



Posted in Community, Programming | Tagged , , | Leave a comment

SqlSaturday Cambridge, #SqlSat411, re-cap

Early Friday morning, I left Copenhagen for my second SQL Saturday in Cambridge. Two years ago I had a blast at same event, as documented here. My expectations were high, but I was completely blown away by the work and preparation that had gone into the event. I apologize in advance to those who are attending the SQL Saturday in Copenhagen next weekend, we cannot provide the same level of experience I am afraid. A huge effort was made by the team, so hat tip to them!

After arriving at the DoubleTree, down town Cambridge I hooked up with Johan L. Brattås (b|l|t) and Boris Hristov (b|l|t) to walk the streets and see the impressive sights (mostly colleges). Spending the afternoon in good company was topped of with the most amazing dinner settings at the Gonville & Caius College, where a team of servants had a dinner lined up, with millimeter precision.

Up early on Saturday  morning, to get to the venue, I even had to wait for the hotel to have breakfast ready – I knew I had to have something to eat, because normally the lunch bags at these events are…erhh…scanty, to last for a whole day at least. Speaking of the venue, I also need to mention that it was absolutely fabulous for events such as this – kudos to the team for snagging this. On another note, it was not until I saw the picture on the wall, that I made the connection – it has Maersk written all over it :)

I didn’t get to see much of the other sessions, as I was preparing for my own. Introduction to MDX was executed way better than Scaling out Analysis Services, but I guess you learn as you go – apologies to the two (2) attendees for the latter one – I hope you enjoyed the ten minute early break 😉

After the almost unbearable exitement at the raffle – volunteers, organisers and speakers went punting, as depicted below:

If ever in Cambridge, you should definately do this – get a guided tour, as they know all kinds of odd stories that add to the image of Cambridge. Cambridge, where I certainly hope I have not set foot for the last time. Well done Mark and Team.

Posted in Community, Programming | Tagged , , | Leave a comment

Extracting SSAS Database(s) XMLA w/ PowerShell

For some time, we have been trying to get Informatica’s Metadata Manager to extract information from our SSIS, SSRS and SSAS instances in production. However, we were facing issues with credentials and service account clashes that lead us to just extract the Metadata and deploy it on a server where we had full control.

The easist wasy of doing this, was to create a PowerShell script that extracts, saves and deploys the XMLA from the databases in question. So we would just be looping through a database collection on a number of servers, in order to get the required information.

Basically there are a few steps you need to do, in order to fit this script into your environment. Mind the list of sources, multiple servers can be added, although this has not been tested for name clashing on target box.

If you don’t need a physical file, for the archive, this step can obviously be skipped.

Finally, you may have to clear the database collection on the Target server, before running the script. This was done as a one off. This step is also diabled, commented out, in the script above. Less distruction on account of copy/paste.

Obviously if the cubes needs to be processed at the Target location, this needs to be added as well. We were just after the Metadata, hence no processing was needed.

Posted in Programming | Tagged , , | Leave a comment