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