Simons SQL Blog

Anyone that has used service broker and wanted to find out more would have generally ended up at one of two locations. One is Roger Wolter and the other is Remus Rusanu. Both are Microsofties that worked on servicebroker and both are unbelievably helpful in answering questions.

Well Remus is only a softie for a few more hours and then he'll be off in the free world doing consultancy.

His new blog is can be found here http://rusanu.com/blog.

I wish him all the best in his new adventure. They are providing services accross the development specturm so keep them in mind. If his contribution in the service broker community is anything to go by they will be very good.

So why the hell do they keep hanging up on me.

I'm trying to upgrade my phone and have gone through the maze of automated systems. I get to the end and and told,

"Please hold whil I try and connect you to one of our customer services representatives".

it then goes silent and I'm then told

"Orange is committed to providing excellent customer services. Your call may be recorded for training purposes"

and then it all goes dead and I left in limbo with a dial tone that tells me the situation is final.

Why oh why do they do that?

You may have noticed a guy chatting with people in quiet corners at SQLBits. The guy was craig murphy and he was doing interviews throught out the day. The podcasts of the interviews are now available on Craigs blog.

http://www.craigmurphy.com/blog/?p=750

If you went to SQLBits and saw Dave's session you will undoubtedly be smiling at this point.

Daves session was by far the most popular but if you didn't get to go to SQLBits or didn't get to Dave's session then all is not lost. Craig Murphy managed to video the session and has put it online.

So enjoy the wonder fo Daves Top 10 SQL Keywords.

One often thinks that the use of decimals provides the most accurate recording of numbers with decimal places.

Whilst that is the case when you don't have any rounding. At the point that you round data you are starting to loose accuracy.

Colin has blogged about the use of floats. "Do you use the float datatype?". Which has got a good discussion started on it.

If you are performing multiple calculations where you are multiplying numbers with decimal places then you can soon end up requiring lots of decimal places to maintain total accuracy. However the second you start dividing numbers you are in a whole world of in accuracy.

So you need split a payment of £10 3 ways. Who gets what, 3.33 to each and then 1 p remaining.

In using SSIS you will have noticed a section in the toolbox labelled Maintenance Plan Tasks. You will have seen in there tasks like "Execute SQL Task". You may be tempted to use these in your package.

DON'T

These are tasks that are designed to be used in maintenance plans and not in normal packages. These tasks have built in looping for iterating over databases and have custom error handling to ensure that the package works as expected in a maintenance plan, (generally continue on failure) unlike in a normal package which is to fail.

This was highlighted with SP2 which introduced a number of bugs with these components resulting in you maintenance plans not doing what they were supposed to.

So make sure you only select the tasks from the main “Control Flow Items” section.

Just a quick note that if you are developing SSIS packages with Excel on a 64 bit machine you need to make sure the package is configured to not use 64bit runtime. There are no 64bit Excel drivers (Jet) and so whilst you code works in the desiger, i.e. getting fields and doing previews etc it won't work at runtime. Thats because the designer is 32bit and the runtime defaults to 64bit on a 64bit machine.

So how do you achieve this. You need to go into the Project Properties and select the Debugging node on the left and then change the Run64bitRuntime to false.

When building an SSIS package, which I seem to be doing more and more of lately, I am invariably using a data flow. Whilst the data flows often end up with complicated transforms in them my approach to building it is very much bit by bit.

The first step is to get your source component working. To do that drop the source component you want on and configure it appropriately. Next you need to test it. I find the simplest way is to drop a derived column component on and connect the source component to it.

You now have a valid data flow that can be run. A data flow only has to have a source component, using the derived column transform allows you to put a data viewer on the flow of data coming out of your source component.

This component can be removed later on, however I often find I leave it there to perform data cleansing or calculating new fields etc.

If you are doing looping in your control flow you can set that up at this point to verify that your looping is working correctly. Its a lot easier to test at this stage when it is quick to do so rather than when you have lots more components in the data flow.

Alex has a great post questioning whether you should ever go against the "never use select * " mantra.

I for one think Alex has a valid point. The only point against select * is that you have to look elsewhere to find out the columns that are being returned.

However if you already have the list of columns in your query then its just a cut and paste to put them in the top level select.

and finally its only going to save you a few seconds. Far more than its taken to read Alex's post and for me to write this one. So just be a good citizen of planet SQL and "don't use select *".

I've seen it and I love it. If you've ever used Visual studio you would know that you can expand and collapse blocks of code to make your code readable. Well in Katmai you will be able to do this in management studio.

So if you've got big sps then you can easily simplify the view by expanding or collapsing blocks. In addition this feature helps with the whole BEGIN and END synchronisation. If the BEGIN corresponding  to your END is 2 pages up it is easy to miss resulting in an "incorrect syntax near ...<your last statement in your sp>" 

ps this is bought to you as part of TSQL Intellisense. I wonder what my next favourite feature will be ???