Simon Sabin UK SQL Consultant's Blog

The title says it all

Registration for SQLBits is now open.

We expect this to be very very popular so make sure you register quickly.

http://www.sqlbits.com/

The details of our acommodation deal have been announced. We are going to be using the Park Paza on Westminster Bridge.

http://www.parkplaza.com/hotels/gbwestmi

The speakers are going to be based there so why not join us, its a short walk from the venue.

We have a promotion code SQL6 which gives you a greatly reduced rate of £139 + VAT.

Register Now for UK Tech Days: Windows Phone 7 Series

https://msevents.microsoft.com/cui/EventDetail.aspx?culture=en-GB&eventid=1032442961

Come and join us to learn how to build applications and games for Windows Phone 7 Series.

Be amongst the first in the UK to learn how to build applications and games for Windows Phone 7 Series. We’ll introduce you to the development platform and show you how to work with the Windows Phone 7 Series development tools.  Each session will ramp up your knowledge and help you become skilled in developing games and apps for Windows Phone 7.

This will be a fun and practical day of detailed Windows Phone 7 Series development sessions covering the new Windows Phone 7 Series specification, applications technologies and services.

My current client is using schemas which is good as it provides nice seperation. However it causes me pain on a daily basis.

The reason. I can't use the built in keyboard shortcuts in SQL Server management studio.

I can't believe how painfully annoying this is. It's just madness that SQL Server's own tool doesn't support a best practice feature. 

You can vote on the connect item here to get this sorted https://connect.microsoft.com/SQLServer/feedback/details/349116/keyboard-shortcut-alt-f1-sp-help-doesnt-work-for-tables-belonging-to-non-default-schemas

I've blogged about this before, but this just annoys me so much I'm posting about it again.

Surely it can't be difficult to change. The other option is to open up SSMS so we can use add-Ins. I've blogged that before and you can vote on that suggestion here https://connect.microsoft.com/SQLServer/feedback/details/265567

I've also raised a connect item to give other improvements to keyboard shortcuts https://connect.microsoft.com/SQLServer/feedback/details/390612/improvements-to-keyboard-shortcuts-in-ssms

I did a presentation at DDD8 on the entity framework and how to stop your DBA from having a heart attack. You can find my demos and slide deck here http://sqlblogcasts.com/blogs/simons/archive/2010/01/30/Entity-Framework-how-to-stop-your-DBA-having-a-heart-attack.aspx

Whilst at DDD Mike Ormond interviewed me about my view on ORMs and the battel between DBAs and Devs. To see what I said go tohttp://bit.ly/bnf1By

The XML Source in SSIS is great if you have a 1 to 1 mapping between entity and table. You can do more complex mapping but it becomes very messy and won't perform. What other options do you have?

The challenge with XML processing is to not need a huge amount of memory. I remember using the early versions of Biztalk with loaded the whole document into memory to map from one document type to another. This was fine for small documents but was an absolute killer for large documents.

You therefore need a streaming approach.

For flexibility however you want to be able to generate your rows easily, and if you've ever used the XmlReader you will know its ugly code to write.

That brings me on to LINQ. The is an implementation of LINQ over XML which is really nice. You can write nice LINQ queries instead of the XMLReader stuff. The downside is that by default LINQ to XML requires a whole XML document to work with. No streaming.

Your code would look like this. We create an XDocument and then enumerate over a set of annoymous types we generate from our LINQ statement

XDocument x = XDocument.Load("C:\\TEMP\\CustomerOrders-Attribute.xml");

foreach (var xdata in (from customer in x.Elements("OrderInterface").Elements("Customer")

                       from order in customer.Elements("Orders").Elements("Order")

                       select new { Account = customer.Attribute("AccountNumber").Value

                                  , OrderDate = order.Attribute("OrderDate").Value }

                       ))

{

    Output0Buffer.AddRow();

    Output0Buffer.AccountNumber = xdata.Account;

    Output0Buffer.OrderDate = Convert.ToDateTime(xdata.OrderDate);

}

As I said the downside to this is that you are loading the whole document into memory.

I did some googling and came across some helpful videos from a nice UK DPE Mike Taulty http://www.microsoft.com/uk/msdn/screencasts/screencast/289/LINQ-to-XML-Streaming-In-Large-Documents.aspx. Which show you how you can combine LINQ and the XmlReader to get a semi streaming approach. I took what he did and implemented it in SSIS. What I found odd was that when I ran it I got different numbers between using the streamed and non streamed versions. I found the cause was a little bug in Mikes code that causes the pointer in the XmlReader to progress past the start of the element and thus

foreach (var xdata in (from customer in StreamReader("C:\\TEMP\\CustomerOrders-Attribute.xml","Customer")

                               from order in customer.Elements("Orders").Elements("Order")

                               select new { Account = customer.Attribute("AccountNumber").Value

                                          , OrderDate = order.Attribute("OrderDate").Value }

                               ))

        {

            Output0Buffer.AddRow();

            Output0Buffer.AccountNumber = xdata.Account;

            Output0Buffer.OrderDate = Convert.ToDateTime(xdata.OrderDate);

        }

These look very similiar and they are the key element is the method we are calling, StreamReader. This method is what gives us streaming, what it does is return a IEnumerable list of elements, because of the way that LINQ works this results in the data being streamed in, it returns the elements one at a tiem rather than building a collection of them. The key is the use of the IEnumerable and the "yield return"

static IEnumerable<XElement> StreamReader(String filename, string elementName)

{

    using (XmlReader xr = XmlReader.Create(filename))

    {

        xr.MoveToContent();

        while (xr.Read()) //Reads the first element

        {

            while (xr.NodeType == XmlNodeType.Element && xr.Name == elementName)

            {

                XElement node = (XElement)XElement.ReadFrom(xr);

                yield return node;

            }

        }

        xr.Close();

    }

}

This code is specifically designed to return a list of the elements with a specific name. The first Read reads the root element and then the inner while loop checks to see if the current element is the type we want. If not we do the xr.Read() again until we find the element type we want. We then use the neat function XElement.ReadFrom to read an element and all its sub elements into an XElement. This is what is returned and can be consumed by the LINQ statement. Essentially once one element has been read we need to check if we are still on the same element type and name (the inner loop) This was Mikes mistake, if we called .Read again we would advance the XmlReader beyond the start of the Element and so the ReadFrom method wouldn't work.

To get this working,

1. Put a script component in your data flow as a source component.

2. Then add the columns you wish to the output.

3. Add the StreamReader function to your script code

4. Put the first foreach in the CreateNewOutputRows method

4. Change the LINQ query etc to match what you want

With the code above you can use what ever LINQ statement you like to flatten your XML into the rowsets you want. You could even have multiple outputs and generate your own surrogate keys.

Don't forget to register for the SQL Server 2008 R2 Launch event in London on the 15th April.

http://www.microsoft.com/uk/techdays/itprodaythursday.aspx

Why not make a long weekend of it and do the launch and SQLBits (www.sqlbits.com ) followed by a few days sightseeing in London.

We will be opening registration for SQLBits next week but in the mean time get registering for the launch day, from what I understand there are only a few places left.

http://www.microsoft.com/uk/techdays/itprodaythursday.aspx

I've had a chance to look at the results directly and it is clear that there is a tough choice.

On the one hand people are saying that they prefer to have PASS put their money into chapters and things like 24hrs of PASS rather than an event on the east coast. Whilst at the same time almost 50% more people said they would be more likely to attend an East Coast event than a Seattle event, and 60% more said they would be more likely to attend a US Central region event. What’s more 60% said that the summit should be outside of Seattle every other year with only 19% saying it should always stay in Seattle.

So clearly there is a huge desire for a non Seattle event.

Looking at the other reasons for keeping in Seattle and the big one being that people want Microsoft speakers. More people think it’s somewhat important of very important that the conference is in walking distance of the hotels and restaurants. Essentially the Q6 questions show an even balance for normal conference, highlighting that they are prepared to travel, not with the family and they want a well laid out conference.

What’s very annoying is that the questions, as people have commented, were biased towards certain answers. For instance there was no option about whether people feel it’s important to have industry leading speakers, MVPs etc at the conference. Only questions about Microsoft speakers. I know survey writing is very difficult to avoid biasing the answers one way or another. There was also no choice to show peoples preference, would people prefer Microsoft speakers or the summit to be held on the East Coast/Central US. I also find it amazing that people prefer hundreds of developers rather than the SQLCAT and CSS teams, surely that indicates another issue about a lack of understanding of what the these teams do.

All in all it is clear that people showed they want an event outside of Seattle and don't want PASS to be putting money into that instead of into other community activities. I find it surprising that there appears to have been a huge weighting against certain questions which have prioritised them over the huge desire for a PASS summit outside of Seattle.

Let’s see where we will be in 2013 or maybe they will rethink 2012 who knows.

Due to the overwhelming number of session submissions we have increased the number. So we now have 24 sessions plus 4 sessions from sponsors planned. To do this we are shortening each session to 50 minutes.

I hope this goes down well, I guess we'll see when we get the feedback in.

I've just received the PASS connector newsletter with details of the decision of where to locate the next summits.

To me it shows you how you can spin statistics they way you want to.

I am very surprised by the numbers, 81% said that they would like an east coast event. The spin is that "When we look at responses from only 2008 and 2009 Summit attendees (our most successful ones by far), the number who want a future Summit outside of Seattle drops to 69%." Wow the number drops that’s bad. We must stay in Seattle then.

My take however is that 69% of those willing to travel to Seattle want an event NOT in Seattle. Doesn't that suggest that people would like an event not to be in Seattle?

I appreciate the comment about the launch years however 2012 isn't going to be a launch year because they need to get SQL out of the door before then to meet the 3 year software assurance cycle. So sticking 2012 in Seattle is IMHO a bad decision.

It might keep the cost down for the PASS org, Microsoft and the attendees that live near Seattle, but does it keep the cost down for the rest of the SQL Community?

They do mention a possibility of an event on the East Coast and I do hope that it’s not just lip service. Flights for me to the East coast look ~30% cheaper than to Seattle and I've never been to the East Coast. Come on PASS show you are listening to the community.

David's posted a great post on shrinking the transaction log and log shipping. Log shipping and shrinking transaction logs

Unlike shrinking the data file shrinking the transaction log isn't a bad thing, IF you don't need the log to be that size.

I've seen many systems that shrink the log because it has grown only for it to grow the next day to the same size becuase of an overnight operation.

To reduce the growth of the transaction log you need to do one or more of the following,

1.Back it up more frequently
2.Change to simple recovery model
3.Use minimally logged operations
4.Keep transactions short and small
5.Break large transactions into smaller transactions
6.If using replication ensure that your backup of the replication topology is frequent enough

I just got this from fello SQL MVP Chris Testa O'Neil

"I am pleased to announce the release of the Author Model eCourseCollection 6233 AE: Implementing and Maintaining Business Intelligence in Microsoft® SQL Server® 2008: Integration Services, Reporting Services and Analysis Services

This 24-hour collection provides you with the skills and knowledge required for implementing and maintaining business intelligence solutions on SQL Server 2008. You will learn about the SQL Server technologies, such as Integration Services, Analysis Services, and Reporting Services.

This collection also helps students to prepare for Exam 70-448 and can be accessed from: http://www.microsoft.com/learning/elearning/course/6233.mspx 

It appears the video for my session on when query plans go wrong was not working.

This has now been fixed.

You can view the video here http://sqlbits.com/Agenda/event5/When_a_query_plan_goes_wrong/default.aspx

Something mildly amusing for a monday morning.

The Deprecation Event Class uses the ntext data type which is a deprecated feature.

To have a look yourself go to http://msdn.microsoft.com/en-us/library/ms178053.aspx

Yeh I know its profiler that is using ntext, still made me smile.

We will be deciding on the sessions tomorrow (Tuesday 8th March) so make sure you get your session in for SQLBits quick.

Don't forget we are focussing on performance an scalability so make sure your session covers one or both of these.

To submit your session