By: Atul Gaikwad | Updated: 2016-06-30 | Comments | Related: > SQL Server and Oracle Comparison
Problem
I saw your previous tip (How to Automate Oracle Table Space Report from SQL Server) and want to find out how to create a SQL Server Reporting Services (SSRS) report with a subscription to improve my visibility into Oracle tablespace data. Can you show me how to build this report?
Solution
In Part - I of this tip series, we saw how to create a SQL Server Linked Server to Oracle and pull tablespace information into a SQL Server database table through a SQL Server Agent Job. In this tip we will create an SQL Server Reporting Services (SSRS) report for Oracle tablespace and schedule the report to run via an SSRS subscription to deliver the report to your inbox every 8 hours.
Create an SSRS report for Oracle Tablespace Information
1. Open BIDS (Business Intelligence Development Studio) and select New SSRS project.
2. Name the project Oracle_Health_check.
3. In Solution Explorer, right click on the Reports folder and select Add New Report. Name the report - TableSpaceReport.
4. In Solution Explorer, right click on the Shared Data Sources folder and select Add New Data Source, as shown below:
5. On the Shared Data Source Properties interface for the General tab, enter the name of the data source, the Type and in the Connection String box the Server Name and Database name (i.e. Initial Catalog) where you have tablespace information.
6. Verify that the data source is created successfully.
7. Go to View and Select Report Data. Now you will see Report data window at the left side.
8. Click new at the top of the window and select New Data Source.
9: Data Source window: give some logical name like I gave oracle, select Use Shared Data source Reference. Here we are going to use the shared data source we created earlier. Select the shared data source from drop down and click ok.
10: Now you will see your data source in report data window. Right click your data source and select add Dataset then add the appropriate name. I left it default as dataset. Select the data source from the drop down, select query type as text and in the query window enter the query to pull information for your instance from the Tablespace report as shown below:
11: Repeat the same steps for creating another dataset for the oracle_health table.
12: Now your report data should look like this:
13: Now let's create a report to display the Oracle tablespace details and oracle health status. Go to the toolbox and drag-n-drop the table into the report, it should have 2 rows and 5 columns as shown below:
14: Go to the report data and drag-n-drop fields from the dataset to the table as below:
15: Now, let's do some conditional formatting to the table based on the free space percentage for a better understanding of the report.
16: Click on the PctFree column in the table and open properties, select BackgroundColor and choose Expression as below:
17: In the Expression window enter the code below, to highlight the report row if the free space percentage is below the threshold. I set it to 5% here, you can change this as per your need.
18: From the Toolbox select a Text Box, and drag it to the top of table for the Instance Name, Open Mode, Log Mode. Next drag the values from Dataset2 against the respective text box.
19: After, do some cosmetic changes for a good looking report like background color and report header. Now your report should similar to the one below.
20: Go to preview and view your final report. It should be look like the sample below.
You can see Master_data_4m has free percentage below 5%, hence it has been highlighted so you can take appropriate action before it creates an issue.
Deploy the Oracle Tablespace SSRS report to the Report Server
Now, we have report ready for the Oracle Tablespace. Let's go ahead and deploy to your SQL Sever Report Server.
1: In Solution Explorer, right click the project and select properties.
2: In the property window, update the parameters for the TargetReport folder under which you want to deploy report and enter the Target Server URL as shown below:
3: Click Apply and Ok. Save the project.
4: Right click project in the Solution Explorer and click Deploy.
5: In the output window, you will see the below message after the report is deployed successfully.
Deploy complete -- 0 errors, 0 warnings
========== Build: 1 succeeded or up-to-date, 0 failed, 0
skipped ==========
========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========
Setup SSRS report subscription to deliver report to every day
1: Connect to your report server. i.e. https://<your Report Server>/Reports/Pages/Folder.aspx
2: Click on the Oracle TableSpaceReport (or any report you want to schedule).
3: In reports header, click on Subscription and Create New Subscription.
4: Enter the details as shown below. This you can change as per your needs.
5: Select the schedule as per your needs.
Now you have setup a subscription to the TableSpace report and you will start receiving email alerts in the form of Oracle Table space status. Now you can take proactive action to increase table space before free space percentage goes below threshold which should prevent any outages from occurring.
Next Steps
About the author
Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-06-30
