March 2007 - Posts

I am reviewing my interview questions to try and make them more exhaustive.

I intially started off interviewing with the view that the interviewee should be able to stand up on there own. I'm not going to give them anything. If they can't think of the right answer then mustn't know it. I never wanted to give a interviewee the answer as I felt that defeated the objective.

This I have realised is a bit drastic and you have to find a middle ground. Obviously the more non-leading questions you can give and get the answers you want the better. but where would you start.

For example would you ask "How do you manage replication?" or would you ask "What is RMO/Remplication Monitor etc?" An other example "How do you improve the performance of a query?" or "What is an index?"

I do find the open questions lead on to a few or more more detailed questions. If the interviewee hasn't made that connection and you have to make the step to the more leading questions then you know the level they are at.



Posted by simonsabin | 2 comment(s)

The one thing I learnt when I was at the MVP summit a few weeks ago was that hotfixes are always cumulative, in other words you get all the other hotfixes that have been made prior to your hot fix.

The reason for this is that there is one SQL Server code branch that is used for releasing code. The code changes for the hotfix are applied to the branch and the hotfix package is then built.

You may have noticed that the GDR (General Distribution Release in between a hotfix and an SP) for the Maintenance plan cleanup bug made changes to all sorts of areas including RS, AS not just the tools. This is because the GDR is just a hotfix that is made public.

Tony has blogged about the time it takes to apply the latest SQL Hotfix SQL 2005 SP2 hot fix from windows update takes over 10 minutes to run! This is probably because the cut off for SP2 was quite a while ago and so hotfixes have been piling up so the hot fix actuall contains lots of previous hotfixes.

Be warned. Make sure you have a well structured testing process for applying hotfixes as well as service packs


We are holding the next UK SQL User group meeting in Central London on the 26th April from 6pm - 9pm (later if you fancy a drink)

The eveneing will have a SQL event and BI event running side by side with 8 presenters and loads of giveaways including t-shirts, books and USB keys.

If you want to learn more about Performance point or SSIS in the real world register for the BI event SQL Usergroup meeting - Performance Point, SSIS and more cool BI stuff

Or for sessions on managing change and using SMO from TSQL, Arrays in TSQL and other great SQL topics register for the SQL Event. Change, Agile Development and SQL Server Tips and Tricks

We are restricted to only 30 people per event so make sure you register soon.


Thank you to those people that informed me that my taskpad report was incorrectly reporting the backups times against the wrong type of backup.

I have now fixed the report and have also included the backup types that are supported by SQL 2005.

You can downlaod the new version of the taskpad custom report

You will need SQL Server 2005 SP2 to use the custom report.


If you are using Event Notifications or capturing events in Profiler you undoubtedly have noticed that MS have used the same columns (xml elements) for storing different data for each event. For example there is an element called "integer data". You may ask what is stored in here cos it ain't obvious from the name of the element.

Help is at hand but it's not obvious. I spent a while digging through BOL and found the pages you need.

The top level page is called "SQL Server Event Class Reference" and can be found with thi BOL link ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/0f0fe567-e115-4ace-b63c-73dc3428c0f6.htm

This page provides a link to all the pages that detail what all the elements mean for each event class.

In addition you can use the index and go to the pages directly. The name of the page is "<event name> event class" i.e.

"Exception event class"  with a BOL link of ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5a45dfa7-51fc-420c-96f6-ce2a1f61b684.htm


Microsoft are in the process of retiring GotDotNet and replacing it with CodePlex.

CodePlex is a shared source website which allows you to upload projects and give access to other people to view/maintain.

The great feature is that it is using Team Foundation Server. This means that any one using Visual Studio with a Team suite client license can have full access to the Team foundation server. This is all done over HTTP.

A big step has been made by the SQL Server team in putting the samples on CodePlex (Thanks Ed) Whilst you can't have access to modify the source you can access all the component parts of the samples. This means you don't have to download all the samples to get the sample you want, and even better you don't have to download a complete sample you can access individual files, i.e. scripts, read me files, word documents etc.

So go and have a look

This site also acts as a starting point for looking for other SQL Samples provided by the community.

Posted by simonsabin | with no comments

Paul Randal recently informed me of a new white paper on online index operations. The whitepaper can be found here Online Index White Paper.

This is one of many whitepapers that have been published lately by Bob

Internals Troubleshooting




SMO is a great feature of SQL Server 2005. It is a rich API for managing SQL Server and as with its predecssor DMO the SQL Server tools are built on top of it.

As SMO is a .Net managed library many people want to reference SMO in a SQL CLR function or stored procedure. However SMO is one library that cannot be hosted within SQL Server (See Bob's post for more details

If you want to use SMO functionality there is a way you can achieve this from within TSQL and thats with the use of Service Broker.

Service Broker allows us to submit a request to a service in TSQL and waits for a response, an external service is then able to recieve the request, process it and reply with the response. The TSQL receives the response and can do what it needs to do with it. Whilst Service Broker is an asynchronous process you can make an end to end solution that is synchronous by having the calling process wait for a response.

The beauty of this is that your external service can be anything from a VB6 application to a .Net application, it just needs to be able to read a message from a queue and send a message back. Both of which are just TSQL statements.

I will shortly be posting a demo of this in action.

Posted by simonsabin | 1 comment(s)

Due to a regression bug introduced in SP2 some of the maintenance plan tasks that can operate on multiple databases may not perform as expected.

If a maintenance plan component uses TSQL of this nature

USE [DBFred]
USE [DBSimon]

To handle errors in the process the batch is split into multiple commands (split by the GO keyword) so that if one fails the others can still be run.

Because a new the server connection is being made for each statement the context of the connection is always the database specified in the connection string. Rather than the one specified in the previous USE statement.

I have found a partial workaround for the executesql task (the maintenance plan one), which is to open the maintenance plan in BI Studio and change the properties of the connection to not reset the connection.

However this does not work for the Database Integrity component.

I therefore suggest anyone that has upgraded to SP2 and is using the Database Integrity component to change and generate the SQL that would have been used and use either the normal SSIS ExecuteSQL Task or the Maintenance ExecuteSQL Task with the above work around.


A regression bug was introduced in SP2 that resulted in a view problems when used in evironments that had other versions of SQL 2005 i.e. RTM and SP1.

The SP2 download has been changed to include this hotfix.

The change is mentioned in this blog post,

which jasper pointed out here