Do you need an SSRS 2005 report that displays totals for the previous 3 months going horizontally across ? Well, I did, and my first approach involved using derived tables, each with a separate date range, to get each month’s total to appear in its own column. While I achieved my goal, performance was lousy because the query needed to execute 3 times (once for each date range).

I shaved 2 minutes off the query execution time with a second approach. This alternate approach involves using a single query for the entire, 3-month date range, along with a GROUP BY clause including year and month. For the SSRS piece, I use a matrix format to get the months to display horizontally across. Since I have additional columns to display besides the total, I have to do some extra maneuvering, but more on that, later.

Here’s a look at the T-SQL code:

With the stored procedure written, I’ll go to SSRS 2005 and add a Matrix Report Item. I then drag the year field to the matrix cell that displays columns, the TerritoryID field (leftmost column on desired report) to the matrix cell that displays rows and the qty field to the matrix data cell (where the columns and rows intersect).

However, this is not a simple matrix scenario as I have several additional columns I need to display, besides the totals by month. In order to accomplish this, I’ll add row groups for each additional column.

But first, I’ll start with the column groups. I right-click above the year column group and choose ‘Add Column Group’ to add the month…

…and sort on the month field:

That takes care of my column groups.

I proceed in the same fashion for my row groups. I right-click on the first row group and choose ‘Add Row Group’:

I group (and sort) on the next field I want to appear on the report, the ProductNumber:

I repeat this until I have all the columns I need.

As an additional step, for each column and row group I want to sort on, I make sure I’m sorting on the same field I’m grouping on. To check this, I right-click on the group and choose ‘Edit Group’:

Now, the headings for the row group columns are missing because the Matrix Report Item has only an empty box in the upper, lefthand corner by default. To avoid ambiguity in the report, I add a Rectangle Report Item (to the upper, lefthand corner) then textboxes, one for each column needed:

I then type in the row group column headings:

I also apply some formatting to improve the appearance of the report, including editing the expression for month so that the month name appears:

And voilà ! Below you see the finished report that is speedy and even has column headings for the extra row group columns !

Introduction

A few days ago I made a post on how to make a factory with T4 templates. That version only made one factory for one namespace. So it was pretty limiting. But as you could see, in that example, we had 2 namespaces, namely Test and Test2.

We need a factory for both those namespaces. We can either copy/paste or change our template to produce more files. After all, these files are nearly the same.

The code

The code for this is pretty simple, once you know how. And here it is.

As you can see, I added a List with namespaces, I could have just gotten them from the assembly as well. And then I have a for each which makes a template for each of those namespaces. In the end of that loop, I call WriteTemplateOutputToFile with the desired path. And now comes the important bit.

The bit of code above writes the generated code to the filesystem and clears the Generated code so that you get a blank slate to generate the next file. I also added a comment at the top of the file to warn users not to change the file since it is generated and changes will get lost when regenerated.

Conclusion

Why write code if you can have it generated? Lazy people make good programmers and even better DBA's.
Adding a warning to your generated code is also important so that other people don't change it just to find out they their changes don't stick.

Resources

• How to generate multiple outputs from single T4 template by Oleg Sych
• Multiple outputs from T4 made easy – revisited by Damian Guard
• StackOverflow answer to question T4 template to create *multiple* html (for example) output files per table from database schema by Michael Maddox

I wrote a blog post 437 days ago asking you the reader if you would be interested in a FxCop tool for SQL Server. That post can be found here: SQLCop, FxCop For SQL Server, Would You Be Interested in This?. Today I am pleased to announce that the first version of this tool is available. The tool is free and will remain free, we will never charge for it.

The tool is only 412 kb to download and no installer is needed. The reason we didn't do an add in in SSMS is because we wanted people to be able to run it it by itself. The tool was tested on Windows XP, Vista and Windows 7 (64 and 32 bit)

A big thanks to George Mastros for coding this tool, you can also thank him on twitter here: http://twitter.com/gmmastros

The tool will not modify any of your database, it will only check and if it find issues it will list the objects if possible. It will also show you a blog post or wiki article explaining why something is a problem and how to remedy it.

To run the tool you just download it from the download link at the SQLCop homepage here: http://sqlcop.lessthandot.com/index.php

After that you run the tool, you will get the following login screen.

Enter the server name, database name and credentials (or windows authentication)

After that you will see a screen similar to the one on the bottom. From the left side tree menu expand the node that you are interested in. When you click on an item the right part of the screen will show an html page showing you why the issue might be problematic and how to remedy it. Just remember that these issues might not be issues for you, maybe some of these objects are not used anymore but people are scared to drop them because they might break things down the road. So use your best judgement!

Here is a video of the tool in action so that you can see how it works before you install it

More info about this tool can be found here, including where to post questions if you have any problems: http://sqlcop.lessthandot.com/index.php

You can also leave a comment here but we prefer you do it in the SQLCop help forum because the format is better suited for questions and answers

The recent thread in ASP.NET forums is the primary reason for this blog post.

I want to show how error messages from the SQL Server were handled in our web pages. The site I was working on had most of the functionality developed by the previous developer. Unfortunately, most pages didn't use a separation of layers and used SQLDataSource for data manipulations.

The sample I want to show was called from InsertPerson.aspx page. For inserting a person we used the stored procedure I showed in my previous blog How to insert information into multiple related tables and return ID using SQLDataSource.

For inserting a new person the InsertPerson.aspx page was used that had multiview control, FormView controls with different tabs using User Controls for each tab (the tab was a special control designed by my colleague).

Here is the beginning of the page's code:

etc.

The InsertPerson.cs (code behind) had the following code in regards to showing the error message:

The way the error was passed back to the client was handled in Form_ItemInserted event:

The page calling the InsertPerson page was called CreatePerson.aspx and had the following ASPX code:

So, hopefully you see the logic of the pages. The CreatePerson page had a place to show an error message or a link if the insert worked correctly. From the InsertPerson page we manipulated the parent CreatePerson page and displayed the appropriate message (either the error message or a link to edit a newly inserted person).

Introduction

Some jobs are tedious and errorprone. To avoid the errors we should unittest, but to avoid the tediousness we can make use of code generation. One such code generation tool is T4. It is available in Visual studio so why not use it? Well I can think of a few reasons. No intellisense no code coloring and no refactoring. But I guess we will have to learn to live with that. Or use a third party editor like the one from Clarius consulting. So I thought code generation would be a good fit for some of my factory classes I have.

The goal

Well that is simple I want to create this file automagicaly.

The point is that it should create a Function for every Interface I have in a certain namespace.

Getting the interfaces

First problem to solve was how to easily get the interfaces in a nice list.

I made a ConsoleApplication to test this. First I added a few interfaces in 2 different namespace and then I created a program to find the interfaces in the namespace I want.

Yes that is C#. And why is it C#? Because I decided to write T4 templates in C#. To keep the generated code apart from the generating code. You could also write the generating code in VB.Net if you like.
So we have this covered. Seemed simple enough.

The template

And now came the tricky part, writing the template.

First of all we create a file called IFactory.tt. Visual studio will give you a warning if you do that, but it is safe to ignore that warning, just pretend that you know what you are doing.

Then we add the generating language. and we tell it to output our file with a .vb extension.

Now for the code.

Text between <# #> tags is our code text between <#= #> tags is text that will be output to the file. Text between <#@ #> tags are directives.

The above will fail miserably and Google failed me too.

You will get 2 errors out of the above.

Compiling transformation: The type or namespace name 'IFactory' could not be found (are you missing a using directive or an assembly reference?)

and this one

Compiling transformation: Could not find an implementation of the query pattern for source type 'System.Type[]'. 'Where' not found. Are you missing a reference to 'System.Core.dll' or a using directive for 'System.Linq'?

Clearly it is complaining that you need to set references and imports.

However it is not always clear to me when you need to this and for which libraries. I guess we just wait for an error and then add it.

In my case I needed to add these.

The directives with assembly are actualy what you would do when you do Add reference for your project. If it is a system assembly then you can just use the short name like I did for System.Core if you want to reference one of you project then you need to add the fullpath and filename. like I did for my project. If you then have all the references you need you can add import directives. And then it all just works.

So the final code would then be.

The tt will now have a vb file attached to it.

And that will then look like this.

All ready for use. Now I can just add an interface and the factory will.

Warning

The above code works, but as soon as you build your project it wiil complain to say that the exe in the bin folder is blocked. This is a well known problem. It has to with the fact that the template engine does not release the AppDomain immediatly, but it caches it. There is however a nice solution to this problem. And it is called the T4 toolbox and the VolatileAssembly. You just need to download and install the Toolbox then you need to swap the assembly directive to this.

You can read more abot this problem in these locations.

<#@ VolatileAssembly processor="T4Toolbox.VolatileAssemblyProcessor" name="$(SolutionDir)/ConsoleApplication1/bin/Debug/ConsoleApplication1.exe" #>

• Understanding T4: <#@ assembly #> directive by Oleg Sych
• Working with the T4 by Siderite

Conclusion

Code generation is a powerful tool that can take a lot of work out of your hands. But it can also become a maintenance nightmare if you aren't careful. I learned a lot while making this post. And all this because someone on Stackoverflow asked how to add the namespace to a class template for VB.Net.

Resources

• T4 (Text Template Transformation Toolkit) Code Generation - Best Kept Visual Studio Secret by Scott Hanselman
• Code generation using T4 Templates by DotNetThoughts
• Everything about T4 by Oleg Sych
• Walkthrough: Creating and Running Text Templates on MSDN
• How-To Q&A: How Can I Automate Code Without Resorting to Heavy Code-Generation Techniques? by Kathleen Dollard

Introduction

Making a splashscreen in WPF is easy, since the .Net framework SP1 there is even a built in method to do this. But this method only accepts a static image. I would have liked a more dynamic thing. Something that says what it is doing. And something that fades in and out and has a custom shape. I'm not very demanding but I know this would be very complicated in windows forms. Possible but difficult. In WPF it seems a bit easier. So I went on a Google frenzy and combined several things I found along the way.

The static splashscreen

• Make a nice Image.
• Add it to the project via Add->Existing Item or Ctrl+D.
• Click on the file and change the Build action to SplashScreen

And that's all there is to it. It does have fade-in and fade-out effect.

The dynamic splashscreen

This was very easy since Google's first hit was this MSDN forum post.

And someone even handed us the VB.Net sample.

So I just had to add a window called splash.xaml

That had these properties.

And this in the code behind.

I changed it so that you can show messages to the user and I set the progressbar to continuous.

Then we just make this form the startup form. And we add a little bit of code to the codebehind of Application to make it all work.

That was simple and I didn't change much from what I saw in the post. You can just copy/paste it but you have to understand what it is doing.

The fade in and fade out

But the above code is still not much better than what we can do in winforms. So now it is time to add some fade in and fade out to it. Again I directed my attention to Google and found some inspiration in a Stackoverflow article.

But there are some problems with what they said, the solution is not really copy-pastable as is.

Getting a fade in is easy. you just add a trigger to the window, like so.

That works, but it had one minor glitch that I will cover in a little bit.

Then you need to add the fadeout. This is more work and needs some code behind.

First you add some xaml.

And then you add a few bits to the codebehind.

This has a slight bug in it. Which can only be seen if your splash screen is not going to be shown long enough (less than 4 seconds to be precise). So I had to make a few adaptations so that I would still get a fade in and out even if the loading of the form takes less than 4 seconds.

First we add an event completed to the beginstoryboard. like so.

And then we change our codebehind to this.

I added the beginstoryboardcompleted and made sure it only it was set to true, meaning the fadein is done, before starting the fadeout. Simple really, if you understand what you are copy pasting.

The custom shape

To make a form a custom shape is just as easy. First you set your window properties like so.

Then you add an image to form. and that's it.

And just for fun I made the ugliest splashscreen you can think of, but it works. This is the design view.

See how the image is outside of the bounds of the window?

Now see that when you run it it will chop of those parts.

So now your window is completely transparent and as ugly as I could make it ;-).

Conclusion

Making a dynamic splashscreen in WPF is easy and well worth the trouble. Your users will love you. Getting to grips is easy too, you can use Google and find just about anything you want or can think of. Some examples are just good and some examples need a little work. Some examples need to be ignored. But remember don't just copy paste the code, try to understand it first. You know you understood it when you can combine and change different pieces and make them work together.

Waiting for your company to provide training courses or advancement opportunities? It might be a long wait. Unfortunately many companies overlook professional development or consistently sacrifice it when the first round of annual budget cuts occur. Even when we do get training opportunities, many of us continue to select classes at random, based on what seems interesting at the moment.

We live in a field that is expanding daily, where the environment we work in is drastically different from what it was 5 years ago and from what it will be 5 years from now. Whether we plan on being in a different role or not, advancing our skills and staying in step with the market is a must.

This post covers a number of helpful links and ideas for staying up to date in our field.

Development Plans

Creating a development plan can help provide focus and give us a measure of our progress. These articles provide additional information on creating and refining our plans:

• SQL Server Professional Development Plan, Part 1 and Part 2 by Buck Woody (blog | twitter)
• How to Write a Great Individual Development Plan (IDP) and a great checklist by Dan McCarthy (blog | twitter)
• Your Own Personal Development Plan by Jeremiah Peschka (blog | twitter)

Tools and Processes

Once we have a plan, we need to be able to execute it. Having tools to help us execute the plan, methods of communicating the plan, and tricks for finding time can all help us be successful.

• The Personal Kanban site has a lot of good information on creating a visual board and process to help manage short and long term tasks
• Jes Borland (blog | twitter) helps track her progress by reporting a Quarterly Status to her friends and the wider community
• And American Express's OPEN Forum has a number of ideas on where we can find time to execute

Free and Cheap Resources

Not all development costs money. My own plan includes a certain amount of blog writing (free), reading a certain number of articles each week (free), an ongoing effort to keep up to date with webinars and webcasts (free), and attendance (and beginning to speak) at user groups and events (the free kind).

I can't afford Tech-Ed or the PASS Summit, but I can afford:

• Watching the recorded Tech-Ed sessions for free at MSDN (I've had over 11 hours of free training so far)
• Watching the 2008 PASS Summit recordings for free (requires login)
• Catching free live and recorded webcasts from Microsoft

Plus there are events like SQLSaturday, Code Camps, and user groups galore that don't cost $1000's to attend.

If you need more ideas, Ted (blog | twitter) posted an article with more SQL Resources here and I presented a list of ideas in Part 1 of the "There is Never Time For..." series.

Have more ideas or links for resources? Add them to the comments below. I'm always on the lookout for more ideas and I'm sure others would appreciate them as well.

This is your query on a rollercoaster. Not a fun trip and sometimes bringing a paper bag with you is a necessity.

It really happens

The scenario starts with a phone call from the support desk reporting failed integrations from the ERP system to the WMS system. This integration process is critical for the production of items with the company to fulfill orders and build inventory. Without the process, finished goods inventory is not posted and thus does not become available for sale. This causes a more detrimental effect on financial reporting and filters down to the revolving reason we are in business - making money.

In a normal IT team structure, there are three high-level components we all start with to troubleshoot backend systems process failures: Network, Systems/Applications and Database Server Performance. Each team sector should work together and focus on their specialty to document possibly causes. This will benefit the situation the best and also create a collaboration point where the team can compile everything found in order to proceed with the fix. As a DBA you will find yourself in this situation often and there are a couple of key points you can focus on to quickly determine if the cause is the database server. After your findings the DBA can then focus on areas to alter, tune or repair.

In the scenario that my own team found themselves in recently, the troubleshooting steps lead to find timeouts occurring within the integration processing of the application. The application was kind enough to have built in logging to show the query that was being timed out which greatly lowered the analytical stage in finding the root cause. In most cases this isn't logged but with the combination of profiler and monitoring during these timeouts, the query can be extracted in order to proceed with determining if it is the cause and possible resolution.

Once looking at the execution plan of the query that was in the log related to the timeouts, an RID Lookup operation was found with an Index Seek. In some cases an RID Lookup may not cause any problems with performance and go without notice. However is this case, the table in question that the query was requesting data from was roughly five millions rows. This type of table size will cause the RID Lookup to slow the performance of even the simplest queries.

Showing is learning

To portray the data, indexes and query we will step through recreating the exact situation that occurred.

First step will be to create a database that we can work in. We will then create an item table and load the item table from use of the AdventureWorks database table, "Production.Product".

Note: AdventureWorks can be used for this entire recreation but to retain the database as it is the secondary database was created for example purposes.

Run the following to prepare the database and needed objects:

Now that we have our testing area, we can create some common indexes that may be found on a table like the items tables.

First a nonclustered index on the ITEMCODE

Next, a nonclustered index on CREATETIME, ITEMCODE and CREATEDATE

Once these indexes are created we can run a basic query to extract ITEMNAME and CREATETIME with a WHERE clause only looking at a specific ITEMCODE value.

We should be fine as the nonclustered index, "IDX_ITEMID" should be used in an index seek given the WHERE clause on ITEMCODE. However, when we run this query to check the estimated execution plan, we can see we have a lookup occurring.

The lookup is caused by the fact that there is not a covering index to fulfill the entire needs of the query. The WHERE clause and columns returned combined equate to, "covering". In order to satisfy this query and remove the RID Lookup, we need to create a covering index or a clustered index on the ITEMCODE. Creating a clustered index physically orders the data under the conditions of the index and in some cases (like this) that was not possible. An effective solution in this case is a nonclustered index covering the entire query requirements.

Note: Let's say that the table in question isn't a HEAP and there is an existing clustered index on a different key. Since we can only have one clustered index per table, the resolution of ITEMCODE becoming a clustered index to resolve our RID Lookup is not possible. A nonclustered index is then the only way to resolve (or remove) the RID Lookup.

To create a covering index we can do the following

After looking at our execution plan, we should see only the index seek operation being performed

Let's put things into perspective. We all know an index seek is optimal (in most cases). We do see our index seek in the execution plan prior to our resolution of a covering index but we have the handicap of the RID Lookup being performed in a nested loop with the index seek. This will result in the cost of the index seek operation plus essentially the same effect of a table scan under a nested loop operation. For each record we find in the index seek, we have to run out to the disk, find the remaining data we need in order to fulfill the needs of the query.
This is all reflected in a nested loop operation.

What is shown below is the Index Seek retrieving the data directly based on the index pointers and requiring a need to loop back to the data to fulfill all of the data needed by the request. This causes the query to perform slowly and also increases the possibility for high CPU utilization and high IO.

Basic troubleshooting steps of query performance like this should be a daily part of database tuning and monitoring. Allowing changes to go directly into production database servers without the initiative of code reviews and heavy testing will allow these types of problems to happen. The best resolution of all is to prevent these types of problems by not bypassing those normal transport objectives.

I have been doing most of my frontend in windowsforms but I thought it was time to switch to WPF after all it is already at version 4.0 so most of the little quirks should be gone by now.

First thing I did was to convert my dashboard over to it. I can use all the same controllers and model so I only need to worry about the views.
On my dashboard there is a ListBox which shows the user some messages of the system (not that anybody really reads them, but it looks cool).

The text for each item can be longer than the width of the ListBox. This is no problem since there is a horizontal scrollbar that can be seen. But I don't like that.

And this is the XAML you use for this.

And I filled the ListBox via this method. Which is an event coming from the ViewModel. Witch needs to be made threadsafe.

So I'm using the Itemsource for this.

I would prefer some text trimming (the three dots) and a tooltip with the complete text.

In Windowsforms this would just be difficult to do. In WPF this is very easy.

You just need to add a Datatemplate like this.

And then change your ListBox a little.

And that is it. As you can see I removed the Horizontalscrollbar and added an ItemTemplate.

Or you can make it wrap instead. Just a little change in the XAML.

And then it looks like this. I guess you could then do without the tooltip so I removed that.

The hardest thing to find on the internets was the Text="{Binding}". I actually didn't find it on the internets I just got lucky.

Today I was in the process of making my own copy-paste Analog clock. I went aruond the internet and found this pretty thing. Of course I could have used that code and gone on with life. But that was not the point of the exercise. I wanted to learn something of this.

I liked the way he did the animations, no more need for a timer to update the angles, all is done in XAML.

So his project is Silverlight. And he has this to aniamte the hands of the clock.

and this in his codebehind

Cool I can just copy paste that and get on with it.

But Silverlight and WPF aren't exactly the same they just look the same.

In WPF A name is no good for a resource apparently. It needs a Key.
So the above XAML becomes this instead of the above Silverlight code.

And then you find out that the code you have doesn't work either. YOu need to change it to this.

And BTW doing s.Begin(Me, True) does not work whatever MSDN say.