Врезка 1

Листинг 1. Оценка свободного пространства на диске.

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

Листинг 2. Оценка свободного пространства в файле.

CREATE PROC sp_free_file_space @file_name varchar(255)= null, 
	@file_spec varchar(255) = null, @freespace_limit int = 1024
AS
/* 
** Процедура для оценки свободного пространства в заданном файле (Кбайт)
*/ 
DECLARE @fs int, @file int
DECLARE @freespace int, @size int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @char_freespace varchar(20), @char_freespace_limit varchar(20)

SELECT @fs=null,
	@file=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, `GetFile`, @file OUT, @file_spec
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 @file, `Size`, @size OUT
IF @hr <> 0
BEGIN
	EXEC sp_OAGetErrorInfo @file, @src OUT, @desc OUT 
	SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
	goto destroy_objects
END
SET @freespace = @size/1024 - (select fileproperty (@file_name,`SpaceUsed`)*8)
SET @char_freespace = CAST (@freespace as varchar(20))
SET @char_freespace_limit = CAST (@freespace_limit as varchar(20))

If (@freespace < @freespace_limit)
RAISERROR (60002, 10, 1, @file_spec, @char_freespace, @char_freespace_limit ) 
	with log
destroy_objects:
if @file is not null
begin
	EXEC @hr = sp_OADestroy @file
	IF @hr <> 0
	BEGIN
		EXEC sp_OAGetErrorInfo @file, @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