Sql-Server

I really liked the addition of the WITH EXECUTE AS clause when defining stored procedures and functions, to change the execution context, just for the duration of the stored procedure or function. For example:

CREATE PROC SomeSchema.SomeProc

WITH EXECUTE AS USER = ‘Fred’

AS

… 

I’d also used the EXEC AS clause to temporarily change my execution context during testing. For example:

EXEC AS USER = ‘Fred’;

-- Try some code here while running as Fred

2011-08-13

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:

2011-08-13

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.

2011-08-11

Ola has updated his index optimization scripts again. The update relates to logging to a table.

You’ll find the scripts here: http://ola.hallengren.com/

2011-07-19

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.

2011-07-06

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.

2011-06-21

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.

2011-06-21

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.

2011-06-21

CU8 is now available for SQL Server 2008 R2.

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

2011-06-21

I’m loving my Kindle. I seem to be getting through books so much faster. One book that I recently read was Book Review: Microsoft PowerPivot for Excel 2010: Give Your Data Meaning by Marco Russo and Alberto Ferrari.

I really liked this book. It provided quite good coverage of PowerPivot use in Excel 2010 and also spent some time mapping the use of PowerPivot to organizational requirements. Marco and Alberto provided more coverage of DAX (Data Analysis Expressions) than I have seen anywhere else, particularly in relation to the CALCULATE verb.

2011-06-01