April 8, 2004 at 5:16 am
Hi all,
I am trying to write code that will give detail about the Server's drives. The code below works up to a point, but I cannot retrieve the information returned in the collection of drives (see commented section). I have a working version of this in VB Script, but I need this to run from T-SQL or sp. The xp_availablemedia sp does not provide what I need.
Anybody that can help?
Thanks.
Errol.
DECLARE @rc int , @iCount int, @idx int
DECLARE @objFS int, @objDC int, @objDrv int
DECLARE @val varchar(255), @src varchar(255), @desc varchar(255)
EXECUTE @rc = sp_OACreate 'Scripting.FileSystemObject' , @objFS OUTPUT
if @rc = 0
begin
print '@objFS created successfully...'
EXEC @rc = sp_OAGetProperty @objFS, 'drives' , @objDC out
if @rc = 0
begin
print 'Drive Collection built...'
EXEC @rc = sp_OAGetProperty @objDC, 'count', @iCount out
if @rc = 0
begin
print 'Number of Drives in Collection = ' + convert(char(15), @icount)
-- while -- For Each @objDrv in @objDC
-- begin
-- Get:
-- @objDrv.DriveLetter
-- @objDrv.DriveType (0,1,2,3,4,5)
-- @objDrv.VolumeName or @objDrv.ShareName
-- @objDrv.TotalSize/1024
-- @objDrv.AvailableSpace/1024
-- end
end
else
BEGIN
EXEC sp_OAGetErrorInfo @objDC, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc
RETURN
END
end
else
BEGIN
EXEC sp_OAGetErrorInfo @objFS, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@RC), Source=@src, Description=@desc
RETURN
END
end
else
print 'No FS object'
print @val
exec sp_OADestroy @objDrv
exec sp_OADestroy @objFS
print '--- SP Ended ---'
?
April 8, 2004 at 6:47 am
Have you tried using DTS instead? You can keep using your vb script and you can execute a DTS package from a SP. I used this once to collect info about drives on other servers (using vb + wmi). Works well.
sp_OA procedures won't work with any COM. Depends on the datatypes involved.
April 13, 2004 at 3:42 am
Thanks for the promt reply. I will give it a try via DTS.
?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply