Thursday, November 14, 2013

Power Pivot: unable to convert a value to the data type requested

I just ran into an (for me strange) issue when I was updating a Power Pivot workbook.
I had 2 columns in a table, let's say "One" and "Two", where One has integer values and Two string values. In my model I created a calculated column Three to concatenate these values. Because One and Two are actually redundant and never used on its own, I decided to delete those columns and save some space (the tables contained just over 2.2 million records).
So I started with updating my source view to concatenate the values in T-SQL, but when I refreshed the table in Power Pivot I got the error:
"Unable to convert a value to the data type requested in table x for column Three"

This error occured because I renamed the column in the view One and I already had that column present in my PP earlier (although I deleted it before opening the Table Properties). So my column One was previous INT, but would now be VARCHAR, however PP keeps this setting in mind although the column was deleted.

I found an answer on StackOverflow:
http://stackoverflow.com/questions/6981688/powerpivot-sql-query-fails-unable-to-convert-a-value-to-the-data-type-requeste

My solution was to recreate the column One in my PP and leave it empty (this would get the data type INT), and then add the new column One from the source in the Table Properties. This gives a warning that the column is already present in your workbook, but solves it by renaming the new column to "One 1" when refreshing the changes. After that you can delete the old column One and rename "One 1" to One and you're done!

So in short:

  1. Recreate the column (One) you deleted
  2. Then add the newly added column from your source with the same name
  3. PP renames the new column to "One 1"
  4. Delete the old column (One) again and rename "One 1" to One, or the desired name

Tuesday, November 5, 2013

SQL Rally Amsterdam 2013


Of course I would have loved to go to the PASS Summit this year, but unfortunately that wasn't going to happen according to my employer..
But then I heard that SQL Rally was coming to Amsterdam and saw my opportunity.
I read several posts from AllenBrent and PASS itself on the topic How to Convince Your Boss... I had my hotel reservation ready, my arguments lined up (with even some backups to negotiate).. But he just said: "Well, that sounds like a good idea, just book it! :-)
The only catch was that I was asked to talk about a few of the topics on a study night to some of my co-workers. Well, that wouldn't be too hard I guess!

I hope to see a lot of my SQLFamily (Twitter-)friends in Amsterdam in the next 3 days! Do grab on to me and we'll have a chat!

Wednesday, October 30, 2013

Suggested reading

I came across some nice articles and blog posts in the last weeks I wanted to share:
SQLRally Nordic and Amsterdam will be held next week, I believe you can still register.
I will be attending SQLRally Amsterdam, so I hope to see some of you over there!

Hopefully you can get somehting useful out of here!

@NickyvV

Friday, September 27, 2013

LastIndexOf in T-SQL: Get Filename out of Path

Just a quick post for a quick solution:

I'm trying to get some information about documents stored in a SQL Server 2005 database. The documents are stored in an nText field in a table (called archive), but metadata is stored in other fields and tables.
I have the original filepath, but I wanted to get only the filename (+extenstion).

So actually, I was looking for some sort of T-SQL equivalent of the LastIndexOf C#-function. I couldn't find it easily so I thought substrings and CHARINDEX will do.. :)
Filepath is the column that contains the original filepath in the beneath code example:

RIGHT(filepath, CHARINDEX('\',REVERSE(filepath))-1)
LastIndexOf in T-SQL



When filepath = 'C:\temp\new\1\document.doc' this returns 'document.doc'
It basically:

  • reverses the column filepath
  • searches for the first '\'-character (so actually the last)
  • and returns the part to the right of the '\'.


@NickyvV

Friday, August 30, 2013

Suggested Reading

I came across some nice articles and blog posts in the last weeks I wanted to share:
Hopefully you can get somehting useful out of here!

@NickyvV

Thursday, August 15, 2013

Suggested Reading

I came across some nice articles and blog posts in the last weeks I wanted to share:
Hopefully you can get somehting useful out of here!

You can also follow me on Twitter: @NickyvV

Thursday, June 20, 2013

Suggested reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

Hopefully you can get somehting useful out of the links!

You can also follow me on Twitter: @NickyvV

Wednesday, June 12, 2013

The slight difference between ISNULL and COALESCE

I ran into a little issue yesterday when I was solving an issue for a customer. An Slowly Changing Dimension was implemented for the Organization table and I was looking for the currently active records in that table. I'll explain the issue with the use of the well known AdventureWorks2012 database.

My goal is to select the currently active records in the [HumanResources].[EmployeeDepartmentHistory] table, so I take the next select statement:









This results in 0 rows returned. I'm stunned, because I saw a bunch of records which have a NULL for EndDate so I would expect at least those records to be returned.
I leave out the where clause and add a row so my output also shows the ISNULL(EndDate, GETDATE()) column as follows:









This results in the following output (abbreviated):





I now realize my GETDATE() is converted to a DATE instead of a DATETIME column. But why?
Well, you might say: it was designed that way... as BOL (ISNULL (Transact-SQL)) states:
Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.
Because my EndDate column is of type DATE, the GETDATE() function (inside the ISNULL) will be converted to DATE also. So my where clause will actually look like this:





which is the 'same' as the following, technically spoken it's not the same, because the ISNULL will output a DATE data type, but for explaining my point I will use this :




This will only result in output when you execute the query at exactly 00:00:00.000 hours, which will *never* happen in real time.
Instead of ISNULL I will use COALESCE (Transact-SQL) which, according to BOL,
returns the data type of expression with the highest Data Type Precedence (Transact-SQL)  If all expressions are nonnullable, the result is typed as nonnullable.
This will result in the DATETIME data type for the last expression when the EndDate column is null and returns the correct result set for my goal.



There also is a slight difference when using non-null parameters:
ISNULL and COALESCE though equivalent, can behave differently. An expression involving ISNULL with non-null parameters is considered to be NOT NULL, while expressions involving COALESCE with non-null parameters is considered to be NULL.
Hopefully you found this useful and will also never make the same mistake again... :-)

Monday, June 3, 2013

Suggested reading

I came across some nice articles and blog posts in the last weeks I wanted to share:

Hopefully you can get somehting useful out of the links!

You can also follow me on Twitter: @NickyvV

Monday, May 27, 2013

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.

Wednesday, May 22, 2013

Tuesday, February 12, 2013

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!

Thursday, February 7, 2013

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.

Featured Post

Governance & Administration - Tenant Settings: Visual Cues

With all the Fabric announcements in the last months, some of the Admin announcements might have slipped through. As you might know, the Adm...