I'd never run into this one before and am not likely to again, but the fix is pretty simple and worth posting for my own reference (and I hope it helps anyone googling for it too). The problem occurs in SQL Server 2005 or SQL Server 2008 when you try and select a DISTINCT range of values from a sub-query that uses a UNION or UNION ALL to union two columns with different collations. The problem may occur if the collations are set differently at a database level while unioning two tables from different databases, and also at the column level...

As I discussed last week in part 1 of this article, color can be used to highlight common elements across groups. As promised, here's the necessary code to achieve this effect in Reporting Services 2005 (or you can download the finished report here): 1. Create a new report, and add a query. I've used AdventureWorks and a simple query that returns employees and years of service: --get employees by Department, with years of service from AdventureWorks SELECT TOP 50 D.[Name] AS [DepartmentName], EMP.[EmployeeId], C.[FirstName] + ' ' + C.[LastName] AS [EmployeeName], ...

UPDATE: Ola has moved his site to http://ola.hallengren.com - please check there for the latest versions of his IndexOptimise (and other) scripts). Ola Hallengren recently posted an all-in-one solution for backups, integrity checks and index optimisation for SQL Server 2005 and 2008 here (http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html). I'm particularly interested in Ola's extremely flexible index optimisation stored procedure, IndexOptimise (you also need his CommandExecute and DatabaseSelect scripts to run it). By default, IndexOptimise rebuilds indexes with fragmentation over 30%, re-organises indexes with fragmentation between 5% and 30%, and ignores indexes with fragmentation under 5% or smaller than a configurable size. The level of configurability offered improves...

Microsoft's David Lean has a great series of posts on "heatmapping" and other color-based conditional formatting in Reporting Services tables and charts (link is to part 4 of the series) (via Robert Bruckner).   Particularly useful are Dave's algorithms that work on colors and shades and conversion to hex values; part 2 of the series has how to change hue, while part 3 details how to change saturation. And, Dave packs as much useful content into his tips and asides as his main content! Tags: reporting services, sql server, color

Fellow Aussie Grant Paisley has set up a site called "Report Surfer" where you can view and share sample Reporting Services reports. The site covers Reporting Services 2005 and 2008, with a growing list of techniques demonstrated and comes in handy for viewing reports in the "report packs" from Microsoft, without installing. Reports can be run on the site or downloaded; free registration is required. The site is in beta and looks like it could be a worthwhile resource (including a list of Reporting Services bloggers in the sidebar, which I'm reading now). Tags: reporting services, sql server, sample

Reporting Services offers two "out of the box" methods to manage subscriptions (subscriptions are scheduled report executions, controlled by Reporting Services): Each report has a "Subscriptions" tab (depending on permissions) which shows your/all subscriptions (again, depending on permissions) for that report only All your subscriptions can be managed using the "My Subscriptions" link in the top right-hand corner of Report Manager There's one key thing missing from the two methods: how does an administrator or power user manage all subscriptions in Reporting Services? The Query I'm going to provide a starting point for a better way...

I'm about to have a couple of weeks holiday over Christmas (lucky me!) and one of the tasks my boss asked me to do before I went was make sure that e-mail notifications that were directed to me only, should be re-assigned to our entire team. We rely on SQL Server Agent Jobs and these can be set to notify operators when the job fails, succeeds, or completes (regardless of the outcome). After quickly looking through a couple of jobs, I found plenty that were only notifying me (oops). I needed a more foolproof way to identify the jobs that needed...

The one problem when using PIVOT in SQL Server 2005 is that the values to be pivoted (aka "crosstabbed") need to be hard-coded, and if new values come along the query needs to be altered. Additionally (but unavoidably), any queries or reports that rely on the PIVOT query will also need to be altered. To address this problem, I'm going to demonstrate with a simple example how to create an "other" bucket to catch values that aren't hard-coded. But first, some code! An example I've come across frequently is when a set of codes map to columns, and at a later date...

Hiding and showing columns is simple in Reporting Services - each column has a "Visibility" property that can be set using an expression. The expression could be driven by a parameter, calculation or data and only needs to resolve to "True" or "False". One way I use to hide and show columns is allowing the user to select which columns to show using a multi-value parameter (to do this, you must be running at least Reporting Services 2005 which introduced multi-value parameters). Here's my method, step-by-step: Step 1 is to set up the parameter. In the "Parameters" dialog, add a new parameter...

Recently I needed configurable grouping in an SQL statement - the exact requirement was that the results be grouped by day, week or month, depending on the user's selection. This can be done without a table of dates or dynamic SQL by using a CASE statement in the GROUP BY clause. Here's an example with CASE to achieve configurable date grouping using the "TransactionHistory" table in AdventureWorks: --"group by" variable DECLARE @GroupBy TINYINT --******** CHANGE GROUPING HERE ******** --group by Day (0), Week (1) or Month (2) SET @GroupBy = 2 --**************************************   --need to set the first day of week to be Monday so...

I came across a really interesting audit trigger for SQL Server 2005 a little while back, and thought it was worth re-posting it here.The trigger basically logs changes for only columns that are affected into a generic audit table. It's got some fancy bit manipulation coding (that I still don't fully understand) and uses dynamic SQL, but hey, since I normally stay away from triggers anyway, I can accept a little dynamic SQL :-)The alternative is to have an audit table that closely resembles my real table and log changes to that. And then run queries to find what was...