By: Rob Fisch | Updated: 2007-11-21 | Comments (4) | Reporting Services Custom Report
Problem
Wouldn't it be great if some of the great DBA management queries you have found on MSSQLTips were available in a Reporting Services portal so that you could run them from anywhere? Imagine how convenient that would be. You could even share them with other DBAs, System Administrators and/or Developers within an organization as deemed appropriate. In this tip we will take a look at one of the management queries that is found on MSSQLTips.com and transformed into a pre-canned Reporting Services report.
Solution
The solution is to have many of the great scripts and queries found on MSSQLTips retrofitted as Reporting Services reports. This article hosts the first of what I hope to be a long series of companion downloadable reports to go along with the great blogs, articles and tips offered here are MSSQLTips. I will be working on converting as many as time permits.
On 10/11/2007, MSSQLTips published a tip on "Determining Free Space Per Database". The tip offers a SELECT statement that can be used to monitor database growth. I have converted this statement into a (set of) Report Services report(s). One report uses Windows Authentication. The other uses SQL Server authentication, which prompts you for those credentials. Both reports prompt for servername (in the event you want to rerun the report against another server).
(Note: These reports were created with the SQL Server 2005 client tools at SP2. If you have a pre-SP2 system and these files do not work, contact me and I'll try to help.)
Running the reports from the Desktop
If you have the SQL Server 2005 SP2 client tools loaded on your desktop you do not even have to deploy them. You can download the complete Report Designer solution and run it locally.
Download the solution here
Unzip the solution to a folder and open the FreeSpace.sln file from Visio Studio. From there just run the reports as desired.
Configuring and running the reports from a Reporting Services server
- Download these two reports and follow the specific instructions for your server type.
- Unzip the files to a temporary location.
- Configuring the Reporting Server
- Running the reports
- The Windows Authentication report - Unless you have Kerberos delegation configured, you will run into the the 'double hop' issue when running the reports remotely. You should be logged into the Report Server (via RDP or VNC or some other remote method) and use the Report Server's web browser. However, this situation is not appropriate for everyone, especially if you are sharing the reports with other staff that might not have access to the system.
- The PromptSQLAccount report - Use this directly from any workstation, however you will need to know the credentials of a SQL user account with access to the Master database.
Next Steps
- Look for more MSSQLTip conversions coming soon.
- Take a look at these other Reporting Services tips
Last Updated: 2007-11-21
About the author
Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.
View all my tips
Related Resources