Home > Articles > Data > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

Reporting Services - Creating a Maintenance Report

Last updated Mar 28, 2003.

Reporting Services - Creating a Maintenance Report

As database administrators or developers, we sometimes get so busy with what we have to get done that we don't have time to become familiar with all of the features and functions within the packages we work on. In many shops, I've seen DBAs and developers quickly learn a feature to implement it for their users, without considering that the feature might actually help them save some time. And in most work environments, time is something you always need more of. Today I'll show you how to use a feature that is available for both SQL Server 2000 and 2005, and how you can leverage this feature to help you display data that you care about from your system. If you're an administrator, this tutorial will guide you through a practical application, and if you're a developer, you can change a query or two to make this feature available in your applications.

In previous tutorials, I've explained how to create a web page that shows the history of your maintenance plans in SQL Server 2000, and in another I showed you how to create the same kind of report using a scripting language called PowerShell. I mentioned that one of the nice things about Microsoft software is that there is usually more than one way to do something, and this kind of data is no different. In this tutorial, I'll show you how to create that same report information using Reporting Services.

I've explained the general background for Reporting Services in a previous tutorial, for both SQL Server 2000 and 2005. That article provides a general overview of the feature, so if you are unfamiliar with the concepts of this tool, you may want to take a look at that article first. You'll have to install the feature for SQL Server 2000, and in 2005 you'll have to configure it to allow access to your users. In both cases there are some requirements for Internet Information Services, and the setup for that depends a great deal on the version of operating system you're using. For the full requirements and installation instructions, see the references at the end of this tutorial. I'll assume you have Reporting Services up and running for this article.

So let's jump right in. Having installed, configured and tested your Reporting Services system, open the SQL Server Business Intelligence Development Studio, or BIDS. This tool serves as the Reporting Services design environment, although newer versions of SQL Server include a web-based tool, and you can always just write the report using an XML-like language called Reporting Definition Language, or RDL. For this tutorial, I'll stick with BIDS. You can follow along with my example on a test system of your own or just follow the screens here.

Once the tool is open, I click File and then Create new project. Under Business Intelligence Projects I'll select Report Server Project Wizard from the menu that appears.

I'll then enter a name, location and solution name for this project of "Maintenance Plan History Report." Having done that, I click the OK button.

The Wizard explains what is about to happen, so I'll bypass that with a click of the Next button. The first real entry panel brings up connection information, where I enter the name of the server and database as well as my credentials for the system I'm interested in monitoring.

I only have to click the Edit... button to select the server, since I'll connect with my current credentials. Remember, we're working with the msdb database in this example, so that's the database I'll pick.

Clicking Next brings me to the panel that allows me to save the connection, while also displaying the connection string the previous step created. If I select Make this a shared data source, this information will also be stored in the report's definition, in the RDL I mentioned earlier. I'll do that here.

Clicking Next here brings me to the Query Designer panel, which is where I'll enter the SQL Server 2005 version of the two queries I mentioned in those previous articles for getting at the contents of the Maintenance Plan history.

Here is the script that shows the Maintenance Plan history for SQL Server 2000:

/* Read the Maintenance Plan history for SQL Server 2000 */
SELECT 'Succeeded: ' + CAST(succeeded AS VARCHAR(1))
,'Completed on: ' + CAST(end_time AS VARCHAR(11)) 
,'Database Name: ' + database_name 
,'Activity: ' + activity 
,'Duration: ' + CAST(duration as varchar(1)) 
,message 
FROM sysdbmaintplan_history 
WHERE DATEDIFF(day, end_time, getdate()) < 1 
ORDER BY succeeded, database_name

And here is the script to read the Maintenance Plan history for a SQL Server 2005 system:

/* Read the Maintenance Plan history for SQL Server 2005 */

SELECT 
mpd.server_name AS [ServerName], 
mpd.line1 AS [Name], 
mpd.line2 + ', ' + mpd.line3 + ', ' + mpd.line4 AS [Description], 
mpd.start_time AS [Start Time], 
mpd.end_time AS [End Time], 
mpd.error_number AS [Error Number], 
mpd.error_message AS [Error Message], 
mpd.command AS [Command], 
mpd.succeeded AS [Succeeded] 
FROM msdb.dbo.sysmaintplan_plans AS s 
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp 
  ON sp.plan_id=s.id 
INNER JOIN msdb.dbo.sysmaintplan_log AS spl 
  ON spl.subplan_id=sp.subplan_id 
INNER JOIN msdb.dbo.sysmaintplan_logdetail AS mpd 
  ON mpd.task_detail_id=spl.task_detail_id
ORDER BY [Start Time] DESC

Clicking the Next button here moves me to the panel where I set the layout for the report.

I'll add the server name at the Page level and place the rest of the fields in the Details area. You can add different groupings if you wish.

I'll just leave the defaults for the rest of the layout screens (you can play with this later on your test system if you wish) and click Next through the next screen that deals with layout.

In the panel marked Choose the Deployment Location, I set the name of my test server where Reporting Services is installed, and also the share location on the server where the report will be stored. Note that on your system you want to ensure this information is accurate — it won't cause this step to fail, but the report won't process in the next step.

The next panel sets the name of the report, and finally, I click the Finish button to see the completed report design. All that is left is to save the report (File then Save) and then to click the Build and then Deploy menu options.

If all goes well, the report will now be available by typing the URL to the system in a web browser:

http://YourServerName/Reports

The beautiful thing about this quick tutorial is that you don't have to limit yourself to creating a report for this simple query — you can display any information that your users have access to, including them in your applications, or placing them on a portal for your administrative staff.

And here is an interesting side-benefit: if you are using SQL Server 2005 Service Pack 2, you're able to use this report right inside SQL Server Management Studio. Simply open Management Studio, right-click the name of the server, and select Reports and then Custom Reports from the menu that appears. Find the name of the report you just created, and then answer Run to the warning that appears. Voila! You'll have this handy maintenance report right inside your management tool.

InformIT Articles and Sample Chapters

If you need more information on installing and configuring Reporting Services, check out this free book excerpt.

Online Resources

The SQL Server 2005 version of Reporting Services I used for this tutorial is an incredible feature that does much more than I've covered. Check out this link for more info.