snimmaga
asked on
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,
Srini.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
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 ...
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
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 ...
ASKER
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....
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....
Thank you. I'll keep this in mind.
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'