Things I learned today: Calling a scalar UDF with EXEC

One thing I love about T-SQL is that every day I seem to find another way to use it that wasn't immediately obvious to me. I was reading the SQL Server Denali documentation and noticed that in the definition of the EXEC command that it said you could execute a stored procedure or scalar function using EXEC.

While I'd obviously used it to call a stored procedure, it had never dawned on me that you could use EXEC to call a scalar UDF, so I had to try it. Sure enough, it's obviously worked for a long time. Here's an example of a function and calling it as I would have in the past:

USE tempdb;
GO

CREATE FUNCTION dbo.SayHello(@WhoTo NVARCHAR(100))
RETURNS NVARCHAR(120)
AS
BEGIN
  RETURN N'Hello ' + @WhoTo;
END;
GO

SELECT dbo.SayHello(N'Greg');
GO

Notice that you can also call it like this:

DECLARE @ReturnValue NVARCHAR(120);

EXEC @ReturnValue = dbo.SayHello N'Greg';

 

SELECT @ReturnValue;

I was speaking with a member of the SQL Product Group last week and noting just how good the documentation has become in recent years. I continue to be impressed with it.

Setting default values for slicers in PowerPivot

I've been doing some work with PowerPivot and SharePoint/Excel Services this week. I wanted the user interface to have slicers for:

  • Year
  • Month
  • Day

But I wanted the slicer to be preselected for the current month. There is no property on the slicers to set a default value. I read a number of websites and the suggestion was to use VBA code to set the value. This works but if you want to have the VBA code run at workbook open, you have to create a macro-enabled workbook, and these are not supported by Excel Services.

So I seemed to be fresh out of luck. However, one of my Spanish colleagues José Quinto Zamora came to the rescue. All you need to do is to select the slicer filter value that you want as the default, before you save the workbook, and every time you open the workbook, the slicer value will be already selected. That's as good as a default value for me. Thanks José!

Hope this helps someone else.

Analysis Services – Usage Based Optimization – Unhelpful UI

When using the Usage Based Optimization wizard (or the Aggregation Design Wizard), on the screen that asks you to enter counts. There is a button that says "Count" but after using it, you might still be confronted by the message ""Estimated Count or Partition Count must be specified for cube objects that require counting". It's not overly helpful, given you have just asked it to count them itself.

If you see this, look through the list of dimensions and find any that have red squiggles under them. Then open each and find the attributes that also have squiggles and update those counts. Make sure that all squiggles disappear.

You should then be able to continue to the next screen.

Hope this helps someone.

Book Review: Microsoft SQL Server 2008 Analysis Services Unleashed

Yet another book that I started re-reading last week (but haven't finished again yet as it's so large) is Microsoft SQL Server 2008 Analysis Services Unleashed by Irina Gorbach, Alexander Berger and Edward Melomed.

This book has always left me with mixed feelings. The authors clearly offer expert level knowledge on the topics (as they were part of the development team for the product) but I struggle with the "readability" of this book. As an example, each time a concept is introduced, it is done so with accompanying XMLA code snippets, etc.I suspect that a lot of readers would be put off by the XMLA. Most people just don't think in XMLA.

The content of the book, however, is excellent. I normally tell classes that this is a great book to have as a reference on the product, but just not such a great book for reading from cover to cover. On re-reading it, that opinion hasn't changed.

If you're working with SSAS, you should have this book on the shelf for reference when required.

Recommended!

(And thanks to the publisher Sams for making it available in Kindle format)

Book Review: Fast Track to MDX

Another book that I re-read while travelling last week was Fast Track to MDX. I still think that it's the best book that I've seen for introducing the core concepts of MDX. My colleague Mark Whitehorn, along with Mosha Pasumansky and Robert Zare do an amazing job of building MDX knowledge throughout the book.

I had dinner with Mark in London a few years back and I was pestering him to update this book. The biggest limitation of the book is that it was written for SQL Server 2000 Analysis Services, yet it still provides an excellent introduction to MDX. The knowledge from the three authors and Mark's entertaining writing style make this still a "must-have" book for anyone learning MDX.

Highly recommended!

(And thank you to the publisher Springer for making it available in Kindle format).

 

Book Review: Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

I spent last week on campus in Redmond with the SQL Server Analysis Services Maestro program. It was great to have a chance to focus on SSAS for a week. As part of that, I did quite a bit of reading as I had quite a bit of travelling time. Ironically, I re-read a few books.

The first was Marco Russo, Alberto Ferrari and Chris Webb's book Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. I've often told BI classes that I've been teaching that this is a really good book and highly recommended. Re-reading it confirmed that. I thoroughly enjoyed reading it again.

I can't say that I agree with every conclusion that Marco, Alberto and Chris came to, but I really appreciated the tone of the book where they make direct recommendations about how to do things, rather than endlessly messing around with "it depends" types of comments. Often, readers just want an opinion on what to do, even if it's not the only opinion.

I'm not sure that the book is at the "Expert" level but it provides a very sound coverage of Analysis Services. There are only two areas where I would love to see some changes. First, while the AS material is great, the more they step into the relational engine aspects, the shakier the material gets. For example, they discuss that disk queue lengths greater than 2 are a problem. While that was an indication to consider back when we mostly worked with DAS based systems, in most SAN-based environments, this simply isn't correct advice.

Second, I hope the guys push the publisher (PackT Publishing) to make their book available in Kindle format. It was the only book that I took with me on this trip where I needed to carry the hard-copy book.

Regardless, it's a great book and should be on the shelf of anyone working with Analysis Services 2008. Highly recommended!

SQL Server 2008 R2 Cumulative Update 8 now available

CU8 is now available for SQL Server 2008 R2. You will find it here: http://support.microsoft.com/kb/2534352/en-us

It includes the following fixes:

VSTS bug number KB article number Description
726734 2522893 (http://support.microsoft.com/kb/2522893/ ) FIX: A backup operation on a SQL Server 2008 or SQL Server 2008 R2 database fails if you enable change tracking on this database
730658 2525665 (http://support.microsoft.com/kb/2525665/ ) FIX: SQL Server 2008 BIDS stops responding when you stop debugging a SSIS 2008 or a SSIS 2008 R2 package
639374 2525665 (http://support.microsoft.com/kb/2525665/ ) FIX: SQL Server 2008 BIDS stops responding when you stop debugging a SSIS 2008 or a SSIS 2008 R2 package
701471 2530913 (http://support.microsoft.com/kb/2530913/ ) FIX: "Error: 17066" occurs when you create a partitioned nonclustered index in SQL Server 2008 R2 if a parallel execution plan is used
708511 2535660 (http://support.microsoft.com/kb/2535660/ ) FIX: Role membership is synchronized even if the SynchronizeSecurity element is set to the SkipMembership value in SSAS 2008 or in SSAS 2008 R2
711297 2537467 (http://support.microsoft.com/kb/2537467/ ) FIX: You cannot use SQL Server Management Objects (SMO) to transfer data from a table in SQL Server 2008 or in SQL Server 2008 R2
713677 2539098 (http://support.microsoft.com/kb/2539098/ ) FIX: "Runtime error: the property DBPROP_MSMD_UPDATE_ISOLATION_LEVEL cannot be found" error when an application tries to create a connection to SSAS 2008 R2
702342 2539378 (http://support.microsoft.com/kb/2539378/ ) FIX: Replmerg.exe crashes when it enumerates changes to a statement in SQL Server if the stored procedure contains more than 4,000 characters
720633 2545989 (http://support.microsoft.com/kb/2545989/ ) FIX: "A time-out occurred while waiting for buffer latch" error when many transactions concurrently update a database in SQL Server 2008 R2 if the database uses the snapshot isolation level
721958 2547017 (http://support.microsoft.com/kb/2547017/ ) FIX: Instances of DAX functions that have no names incorrectly appear when you add a calculated member to a report that uses an SSAS 2008 R2 database as a data source
737837 2559662 (http://support.microsoft.com/kb/2559662/ ) FIX: Slow performance when you render a report that returns a long string of text in SSRS 2008 R2 if the text contains no carriage return characters
738049 2562753 (http://support.microsoft.com/kb/2562753/ ) FIX: Slow performance when an MDX query uses unary operators on parent/child hierarchy members in SSAS 2008 R2 if you define a scope calculation on these members and if the query accesses multiple dates in a time dimension
722165 2563216 (http://support.microsoft.com/kb/2563216/ ) FIX: Dynamic image does not work in a page header or footer in SSRS 2008 R2 if the value expression uses the Global!PageNumber variable
704879 2563924 (http://support.microsoft.com/kb/2563924/ ) FIX: "The incoming tabular data stream (TDS) protocol stream is incorrect" error when the SQLPutData function inserts the SQL_NULL_DATA value in a TVP in SQL Server 2008 R2

Review: Kindle (Amazon)

Since I've been posting book reviews, I've already had a few questions about the Kindle and what I think about it.

I have the 9" DX with 3G and love it. The 3G is so useful. You don't need to have a 3G provider account and it just seems to work almost everywhere I travel to (including even China). I'm not sure how Amazon make that work but it's great. I see a book, click to order it and moments later the book is on my Kindle.

The other big advantage is that it holds so many books. I've got a good library but it's at home and I'm travelling a lot. I now keep the books I use most on the Kindle instead and it's always with me. That's a huge advantage.

I find the 6" device screens too small. I'm sure I'm getting old and eyesight isn't what it used to be but technical books on 6" screens just don't work for me. A novel might be ok but not a technical book. The up-side of the 6" device is that I've seen people put them into their pockets. You won't do that with the 9" device unless you have really big pockets. It's more like an A4 sheet of paper.

I like the way that I can email a PDF to my Kindle and it's delivered through the 3G whispernet. There's a small charge but it's very useful and the options for controlling charging are great. While you can change font sizes, etc. for Kindle-format books, you can't do that for PDFs that you send to it.

The other reason I like the Kindle rather than say an iPad, is the electronic paper screen. I only charge my Kindle once every few weeks and the screen is a joy to read. It basically only draws power when you turn the page. By comparison, the iPad and other such devices have an active screen and I find them very hard on the eyes after a reasonably short time. By comparison, I can read the Kindle all day without an issue. Unlike the Kindle, active devices also need constant charging to be useful.

If you get a Kindle, you need a cover for it. I got the Belkin Neoprene cover from Amazon. It's excellent also.

The downside of the Kindle? You really will be buying your books from Amazon and not all titles are available in Kindle format. But I've found that availability is getting much better almost daily in the technical book space.

If you want to read old classic books as well, there are a *really* large number of them available in the Kindle store for free.

Overall, I couldn't imagine life without my Kindle now.

Book Review: Dreaming in Chinese – Deborah Fallows

Another book that I've just finished reading on the Kindle is Dreaming in Chinese by Deborah Fallows. I had purchased the hard cover edition a while back on the recommendation of colleague Ron Talmage but hadn't got to reading it. I was glad to then see the Kindle edition of the book appear. I purchased and read it and loved it.

Deborah spent three years recently living in Shanghai and has documented many of the struggles she had with coming to terms with learning Mandarin and with getting used to Chinese society. I imagine that part of the hassle she would have had was dealing with Shanghai-ese rather than the Mandarin that she would have learned before heading off to China but I could relate so well to so many things she spoke of.

The book is also quite funny. I particularly liked the part where Deborah described trying to talk to an employee (who was wearing a big sombrero) outside a Taco Bell restaurant. She wanted to know if they offered take-away food but her attempts to pronounce the words (even though she had the correct words) ranged from asking for a big hug, to discussing hail.

My favorite part was where she discussed Chinese names. Chinese people will often adopt English names when they move to an English-speaking country, and of course, English-speaking people will often adopt Chinese names when they move to a Chinese-speaking country. The most common way to do this is to try to find a set of Chinese words that are like a transliteration of your existing English name. But it's also important to try to find words that mean something sensible in Chinese, like "Harmony". Deborah made me laugh out loud when describing one of her young friend's boyfriend who decided to choose a name to ward off gui (or evil spirits). He chose the name Fendui. Unfortunately, while it sounded like his name, it directly translated to "pile of shit". Beautiful!

If you have any interest in understanding Chinese society, even if you don't want to tackle Mandarin, this book is a great read. Highly recommended! (10 out of 10)