CREATE PROC sp_free_drive_space @drive_name char(1)= `C`, 
	@freespace_limit int = 10240
AS
/* 
** Процедура для оценки свободного пространства на заданном диске (Кбайт)
*/ 
DECLARE @fs int, @drive int
DECLARE @freespace int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @char_freespace varchar(20), @char_freespace_limit varchar(20)

SELECT @fs=null,
	@drive=null

EXEC @hr = sp_OACreate `Scripting.FileSystemObject`, @fs OUT
IF @hr <> 0
BEGIN
	EXEC sp_OAGetErrorInfo @fs, @src OUT, @desc OUT 
	SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	goto destroy_objects
END

EXEC @hr = sp_OAmethod @fs, `GetDrive`, @drive OUT, @drive_name
IF @hr <> 0
BEGIN
	EXEC sp_OAGetErrorInfo @fs, @src OUT, @desc OUT 
	SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	goto destroy_objects
END

EXEC @hr = sp_OAGetProperty @drive, `FreeSpace`, @freespace OUT
IF @hr <> 0
BEGIN
	EXEC sp_OAGetErrorInfo @drive, @src OUT, @desc OUT 
	SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	goto destroy_objects
END
SET @freespace = @freespace/1024
SET @char_freespace = CAST (@freespace as varchar(20))
SET @char_freespace_limit = CAST (@freespace_limit as varchar(20))
If (@freespace < @freespace_limit)
RAISERROR (60001, 10, 1, @drive_name, @char_freespace, @char_freespace_limit)with log
destroy_objects:

if @drive is not null
begin
	EXEC @hr = sp_OADestroy @drive
	IF @hr <> 0
	BEGIN
		EXEC sp_OAGetErrorInfo @drive, @src OUT, @desc OUT 
		SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	END
end
if @fs is not null
begin
	EXEC @hr = sp_OADestroy @fs
	IF @hr <> 0
	BEGIN
		EXEC sp_OAGetErrorInfo @fs, @src OUT, @desc OUT 
		SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	END
end
RETURN (0)
GO

Поделитесь материалом с коллегами и друзьями