Simons SQL Blog

I've been using Excel 2007 for about a month now and am I am strarting to love it.

Ok so I suffered from the "Where the hell do I find option x" syndrome due to the new ribbon bar, but, as I had heard before, this is a short term issue, you will eventually understand how the options are configured and be able to find things very easily.

This brings me to my first observation, the Ribbon bar. The way the ribbon bar is configured does provide a much greater surface area for items, this means that most items are only 2 clicks away. More importantly I have found many of the options I use frequently are now accessible in 1 click compared to 5 or more before. This includes, "Wrap text" , vertical text position and standard formatting.

It is obvious that the standard office toolbars haven't changed much over the years and the development of the Ribbon has been influenced by feedback from users so that the most used items are easily accessible.

The second major observation is formatting. This has taken a huge leap and is just great, especially when dealling with large sets of data, where the addition of vsiuals makes the data stand out from the page without the user having to read every value.

Whats more I am someone that used conditional formatting extensively. Previously this was stuck deep away in a menu, it is now an option directly on the ribbon and provides access to builtin formatting and also a means by which you can customise the formatting. If you've been to any demos of Office 2007 or even BI in SQL Server 2005 you will have seen the data bars, color scales and icon sets.

Finally functions, I haven't looked at depth into what new functions are available I just know that 2 that have appeared are COUNTIFS and SUMIFS. These allow you to specify multiple criteria for suming and counting a range.

I've often used data reader destinations as means of dumping data from a data flow, because it doesn't need configuring. However I always noticed that the package would hang for a while before completing.

I always put that down to some ADO.Net issue and just lived with it.

I am currently siting next to Tony Rogerson (who is badgering me about putting a link in this post to his blog), at a Technet event where Peter Blackburn is presenting on Advanced Reporting Services.

He's just covered using SSIS as a source of for a report. TO do this you have to use a data reader destination. In doing so he highlighted that the data reader is expecting something to be reading its data, when running the SSIS package in the IDE nothing is doing that and so the data reader waits until it times out. This timeout is defined as a property on the data reader destination.

What you can do is to set the timeout so that it only waits a few seconds or even milliseconds.

These events are always great for picking up great tips like this (even if you I do have to endure sitting next to Tony ;)

Many people often ask how they can us a database in SQL 2000 that they have attached or restored to SQL2005.

Unfortunately this is a one way street, once upgraded the only way of using a database in a previous version is to copy the schema and data manually. You can't restore or attach a database even if the compatibility has been kept at the previous version.

Paul Randal has sort of blogged about this, also highlighting the fact that you can't even restore a database to a version with a lower build, so that includes hotfixes and service packs 

Graham has also raised the question on his blog http://sqlblogcasts.com/blogs/grahamc/archive/2007/05/21/sql-server-2005-database-back-to-sql-server-2000.aspx

There is a little known feature of SQL Server 2005 is that when you start SQL Server in single user mode local administrators of the server have sysadmin access to the sql server.

You may feel this a security concern, but if a user can get local admin of your server, and restart your service then having sysadmin access to your SQL Server is the least of your problems.

So next time you forget your sa password and restart your server in single user mode, login using sqlcmd and reset the password. And away you go.

I've just spent a morning trying to figure out why my average measure doesn't average but sums.

I guess I should have googled straight away. In doing so I found this forum post http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=548837&SiteID=17

Christian also mentions this behaviour but only for the other semi additve measures, It does also apply to the AverageOfChildren measure as well http://blogs.conchango.com/christianwade/archive/2005/04/07/1252.aspx

To get wrong this I created a simple calculated measure by going into the calculations tab. Clicking New Calculated Member, giving it a name and then putting in an expression like,

Where the [Sum Of Ping Duration] is the sum of the measure I want the average for and Pingstat Count is the number of instances of the measure.

I don't think you need to put in some divide by 0 handling because if the count is 0 then the duration will be empty and so won't be calculated.

Happy to be told the best way to do this.

Update: I've just read Mosha's post on averages which is much more detailed and a very good read.

No i'm not talking about a new Harry Enfield sketch about a posh bloke that's a bit thick, I 'm talking about tables in a warehouse.

Many people insist on naming tables DimCustomer, Dim Product etc and FactOrderLine. What is the purpose of this, especially in a UDM model where tables can easily be dimensions and facts. What happens then, should it be DimFactOrder or should it be FactDimOrder, sounds like something from a Chinese menu.

I for one don't like the idea of naming objects this way, if you don't know what is in the Customer table then you shouldn't be touching it.

I wonder what silly names one could end up with.

DimLight, DimPle....

I am doing my first speaking event outside of the UK. The guys organising the Irish Microsoft Technology Conference kindly invited me to speak.

Following on from and combing some previous sessions I've done I will talking about Service Broker and how it can be used to achieve asynchronous processing to greatly improve your systems.

The conference can be found here http://imtc.firstport.ie/

If you run this sql on a sql 2005 box then you will see which indexes aren't used in queries compared with the number of times they are updated.

select object_name(s.object_id) , *

from sys.dm_db_index_usage_stats s

join sys.indexes i on i.index_id = s.index_id

and s.object_id = i.object_id

where s.database_id = db_id()

order by user_seeks, user_scans, user_lookups

Thanks to Donald for pointing out this great training onhow to do presentations.

My presentations will never be the same again

I remember seeing a very early CTP of Yukon and the new DTS. I was very exceited to see a parallel property on the loop container which would allow the contents of the container to be executed in parallel.

If you've used the RTM on Yukon you will know that there is no such property. Unfortunately it was too complex to be implement.

However you can still get parallel execution by using multiple tasks or by having multiple paths in a data flow.

For more details read this post

http://blogs.msdn.com/sqlperf/archive/2007/05/11/implement-parallel-execution-in-ssis.aspx