September 2007 - Posts

As much as I hate to admit it Microsoft is a US company which means everything defaults to US. So dates are in mmddyyyy not ddmmyyy format, words are missing letters such as color instead of colour and the currency is dollars and not the Great British pound.

Most of this is handled by regional settings so once you have said you are in the UK you get the correct formatting. However Reporting services is different.

A recent client was trying to display financial figures and so wanted the currency symbol to be displayed. They tried all the regional settings but couldn't get rid of the dollar sign.

The trick is to change the language of the report to English(United Kingdom). And heh presto you get a pound sign.


The SSIS pipeline is being changed in Katmai to allow more parallel processing. For any multi processor machine this should result in a performance improvement.

Essentially each arrow in your data flow can now have an active buffer, where as in SQL 2005 you only have 1 active buffer from the source to the next asynchronous component. Many people put in a UNION ALL component (which is asynchronous) to allow more buffers to be used.

This means the multicast will be able to have an active buffer on each output and not just have 1 buffer which is passed to each output.

I haven't yet played with this but I'm really excited.

For example a flow with 4 lookups currently only has data processed by one lookup at a time, this should enable each lookup to be processing data.

For more infor read Michael's post

Posted by simonsabin | with no comments

If you've written your own custom components then you should read this post on the SSIS forum about a possible change to the pipeline that may affect your component.

Posted by simonsabin | with no comments

I'm implementing some SSIS packages at the moment and started looking at logging with the event handlers. I simply wanted task start and end times etc and so used the start time system variables.

What I noticed when I ran reports was that the accuracy of these values is to the second. When you've got a package that has sub second tasks thats not much good.

So I created my own variable called Now and set it to "EvaluateAsExpression" and set the expression to be GETDATE().

What this means is that when task uses this variable it is evaluated as the current time and thus I get millisecond precision (or what ever the OS will give me but thats another story).

Now I can easiliy see the order of my tasks precisely.

It all seems a bit easy to me so I am sure I have missed something somewhere.


I don't normally blog about documentation errors generally because they're often not that major however this one is fundamental what is being explained.

This is the page online It talks about creating new thesaurus files. Unfortunately the XML example is wrong. When I looked at it intially I was suprised because the diacritics element wasn't valid XML. Well looking at the examples in the <SQL install folder>/FTData/ts????.xml you can see that the example in the documentation is wrong.

The diacritics element should be


Being a good SQL Server citzen I have reported this via the feedback in BOL so I hope it should be fixed.

Posted by simonsabin | with no comments

This week I've been producing XML Thesaurus files for SQL Server. The prupose is to enable spell checking in full text. The thesaurus file is an XML file wih a defined schema, I was creating the file with an XmlTextWriter but couldn't for hte life of me get it to load.

After much digging I realised the issue was that my XML file had a processing instruction in it (<?xml version="1.0" encoding="utf-16"?>), this is standard in a most XML files but unfortunately it causes full text to fail the file.

So how do you stop the the XmlTextWriter from producing the processing instruction. Its not straight forward to find out, but it is straight forward to implement. Simply you don't use the WriteStartDocument method. All this does is write the processing instruction. Pretty obvious really.


The agenda for SQLBits is now live at

Solid Quality

There is also more information on the Group By This is being sponsored by Solid Quality Mentors. We have hired a bowling alley in Reading so it should be a good laugh, we'll have some prizes for best score, worst score etc. If you have registered for SQLBits can you please update whether you are coming to the Group By as we need to know numbers ASAP.


There is also a travel section which provides details of addresses and also coaches from and to the train station. Redgate are kindly sponsoring these coaches which will also be available in the evening to go to the Group By. The times for the coaches are on the travel page, spaces are limited to the numbers that said they were coming by train.

The travel page also include details of hotels as I am sure some of you want to stay over in Reading on the Saturday night.

Idera have also agreed to become platinum sponsors. They will be providing each attendee with something special. We'll tell you more on the day.

Can I please remind you that if you cannot come please unregister with Microsoft events. We have a wait list of people that want to come along and can only do so f those that are not going to come unregister.

Posted by simonsabin | with no comments

SQLBits is now full, Yippee.

If you want to register then you will be put on a waitlist as people unregister you will be notified if you come off the wait list.

If you have registered and cannot come, please please, unregister. We have a fixed number we can allow in on the day and if you registered and don't turn up it means someone else will have missed out. So make sure you un-register if you are not able to come.

We will be running more of these next year, there are 10s of thousands of database professionals in the UK and we only have limited space at these events, so not everyone can attend.

In order to get the best attendance in the future we will be reviewing the attendee and registration list after this event, anyone registered that does not attend may find it impacts their future attendance at these events.


Posted by simonsabin | with no comments

Jon has posted a great summary of Addins in SSMS. Including many features you should consider voting on.

Posted by simonsabin | with no comments

Do you have a date and you want the day of the week but not the number but the name i.e. Monday, Tuesday etc. Well there are two functions that you can use with dates that can be used to extract a part of the date. One function returns the number and one returns text version. The functions are DATEPART and DATENAME respectively.

Both take a parameter that is the part of the date you want, see BOL for the complete list of options, a couple are

dw = day of week

m = month

so DATENAME(dw,'3 sept 2007') = 'Monday'