SQL script using OLE to get file data and input into a table works fine until row 255

I have a script below that reads path information from a separate table and then uses sp_OAGetProperty to populate the variables and then I insert the data into a table. The script works fine until I get to insert 255 (Which is about row 640 in the lookup table which includes directories as well as files) and then it just loops endlessly putting the row 255 entry in every row in the new table. I am suspecting this is a limitation of something, maybe a sql setting/option, maybe ole related, maybe windows? Please help, here is what I have: Here is the output section I was talking about:

Select * FROM FullDirectoryTree
SET @DirTreeCount = @@ROWCOUNT
SET @Counter = 1

EXEC dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT
WHILE @Counter <= @DirTreeCount
BEGIN
    SELECT @CurrentName = subdirectory,
    @IsFile = Is_File
    FROM FullDirectoryTree 
    WHERE RowNum = @Counter

    IF @IsFile = 1 AND @CurrentName LIKE '%%'
    BEGIN
        EXEC dbo.sp_OAMethod @ObjFileSystem,'GetFile', @ObjFile OUT,    @CurrentName

        EXEC dbo.sp_OAGetProperty @ObjFile, 'Path',             @Path      OUT
        EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortPath',        @ShortPath        OUT
        EXEC dbo.sp_OAGetProperty @ObjFile, 'Name',             @Name             OUT
        EXEC dbo.sp_OAGetProperty @ObjFile, 'ShortName',        @ShortName        OUT
        EXEC dbo.sp_OAGetProperty @ObjFile, 'DateCreated',      @DateCreated      OUT
        EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastAccessed', @DateLastAccessed OUT
        EXEC dbo.sp_OAGetProperty @ObjFile, 'DateLastModified', @DateLastModified OUT
        EXEC dbo.sp_OAGetProperty @ObjFile, 'Size',             @Size             OUT

        INSERT INTO FileDetails
            (Path, ShortPath, Name, ShortName, DateCreated, 
                DateLastAccessed, DateLastModified, Size)
        SELECT @Path,@ShortPath,@Name,@ShortName,@DateCreated, 
            @DateLastAccessed,@DateLastModified,@Size
    END

    SELECT @Counter = @Counter + 1
END

EXEC sp_OADestroy @ObjFileSystem
EXEC sp_OADestroy @ObjFile

sample data:

Rownum   Name              Path
249 954011.dwg  D:\yadayadayada.dwg 
250 954012.dwg  D:\Data\GS...\954012.dwg    
251 954013.dwg  D:\Data\GS...\954013.dwg    
252 954014-A.dwg    D:\Data\GS...\954014-A.dwg  
253 954015-A.dwg    D:\Data\GS...\954015-A.dwg  
254 954016-A.dwg    D:\Data\GS...\954016-A.dwg  
255 954017-A.dwg    D:\Data\GS...\954\954017-A.dwg  
256 954017-A.dwg    D:\Data\GS...\954\954017-A.dwg

the 954017-A just keeps repeating until I kill the query...I have longer path strings in the returned data and I have almost identical file names above...what am I missing?

Solution

I researched a ton more and finally found out that there is a MAX_ODSOLE_OBJECTS setting internally of 255, meaning you cannot call the OA sp's any more times than than 255. I just added the EXEC sp_OADestroy calls at the end of the loop so the OA sp's are only being called at most 9 times per iteration. Works like a charm! Thanks for the CURSOR help Tom!