Table function to retrieve disk properties

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Table function to retrieve disk properties

Author

Topic

PABluesMan
Starting Member

26 Posts

Posted - 2008-03-26 : 12:34:19

Here ya go. The advantage of this is that it does not rely on [xp_cmdshell] or any external command-line utilities. Enjoy!

/*******************************************************************************/
CREATE FUNCTION fnc_GetDriveSizes (@Drive CHAR (1))

RETURNS @Drives TABLE (Drive CHAR (1), TotalMB MONEY, FreeMB MONEY)

AS
BEGIN
DECLARE @FSO INT
DECLARE @hr INT
DECLARE @oDrive INT
DECLARE @RawSize VARCHAR (20)
DECLARE @RawFree VARCHAR (20)
DECLARE @Size DECIMAL (32, 2)
DECLARE @Free DECIMAL (32, 2)

DECLARE @DI TABLE (
Drive VARCHAR (1),
TotalMB DECIMAL (32, 8),
FreeMB DECIMAL (32, 8)
)

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @FSO OUT

EXEC @hr = sp_OAMethod @FSO, 'GetDrive', @oDrive OUT, @Drive

EXEC @hr = sp_OAGetProperty @oDrive, 'TotalSize', @RawSize OUT

EXEC @hr = sp_OAGetProperty @oDrive, 'AvailableSpace', @RawFree OUT

SET @Size = CONVERT (BIGINT, @RawSize) / 1048576.0
SET @Free = CONVERT (BIGINT, @RawFree) / 1048576.0

EXEC @hr = sp_OADestroy @FSO

INSERT @Drives (Drive, TotalMB, FreeMB)
VALUES (@Drive, @Size, @Free)

RETURN
END
/*******************************************************************************/

I geek, therefore I am

jordanam
Yak Posting Veteran

62 Posts

Posted - 2008-04-30 : 13:18:42

But it does require allowing OLE automation procedures, at least according to my error log.

Msg 15281, Level 16, State 1, Procedure sp_OACreate, Line 1
SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OAMethod, Line 1
SQL Server blocked access to procedure 'sys.sp_OAMethod' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OAGetProperty, Line 1
SQL Server blocked access to procedure 'sys.sp_OAGetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OAGetProperty, Line 1
SQL Server blocked access to procedure 'sys.sp_OAGetProperty' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.
Msg 15281, Level 16, State 1, Procedure sp_OADestroy, Line 1
SQL Server blocked access to procedure 'sys.sp_OADestroy' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources