Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 1999-07-22
6
Medium Priority
?
253 Views
Last Modified: 2008-02-01
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,
Srini.
0
Comment
Question by:snimmaga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 4

Accepted Solution

by:
mitek earned 800 total points
ID: 1096642
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'


0
 
LVL 4

Expert Comment

by:mitek
ID: 1096643
correction:

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'

0
 
LVL 5

Author Comment

by:snimmaga
ID: 1096644
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..
Thanks..
I'll evaluate once I rec've your comment back..
Srini.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 4

Expert Comment

by:mitek
ID: 1096645
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 ...


0
 
LVL 5

Author Comment

by:snimmaga
ID: 1096646
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..
Srini.
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....

0
 
LVL 4

Expert Comment

by:mitek
ID: 1096647
Thank you. I'll keep this in mind.

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question