Haidong Ji,

Try the next script (no e-mail part attached but it schoudn't be that difficult to add it);

CREATE PROCEDURE [dbo].[sp_diskspace] AS

/*

Displays the free space,free space percentage

plus total drive size for a server

*/

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 (

drive char(1) PRIMARY KEY,

FreeSpace int NULL,

TotalSize int 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

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

SELECT

drive,

FreeSpace as 'Free(MB)',

TotalSize as 'Total(MB)',

CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'

FROM #drives

ORDER BY drive

DROP

TABLE #drives

RETURN

8888888888888888888888888888888888888888888888888888888888888888888

Select the outcome from all your serers (using a databaselink) into a local "gathering-table"

create

table Disk_Capp (Servername varchar(40), Volume varchar(5),FreeMb int, TotalMb int, FreePCT integer, date datetime, runnum integer)

--The @runnum is a counter for each time you execute this script to keep track on history in the DISK_CAPP table

SET @Query ='INSERT Disk_Capp SELECT '''+@servername+''',*,cast(getdate() as varchar(20)),'+cast(@runnum as varchar(10))+' FROM OPENQUERY( [DBA_'+@servername+'],''SET FMTONLY OFF;EXEC master.dbo.sp_diskspace'')'

exec ( @Query )

8888888888888888888888888888888888888888888888888888888888888888888

Mention that the you are authorized to use the SP_OAxxxxx procedures (open and close using the DBlinks 1 and 0 )

SET @Query_c1 ='declare @var1 varchar(3000)

set @var1 = ''EXEC [DBA_'+@servername+'].master.dbo.sp_configure ''show advanced options'',1 go reconfigure go''

exec (@var1)'

exec (@Query_c1)

SET @Query_c2 ='declare @var2 varchar(3000)

set @var2 = ''EXEC [DBA_'+@servername+'].master.dbo.sp_configure ''Ole Automation Procedure'',1 go reconfigure go''

exec (@var2)'

exec (@Query_c2)

 8888888888888888888888888888888888888888888888888888888888888888888

Hope you (or someone else) can use it. . .

GKramer

The Netherlands