CODEWITHSUNDEEP
sql-server-2008ssrs-2008-r2diskspace
NonProgrammer
NonProgrammer

Reputation: 1387

Get space related information from multiple servers

I need to create an SSRS report that would display space related information for all of our servers and associated databases.

Is there a way to create a stored procedure that would loop through all servers and write space information to a temp table?

Or is it possible to do it via SSRS report where based on parameter chosen, it connects to a specific data source, and gets the space related information?

Upvotes: 0

Views: 108

Answers (1)

Louie
Louie

Reputation: 538

I need to create an SSRS report that would display space related information for all of our servers and associated databases. Is there a way to create a stored procedure that would loop through all servers and write space information to a temp table?

It would be a bit cumbersome, however you can make use of linked servers and dynamic SQL to loop each server and store results in a base table. You can then use the base table as a datasource for your SSRS report.

A better way to do it would be to use a powershell script to loop through each database server, store the results in your database reporting server and utilize an SSRS datasource to access the records from the reporting server.

Or is it possible to do it via SSRS report where based on parameter chosen, it connects to a specific data source, and gets the space related information?

You can make use of an SSRS dynamic datasource to connect to a specific data source and execute SQL to get space related information - however this would only return capacity information on one server. Here is how to work with dynamic datasources: http://blog.rdx.com/blog/dba_tips/2014/02/creating-dynamic-data-sources-in-ssrs

Best way to do it as above would be to have a single central reporting server as a central data store, which queries each Database Server and stores the data. You can then point your report to the central datasource

Upvotes: 1

Related Questions