SQL03070: This statement is not recognized in this context

I just ran into the following problem:
when trying to build a database project in my VS2010 solution I got this error:

SQL03070: This statement is not recognized in this context.

After searching the first hit in Google already solved the problem:
The reason for this error is the following:

The project system expects the stored procedure as a create statement only. The additional statements to drop if existing are not necessary within the project system. Project deployment takes care of detecting if the sproc already exists and if it needs to be updated.

To resolve this error

• Simply remove the additional statements other then your create SP, Function etc.

OR

• Exclude the file from build. Right Click on your file in Solution Explorer, Click Properties > Build Action > Not in Build

Credits to: Prashant Acharya.

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.