Executing sp_OAMethod on a linked server

[DBAWinnipeg]

Good Morning

I'm trying to write a neat little script that will go through all my linked servers and get me the free space and total space on the drives.

so I can run this statement fine
EXEC LINKED_SERVER.master.dbo.xp_fixeddrives

but when I start trying to do this type thing
EXEC @hr= LINKED_SERVER.master.dbo.sp_OACreate 'Scripting.FileSystemObject',@fso OUT

EXEC @hr = EXEC @hr = CCLEPOSTTST.master.dbo.sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive.master.dbo.sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive


I get this message

Server: Msg 10019, Level 16, State 1, Line 36
sp_OACreate has not yet been called successfully for this command batch.

but the line that executes the sp_OACreate doesn't error out at all....

any ideas?





Thanks in advance!!!

Colin in da 'Peg

 

[DBAWinnipeg]

Got it working

Here is the code if anyone is interested

just replace @ServerName with whatever you want...




DECLARE @ServerName sysname
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint

SET @MB = 1048576

SET @ServerName = 'CCLEPOSTTST'

IF @ServerName is null or @ServerName =''
BEGIN
SELECT @ServerName = @@servername + '.master.dbo.xp_fixeddrives'
END
ELSE
SELECT @ServerName = @ServerName + '.master.dbo.xp_fixeddrives'

CREATE TABLE #drives (ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)

INSERT #drives(drive,FreeSpace)
EXEC @ServerName

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


DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives ORDER by drive

OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

UPDATE #drives
SET TotalSize=@TotalSize/@MB,
ServerName = replace( @ServerName , '.master.dbo.xp_fixeddrives',''),
FreespaceTimestamp = convert(char(10),getdate(),101)
WHERE drive = @drive

FETCH NEXT FROM dcur INTO @drive
END

CLOSE dcur
DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso

SELECT ServerName, drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
FreespaceTimestamp
FROM #drives
ORDER BY drive

DROP TABLE #drives



Thanks in advance!!!

Colin in da 'Peg