Unheard of sp_OA error
-
Hi all,
I sometimes get this error:
OLE Automation Error Information (10077)
Source: ODSOLE Extended Procedure
Description: NULL
and haven't been able to identify the cause. KB and Google searches returned nothing
Anybody have a clue?
The stored proc only checks the size of a folder and works "most of the time" (SQL2000SP3/Win2003). Here's a simplified copy:
CREATE PROCEDURE FolderGetSize
@vcFolderPath varchar(8000),
@lFolderSize int OUT,
@vcErrTxt varchar(8000) OUT
as
/*
Returns the size (in bytes) of a folder
@vcFolderPath = folder path
@lFolderSize = returned size
@vcErrTxt = Error msg (NULL is successful)
Note: Won't work at the root of a drive (ex: "C:\")
Example:
declare @lFolderSize int, @vcErrTxt varchar(8000)
Exec FolderGetSize 'C:\Program Files\Microsoft SQL Server\', @lFolderSize OUT, @vcErrTxt OUT
select @lFolderSize, @vcErrTxt
*/
DECLARE @lObject int,
@lFolder int,
@RC int,
@RC2 int,
@source varchar(255),
@description varchar(255),
@bCreated bit,
@bExists bit,
@cCR char(1),
@vcErrTxtIn varchar(8000),
@bFolderExists bit
Set @vcErrTxt = NULL
Set @bCreated = 0
Set @lFolderSize = NULL
Set @bFolderExists = 0
EXEC @rc = sp_OACreate 'Scripting.FileSystemObject', @lObject OUTPUT
IF @rc <> 0
GOTO ERR
else
begin
Set @bCreated = 1
EXEC @rc = sp_OAMethod @lObject, 'GetFolder', @lFolder OUT, @vcFolderPath
IF @rc <> 0 GOTO ERR
EXEC @rc = sp_OAMethod @lFolder, 'Size', @lFolderSize OUT
IF @rc <> 0 GOTO ERR
EXEC @rc = sp_OADestroy @lObject
IF @rc <> 0 GOTO ERR
end
RETURN 0
ERR:
Set @vcErrTxt = '[' + isnull(cast(OBJECT_NAME(@@PROCID) as varchar(255)),'NULL') + ']: '
+ 'OLE Automation Error Information (' + cast(@RC as varchar(20)) + ')
'
EXEC @RC2 = sp_OAGetErrorInfo @lObject, @source OUT, @description OUT
IF @RC2 = 0
BEGIN
Set @cCR = char(13)
Set @vcErrTxt = @vcErrTxt + 'Source: ' + isnull(@source,'NULL') + @cCR
+ 'Description: ' + isnull(@description,'NULL') + @cCR
+ 'Folder Path=' + isnull(@vcFolderPath,'NULL')
END
if @bCreated = 1
EXEC @rc = sp_OADestroy @lObject
RETURN 1
-
Hi,
Have you tried looking in event viewer in case it is an automation failure earlier on. Although I can't imagine that being the case if it works most of the time.
Also I would consider putting in more diagnostics so you know exactly where in the sproc it is failing.
Another thought is put some diagnostic to display the folder being checked. Are you sure it is a valid path on those occasion when it fails??
Thanks Jeet -
Thanks for replying Jeet,
a) there's nothing in the event viewer
b) It fails on the "EXEC @rc = sp_OAMethod @lFolder, 'Size', @lFolderSize OUT" line.
c) The path is valid. It is checking (and copying/moving/deleting) thousands of folders and after a while there is this single error repeatedly (other OLE calls are still OK). (can't wait to user C# in SQL2005
)PS: It will also fail with a Permission Denied if there is a NTFS map in the hierarchy below the folder involved. This is an undocumented bug in the file scripting object.
I'm trying to know what this undocumented 10077 error is so I can resolve it or work aroud it OR find another way to get the folder size
regards,
Eric
-
Did you ever find out why the 10077 error was occurring or what the workaround is?
-
I have the same intermittent error when trying to to get to Win32_PerfRawData_PerfDisk_LogicalDisk
SET @output = ''
exec @OA_create_err=sp_OACreate 'WbemScripting.SWbemLocator', @WmiServiceLocator OUTPUT, 5
if @OA_create_err <> 0
begin
EXEC @OA_GetErrorInfo_err = sp_OAGetErrorInfo @WmiServiceLocator, @source OUT, @description OUT;
IF @OA_GetErrorInfo_err = 0
BEGIN
SET @output = @output + ' @WmiServiceLocator @OA_create_err: '+ convert(varchar(255),@OA_create_err) +' Source: ' + coalesce(@source, '') + ', Description: ' + coalesce(@description, '')
END
ELSE
BEGIN
SET @output = @output + ' sp_OAGetErrorInfo failed.'
END
set @message='Could not Create WbemScripting.SWbemLocator - ' + @output
exec sp_OADestroy @WmiServiceLocator
RAISERROR(@message, 16, 1)
return
end
exec @OA_method_err=sp_OAMethod @WmiServiceLocator, 'ConnectServer', @WmiService OUTPUT, '.', 'root\cimv2'
if @OA_method_err <> 0
begin
EXEC @OA_GetErrorInfo_err = sp_OAGetErrorInfo @WmiService, @source OUT, @description OUT;
IF @OA_GetErrorInfo_err = 0
BEGIN
SET @output = @output + ', @WmiService @OA_method_err, '+ convert(varchar(255),@OA_method_err) +' Source: ' + coalesce(@source, '') + ', Description: ' + coalesce(@description, '')
END
ELSE
BEGIN
SET @output = @output + ' sp_OAGetErrorInfo failed.'
END
set @message='Could not get ConnectServer - ' + @output
exec sp_OADestroy @WmiServiceLocator
RAISERROR(@message, 16, 1)
return
end
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply