/ Published in: SQL
This is not my code but I really like the fact that it's not using a COM object to do it's task such as the one I've been using for years.
Expand |
Embed | Plain Text
Copy this code and paste it in your HTML
-- SQL 2005/2008 --------------------------------------------------------------------------- CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree INT, @Drive CHAR(1) AS /* ---------------------------------------------------------------------------- -- Object Name: dbo.spExec_SufficientDiskSpace -- Project: Admin Scripts -- Business Process: Monthly Sales Reports -- Purpose: Validate sufficient disk space -- Detailed Description: Validate sufficient disk space based on based on the -- @MBfree and @Drive parameters -- Database: Admin -- Dependent Objects: master.sys.xp_fixeddrives -- Called By: Admin Scripts -- Upstream Systems: Unknown -- Downstream Systems: Unknown -- -------------------------------------------------------------------------------------- -- Rev | CMR | Date Modified | Developer | Change Summary -------------------------------------------------------------------------------------- -- 001 | N\A | 03.05.2009 | MSSQLTips | Original code -- */ SET NOCOUNT ON -- 1 - Declare variables DECLARE @MBfree INT DECLARE @CMD1 VARCHAR(1000) -- 2 - Initialize variables SET @MBfree = 0 SET @CMD1 = '' -- 3 - Create temp tables CREATE TABLE #tbl_xp_fixeddrives (Drive VARCHAR(2) NOT NULL, [MB free] INT NOT NULL) -- 4 - Populate #tbl_xp_fixeddrives INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free]) EXEC master.sys.xp_fixeddrives -- 5 - Initialize the @MBfree value SELECT @MBfree = [MB free] FROM #tbl_xp_fixeddrives WHERE Drive = @Drive -- 6 - Determine if sufficient fre space is available IF @MBfree > @MinMBFree BEGIN RETURN END ELSE BEGIN RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1) END -- 7 - DROP TABLE #tbl_xp_fixeddrives DROP TABLE #tbl_xp_fixeddrives SET NOCOUNT OFF GO -- SQL 2000 -------------------------------------------------------------------------------- CREATE PROCEDURE dbo.spExec_SufficientDiskSpace @MinMBFree INT, @Drive CHAR(1) AS /* ---------------------------------------------------------------------------- -- Object Name: dbo.spExec_SufficientDiskSpace -- Project: Admin Scripts -- Business Process: Monthly Sales Reports -- Purpose: Validate sufficient disk space -- Detailed Description: Validate sufficient disk space based on based on the -- @MBfree and @Drive parameters -- Database: Admin -- Dependent Objects: master.sys.xp_fixeddrives -- Called By: Admin Scripts -- Upstream Systems: Unknown -- Downstream Systems: Unknown -- -------------------------------------------------------------------------------------- -- Rev | CMR | Date Modified | Developer | Change Summary -------------------------------------------------------------------------------------- -- 001 | N\A | 03.05.2009 | MSSQLTips | Original code -- */ SET NOCOUNT ON -- 1 - Declare variables DECLARE @MBfree INT DECLARE @CMD1 VARCHAR(1000) -- 2 - Initialize variables SET @MBfree = 0 SET @CMD1 = '' -- 3 - Create temp tables CREATE TABLE #tbl_xp_fixeddrives (Drive VARCHAR(2) NOT NULL, [MB free] INT NOT NULL) -- 4 - Populate #tbl_xp_fixeddrives INSERT INTO #tbl_xp_fixeddrives(Drive, [MB free]) EXEC master.dbo.xp_fixeddrives -- 5 - Initialize the @MBfree value SELECT @MBfree = [MB free] FROM #tbl_xp_fixeddrives WHERE Drive = @Drive -- 6 - Determine if sufficient fre space is available IF @MBfree > @MinMBFree BEGIN RETURN END ELSE BEGIN RAISERROR ('*** ERROR *** - Insufficient disk space.', 16, 1) END -- 7 - DROP TABLE #tbl_xp_fixeddrives DROP TABLE #tbl_xp_fixeddrives SET NOCOUNT OFF GO
URL: http://www.mssqltips.com/tip.asp?tip=1706