Table of Contents
- Microsoft SQL Server Defined
- Microsoft SQL Server Features
- Microsoft SQL Server Administration
- Microsoft SQL Server Programming
- Performance Tuning
- Choosing the Back End
- The DBA's Toolbox, Part 1
- The DBA's Toolbox, Part 2
- Scripting Solutions for SQL Server
- Building a SQL Server Lab
- Using Graphics Files with SQL Server
- Enterprise Resource Planning
- Customer Relationship Management (CRM)
- Building a Reporting Data Server
- Building a Database Documenter, Part 1
- Building a Database Documenter, Part 2
- Data Management Objects
- Data Management Objects: The Server Object
- Data Management Objects: Server Object Methods
- Data Management Objects: Collections and the Database Object
- Data Management Objects: Database Information
- Data Management Objects: Database Control
- Data Management Objects: Database Maintenance
- Data Management Objects: Logging the Process
- Data Management Objects: Running SQL Statements
- Data Management Objects: Multiple Row Returns
- Data Management Objects: Other Database Objects
- Data Management Objects: Security
- Data Management Objects: Scripting
- Powershell and SQL Server - Overview
- PowerShell and SQL Server - Objects and Providers
- Powershell and SQL Server - A Script Framework
- Powershell and SQL Server - Logging the Process
- Powershell and SQL Server - Reading a Control File
- Powershell and SQL Server - SQL Server Access
- Powershell and SQL Server - Web Pages from a SQL Query
- Powershell and SQL Server - Scrubbing the Event Logs
- SQL Server 2008 PowerShell Provider
- SQL Server I/O: Importing and Exporting Data
- SQL Server I/O: XML in Database Terms
- SQL Server I/O: Creating XML Output
- SQL Server I/O: Reading XML Documents
- SQL Server I/O: Using XML Control Mechanisms
- SQL Server I/O: Creating Hierarchies
- SQL Server I/O: Using HTTP with SQL Server XML
- SQL Server I/O: Using HTTP with SQL Server XML Templates
- SQL Server I/O: Remote Queries
- SQL Server I/O: Working with Text Files
- Using Microsoft SQL Server on Handheld Devices
- Front-Ends 101: Microsoft Access
- Comparing Two SQL Server Databases
- English Query - Part 1
- English Query - Part 2
- English Query - Part 3
- English Query - Part 4
- English Query - Part 5
- RSS Feeds from SQL Server
- Using SQL Server Agent to Monitor Backups
- Reporting Services - Creating a Maintenance Report
- SQL Server Chargeback Strategies, Part 1
- SQL Server Chargeback Strategies, Part 2
- SQL Server Replication Example
- Creating a Master Agent and Alert Server
- The SQL Server Central Management System: Definition
- The SQL Server Central Management System: Base Tables
- The SQL Server Central Management System: Execution of Server Information (Part 1)
- The SQL Server Central Management System: Execution of Server Information (Part 2)
- The SQL Server Central Management System: Collecting Performance Metrics
- The SQL Server Central Management System: Centralizing Agent Jobs, Events and Scripts
- The SQL Server Central Management System: Reporting the Data and Project Summary
- Time Tracking for SQL Server Operations
- Migrating Departmental Data Stores to SQL Server
- Migrating Departmental Data Stores to SQL Server: Model the System
- Migrating Departmental Data Stores to SQL Server: Model the System, Continued
- Migrating Departmental Data Stores to SQL Server: Decide on the Destination
- Migrating Departmental Data Stores to SQL Server: Design the ETL
- Migrating Departmental Data Stores to SQL Server: Design the ETL, Continued
- Migrating Departmental Data Stores to SQL Server: Attach the Front End, Test, and Monitor
- Tracking SQL Server Timed Events, Part 1
- Tracking SQL Server Timed Events, Part 2
- Patterns and Practices for the Data Professional
- Managing Vendor Databases
- Consolidation Options
- Connecting to a SQL Azure Database from Microsoft Access
- SharePoint 2007 and SQL Server, Part One
- SharePoint 2007 and SQL Server, Part Two
- SharePoint 2007 and SQL Server, Part Three
- Querying Multiple Data Sources from a Single Location (Distributed Queries)
- Importing and Exporting Data for SQL Azure
- Working on Distributed Teams
- Professional Development
- Application Architecture Assessments
- Business Intelligence
- Tips and Troubleshooting
- Additional Resources
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:
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.
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.