December 17, 2009 at 10:06 pm
Hi Friends,
I m building a diskspace check report for multiple servers,
for that i created one table(Stores drive & space info) ,one SP(calculates disk space) on local & remote server.
For remote server i created linked server on my Local sql machine, but when i run report it shows diskspace details of only one server, but i want to view report of my local as well as remote server in single page.
How to achieve this?
Attached is Reporting Server project 'DiskSpace'
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
December 17, 2009 at 10:21 pm
Please also the provide stored procedure and table structures.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 18, 2009 at 12:33 am
CirquedeSQLeil (12/17/2009)
Please also the provide stored procedure and table structures.
Table
---------------
create table ServerDrivestatus
( servername varchar(150),drive varchar(10),totalMB float ,
FreeMB float,PercentFreeMB float, FreeSpaceDate smalldatetime)
SP
----------
create PROCEDURE sp_cloverdiskspace
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB, ServerName = @@servername, FreespaceTimestamp = (GETDATE())
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
insert into MASTER.dbo.ServerDrivestatus SELECT top 100 percent ServerName,
drive,
TotalSize as 'Total(MB)',
FreeSpace as 'Free(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
FreespaceTimestamp
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
December 18, 2009 at 8:44 am
Please post the table structure for the tables referenced in the proc.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 19, 2009 at 8:01 am
i have already posted the table structure (ServerDrivestatus).
in proc. ServerDrivestatus & #drive are the tables used.
#drive structure is in proc only
what r other table structures u want?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
December 19, 2009 at 9:13 am
My apologies for that. When I scrolled down my browser jumped past the table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2009 at 10:31 am
The server that reports back, is it consistently the same server?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2009 at 10:39 am
What I am seeing in this process, is that there is no method to call a list of servers. There needs to be a method to connect to each of the servers and gather this information.
You say you have created a linked server for each of the servers, what methods have you tried to use the linked servers, in order to process this script against each of them?
One possibility is to create a table that contains the information and then use a looping mechanism that queries the table for the parameters and then runs this script using those parameters for each server.
Personally, I use SSIS to perform this kind of task. IN SSIS, you can use a FOREACH LOOP and then change the connection string using an object variable and expressions to overwrite the value in the variable.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply