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...

Since my Access days, I've believed in renaming important* controls in reports. Now that I use Reporting Services daily, this practice still holds: ambiguous names like "Textbox1" or "Textbox112" become "ReportName" or "FinalBalance", "table1" becomes "WidgetSalesTable", and "chart1" becomes "MonthlyTrendChart". As well as better organising the report while in development and adding only a small amount of time to actually do, renaming controls is useful to me later when I'm maintaining a report as I can see which controls contain which data points. There's one further advantage to renaming controls in a Reporting Services report: because reports are stored in XML format,...

Recently I investigated a Reporting Services problem where a user first ran a report with one parameter, then switched applications to do some work, then returned to the report and attempted to run it with a different parameter. The end result was that they received an "execution xxx cannot be found (rsExecutionNotFound)" error. I too see this "execution cannot be found" error intermittently, sometimes after coming back to a report I'd run earlier and clicking "Back" or trying to expand a section on the report, and sometimes when my browser loads up tabs from a previous session. And The Reason Is You Behind...

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...

A requirement for the reporting portion of an upcoming system of mine is that the Y-axis of charts can start at 0, or a defined number, or by letting the chart decide (automatic). This, like many of the requirements for this particular system, is because of the way Excel charts work. Dynamically setting the minimum axis in a Reporting Services chart can be done by typing an expression in the "Scale", "Minimum" box (for SSRS 2005 and greater only). Note you don't have the benfit of the expression editor: In the above image I've passed the minimum with the dataset. I'm sure...

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...

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...

The Reporting Services Catalog table in the ReportServer database contains the RDL XML in the Content column, serialised to binary format. I had an issue recently where I needed to retrieve the RDL XML from the Content column from a backup of the ReportServer database using SQL Server 2005. Here's the code that did the job: SELECT [Name], CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXML FROM ReportServer_Backup.dbo.[Catalog] WITH (NOLOCK) WHERE --get only reports, not folders or other resources [Type] =...

The first thing I need to say about this book is: wow. T-SQL Querying is easy to read and yet really gets in depth with T-SQL. It reminds me of Itzik's presentations - complex concepts explained in a comfortable and informative style. The book covers query processing and optimisation, has some useful scripts for performance monitoring, logic puzzles, the new features of SQL Server 2005 like CTEs, ranking functions and APPLY, and plenty more. Some sections of the book could easily become my standard reference material on logical and physical query processing, while other sections are almost tutorial-like. One of the advantages...

I've always had a problem with documentation. I agree it's absolutely necessary; it's just often the requirements for documentation are so vague that I never know if I'm done or not. One way to enforce standards when documenting tables and views in SQL Server is by using extended properties and auto-generating the documentation. I've used many free tools to do exactly this over the years; currently I favour SQLSpec. If you're interested in a cheap, do-it-yourself method of documenting SQL Server databases, check out this article titled "Create a SQL Server Data Dictionary in Seconds using Extended Properties" at MSSQLTips. Although...

One annoyance I have with SQL Server 2005's right click "Script As...Create" is that permissions on the object(s) to be scripted are not included by default. You can change this in SSMS by going to Tools, Options and then selecting "Scripting" from the tree. There are lots of options for changing how scripts are generated - see this MSDN entry for the full rundown on scripting options. To add permissions to the "Script As...Create", scroll down to "Object scripting properties", and set "Script permissions" to "True". Done! Now if only I could get a DROP in the script before the create... Tags: sql server,...

What does the "Category" drop-down for SQL Server Agent jobs do? The options are so limited and make no sense to me: "Full-Text"? "REPL-Alert Response"? "Web Assistant"? If, like me, you've ever asked yourself the above question, wonder no more. Tim Ford has an article on MSSQLTips.com titled "Custom job categories to organize your SQL Agent jobs" which answers this, and more. Tim's article is so useful and simple that immediately after reading it, I went out and added a couple of custom categories, and then assigned my "[Uncategorized (Local)]" jobs. All this took less than 10 minutes, and now I know...

Recently, my boss paid for me to attend Microsoft Course 2780 Maintaining a Microsoft SQL Server 2005 Database at Solid Quality Learning (thanks, boss!) Around 18 months ago, our department was put in charge of a SQL Server 2005 box. I've been using SQL 2005 since then which of course gave me a huge headstart in the course. Way back when, I attended the 2779 training (focused more on developers), and thought that the 2780 training would complement it and make me into a DBA :-) Well, the training is over, and I'm not a DBA, but I got a lot out of the...

I'm very fortunate to be at SQL Server 2005 training in Melbourne all week this week. Our instructor is Aussie SQL Server MVP Rob Farley who blogs at msmvps.com/blogs/robfarley/ and is unbelievably knowledgeable about the product (of course).So far I've collected 2 useful code snippets from Rob - more on useful stuff from the course later:--show all plans in the cache, courtesy Rob Farley--adding "OPTION (RECOMPILE)" means that subsequent executions --of this query aren't added to the cacheSELECT  * FROM    sys.dm_exec_cached_plans p CROSS APPLY         sys.dm_exec_sql_text(p.plan_handle) t CROSS APPLY         sys.dm_exec_query_plan(p.plan_handle) q OPTION (RECOMPILE) --comma-separated from resultset in 1 call,...

If so, then it's worth knowing that mainstream support ends April 2008 (via Andy Leonard, who asks "What's that ticking sound?") Tags: sql server, support, microsoft

I haven't posted anything on Reporting Services recently. So, I have two links for the infamous matrix control: Advanced Matrix Reporting Techniques by David Leibowitz on Red Gate's Simple Talk site How to format the subtotals of a Reporting Services matrix differently, using InScope by fellow Aussie and SQL Server MVP Rob Farley Because I feel I need to compensate :-) Tags: sql server, database, reporting services, matrix

We have an upcoming requirement to kick all users out of a database before running a backup. So I'm saving this script for future reference (thanks to Chris for posting it to the SQL Down Under List):CREATE PROC [dbo].[sp_SpidKill] @db VarChar(200) AS DECLARE @Tmp VarChar(10) DECLARE @spid VarChar(10) DECLARE @Kill VarChar(200) SELECT @spid = Min(spid) FROM master.sys.sysprocesses WHERE dbid = DB_ID(@db) WHILE @spid IS NOT NULL BEGIN SET @Kill = 'KILL ' + @spid EXEC(@Kill) SET @Tmp = @spid SET @spid = NULL SELECT @spid = Min(spid) FROM master.sys.sysprocesses WHERE dbid = DB_ID(@db) AND spid > @Tmp END Tags: sql server, database,...

Following on from my last post on "Auditing Schema Changes to SQL Server 2005 objects", here's a simple bit of code that will take the EVENTDATA XML and transform it back into rows (note you need to have already run Richard's trigger and table creation script): --EventData XML is element-centric. Below is an example, as elements will vary--depending on what the "event" actually is:/* <EVENT_INSTANCE>  <EventType>ALTER_TABLE</EventType>  <PostTime>2007-10-02T15:39:42.707</PostTime>  <SPID>71</SPID>  <ServerName>SERVER</ServerName>  <LoginName>DOMAIN\USERNAME</LoginName>  <UserName>dbo</UserName>  <DatabaseName>TestDatabase</DatabaseName>  <SchemaName>dbo</SchemaName>  <ObjectName>tblTest</ObjectName>  <ObjectType>TABLE</ObjectType>  <TSQLCommand>    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON"      QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />      <CommandText>        ALTER TABLE dbo.tblTest DROP CONSTRAINT DF_tblTest_Test      </CommandText>  </TSQLCommand></EVENT_INSTANCE>*/--just get back the necessary elements from the audit...

I recently needed to implement a lightweight, simple audit trail in SQL Server 2005. After googling and finding many, many ways, I settled on Richard's recent blog post over at GeekDojo titled "Super easy SQL Server 2005 Database Schema change auditing". Richard has posted a short, helpful script to audit data definition (CREATE, DROP, ALTER) SQL statements to a central table using a simple trigger and the XML data type. This means I can keep an audit trail of schema changes, along with all the information available (user name, date & time, SQL statement, etc.) I'm looking forward to working with this....

...I'd be using Red Gate's SQL Multi Script. The initial "alpha" version is available now for free. I've given it a whirl and it works exactly as advertised, with further improvements in the pipeline too. At first glance I thought a utility like this would be better off being built into SQL Server Management Studio. After using it (and reading on the forum what other users want it for), I reckon a small, stand-alone product is probably better. I've been advised that this alpha release will stop working at the end of 2007. Tags: sql server, database, script, server

The other day I passed Microsoft Exam 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000. This was the second exam on my way to world domination (and my MCAD.NET, which I first blogged about 2 years ago). Before I do the one exam remaining on XML Web Services for my MCAD, I'm thinking of taking a "side road" and doing the MCTS (Microsoft Certified Technology Specialist) for SQL Server 2005. I thought I'd do this quick diversion given that I recently studied up on SQL Server (albeit 2000), MCTS requires only 1 exam, and a while back I completed...

How many minutes are there between 4:30:00 (four thirty) and 4:31:59 (four thirty-one and 59 seconds)? I reckon the answer is "nearly two", but the best answer is probably the old classic "it depends" :-) Recently I was asked to check some code to ensure something does not take longer than 1 minute, given a start time and end time down to the second. The current code was T-SQL's DATEDIFF function, and when input the my example above, the answer was "1 minute": SELECT DATEDIFF(mi, '1/Jan/2000 4:30:00', '1/Jan/2000 4:31:59') Clearly, 1 minute and 59 seconds is longer than 1 minute. What gives? It turns out...

For three days last week I attended a Microsoft training course at Dimension Data Learning Services in St. Kilda (thanks boss for paying for this!) The course was 2779 Implementing a Microsoft SQL Server 2005 Database. The official curriculum covered: creating databases; data types; creating tables, views, stored procedures and functions; indexes and index tuning; XML; managed code (VB.NET or C# in the database, although we didn't cover creating the code, just implementing an assembly); and Service Broker. Along with 3 days of tutorial time, we also got a huge handbook and CD with course materials. The 9 topics were delivered...

In the continuing battle over database documentation, Jon Galloway has released a new .NET 2.0 weapon (with source!): Data Dictionary Creator Data Dictionary Creator collects extended properties/descriptions for SQL Server tables and columns and outputs the resulting documentation to a variety of formats. It's also easy to use, and did I mention the source is downloadable too? A tool I've used in the past for retroactively adding extended properties to views, stored procedures, and stored procedure parameters is the no-longer available XpressProps. Nowadays, I add extended properties to both the procedure and all parameters while scripting the object creation - here's some script...

Recently I had a need to examine IIS log files - lots of them - to do some usage analysis. Analog has been my tool of choice, and although it takes a bit of learning, when coupled with the right config settings will display helpful tables, charts and graphs for summary-type information (click for a larger picture): But when it came time to answer some more directed questions about web site usage, I thought the best way would be to import the IIS log files covering 2 years worth of data into SQL Server, and utilise my existing knowledge of T-SQL. There were...

I was listening to Greg Low's podcast SQLDownUnder show 13 with Bill Graziano, and my ears pricked up when Greg made the comment that most of the consulting he does is on improving performance, and posed this as a question back to Bill as to why...actually, I became interested because Greg mentioned that developer knowledge could be to blame - that's me :-) The first part of Bill's answer, and the part that stood out, was: "...[I]t's easy to grow data at a rate that we never could before. Back in the 80's...it was just so much harder to generate data with...

There was a question posted on the AUS-DOTNET mailing list a while back asking how people keep their SQL Server database schemas in sync between development and production servers. I reckon the tasks of: a) keeping your test server up-to-date with production changes and/or b) rolling changes from test to production in a controlled manner ...would have to be one of the greatest needs for developers (and this problem just screams for a tool/utility to take care of it). I've observed that some people have the solution of never using Enterprise Manager to do any design or make any changes and instead script everything;...