Suggested reading

I came across some nice articles and blog posts this week I wanted to share:

• The (unintended) network effect of Excel by @PowerPivotPro
• Junior DBA interview questions by @SQLRockstar
• Comments and descriptions in DAX by @TechniTrain
• How far can you push tabular? by @MarcoRus
• The emergence of Silicon Valley (documentary) by @AlexanderNL (+Alexander Klöpping)

Hopefully you can get somehting useful (or funny) out of the articles!

You can also follow me on Twitter: @NickyvV

Database [databasename] cannot be upgraded because it is read-only or has read-only files

Today I was studying for my MCSA SQL Server 2012, for 70-461 (Querying SQL Server 2012) and I ran into an issue while going through Chapter 6: Querying Full-Text Data, Lesson 1, Excercise 2.

For this exercise I installed Full-Text Search on SQL Server 2012 and later I needed to make sure the Semantic Language Statistics Database was installed. This was not the case, so I tried to follow the necessary steps.

While I was trying to attach the database with the beneath script I was getting an error message of the following type:

CREATE DATABASE semanticsdb ON
(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb.mdf'),
(FILENAME = 'C:\Program Files\Microsoft Semantic Language Database\semanticsdb_log.ldf')
FOR ATTACH;

Msg 3415, Level 16, State 2, Line 1Database 'semanticsdb' cannot be upgraded because it is read-only, has read-only files or the user does not have permissions to modify some of the files. Make the database or files writeable, and rerun recovery.

As the error message was saying, I checked the permissions of the database files and ensured they were not read-only. Also, the service user account running the SQL Server instance had full access on the files..

I was about to give Everyone full access to the folder when it struck me: I tried starting the SQL Server Management Studio in Administrator mode. When I executed the script again I got a lovely message saying:

Converting database 'semanticsdb' from version 693 to the current version 706.Database 'semanticsdb' running the upgrade step from version 693 to version 694.Database 'semanticsdb' running the upgrade step...

Worked like a charm for me!

Logical Query Processing

Well, that's been a while since my last post.. A new year, new chances!

Opposed to physical query processing which follows the order of the statements:
SELECT .. FROM .. WHERE.. GROUP BY ..)
logical query processing processes the statements in the following order:
FROM .. WHERE .. GROUP BY .. SELECT ..

Last week I saw this excellent poster from (who else then) Itzik Ben Gan, writing down the phases of logical query processing in T-SQL in a flow diagram.

You can download the original poster here.

Power View for Multidimensional Models - Preview

Yesterday the public CTP on Power View for Multidimensional has been released by Microsoft!

In SQL Server 2012 RTM/SP1, Power View can consume data from tabular models, however multidimensional models (a.k.a. “cubes”) were not supported. The Power View for Multidimensional Models CTP addresses this key limitation.  Now Power View users can connect to both tabular BI Semantic Model AND multidimensional BI Semantic Model (cubes).  This is achieved through native support for Data Analysis Expressions (DAX) in Analysis Services Multidimensional models, ensuring optimal performance and functionality.

The SQL Server BI team blogged about it here. You can also directly download the Microsoft SQL Server 2012 With Power View For Multidimensional Models CTP.

If you have any issues or suggestions you can provide feedback via MS Connect.

Error on opening a database connection in SSMS

Yesterday I ran into an issue with SQL Server Management Studio (2012). I was using it on my laptop with Windows 7 installed.

Whenever I wanted to connect to a database I got the following pop-up and nothing could be done with the database I connected to. Opening databases or security also gave me the same pop-up with an error in Microsoft.VisualStudio.Platform.WindowManagement:

After a thorough search I came to an MSDN message that suggested the following:

• Try installing the following Microsoft Visual Studio 2010 Shell (Isolated) Redistributable Package
• Then apply SP1 for Visual Studio.

Installing the redistributable already failed with me so I searched through my recently installed programs to see what has changed the last days.. This monday I installed Vault Client for source control, and after uninstalling this the problem had disappeared. I still don't know why this was happening though..

SQL Server Days 2012

In a few weeks I will be attending SQL Server Days 2012 in Schelle, Belgium.

I already saw lots of good sessions and speakers pass by in the agenda: Jen Stirrup on Mobile BI, Chriss Webb on SSAS, Koen Verbeeck on CDC and SSIS..

Hopefully I can meet up with some new SQL-friends and add some to my SQLFamily :)

See you @ SQL Server Days.

Power Pivot Field List vs. PivotTable Field List

Something strange happened this week when I was giving a PowerPivot training.
The PowerPivot Field list disappeared and the PivotTable Field List came back. This happened after a few minutes after I had made a PowerPivot table in a new document. It seemed to happen just in a moment, not after any particular action. Saving, closing and reopening the document solved the problem.

This triggered me to look at the autosave options. When you create a new document and autosave is on, make sure it saves it with the right extension. In this case, the format of autosave was on OpenDocument Spreadsheet (.ods). This caused the PowerPivot Field list to disappear and only the PivotTable field list was available.

This can be undone when the document type of autosave is on Excel Workbook.
The autosave options can be changed under File > Options > Save.