Extended stored procedure to get disk free space?? Help..

Hey folks,

Here is one for you..
Is there a way from a stored proc, to get the free space available on a drive?  Something like an Extended Proc.
I know that I can outpur DIR to a text file and parse the result set.  But I want a cleaner way.

As a matter of fact, is there an extended proc to which I can pass a Win32 API call and get the results back to my stored proc?

Please get back to me at srini.vasa@mckesson.com.

Thank you,
Who is Participating?
mitekConnect With a Mentor Commented:
of course. here it is:

EXEC master..xp_fixeddrives

if you need to parse results, then:

CREATE TABLE drives(drive char(1),freespace varchar(12) NULL)

INSERT INTO #temp EXEC master..xp_fixeddrives

SELECT * FROM #temp WHERE drive = 'C'


CREATE TABLE #drives(drive char(1),freespace varchar(12) NULL)

INSERT INTO #drives EXEC master..xp_fixeddrives

SELECT CONVERT(int,freespace) FROM #drives WHERE drive = 'C'

snimmagaAuthor Commented:
Perfect, Mike...
Could you please let me know where to find the usage for these procedures?  I would like to know what parameters to send and what to expect..
I'll evaluate once I rec've your comment back..
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

xp_fixeddrives has no parameters and returns two columns -- drive letter and free space in megabytes on that drive.

by and large, these procedures are undocumented. i wasn't able to find any information about some of them, nowhere. in a way, it's a hack, but it works both in 6.5 and 7.0, so it's at least a compatible hack.

there are many interesting things in master database in sql server. for example, completely undocumented stored procedures to read/write nt registry, and things like that.

as for your last question, i'm not sure what you call "api call". there are two ways to do that in sql server -- a simple way and a fancy way. simple way would be to use xp_cmdshell, pass a command-line string, and parse the stdout output of the command (and return code)

a fancy way would be to use sp_OACreate, sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy functions to instantiate an OLE object, call it's methods, and so on.
for example, i was able instantiate both scripting.filesystemobject and scripting.dictionary objects, to read/write files on a hard drive, or to keep a OLE-driven hash table in memory.

yet even a fancier way would be to write an extended stored procedure yourself, but i've never been there, so there is no point of talking about it from my part ...

snimmagaAuthor Commented:
Thanks mitek,
That was of good help.  What I meant by my last question was that, if I can form an interface from SQL Server to tap on the API power of windows.  For example, is there a way to do stuff as we can from VB.  That is, declare an API function and pass it parameters and receive the output.  Through xp_cmdshell, all you can do is execute shell commands.  But, the output of this cannot be returned back to SQL Server unless you redirect the o/p to a text file and read it.  Through API, possibly we can do this.  

Anyway, thanks again..
Ps: If you have any questions regarding SQL Server, you can forward them to me directly to abbulu@hotmail.com.  I'll be more than glad to help....

Thank you. I'll keep this in mind.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.