locked
SP_OAGetproperty error RRS feed

  • Question

  •  

    HI:

    I am trying to use sp_oacreate etc stroed proceduer do a file exam in specified folder:

    I goe error (-2147024809) when I try to loop though all files and get each files's information:

    I post my code here error generated at ******

    Are you guys experiencing same issue? what is that error code means? Invalid parameters?

     

    thanks a lot

     

     


    --- DECLARE VARIABLE FOR FILE OBJECTS
    DECLARE @SYSO INT
    DECLARE @RECODE INT

    --- DECLARE FOLDER OBJECT
    DECLARE @FOL INT
    DECLARE @FOLDERPATH NVARCHAR(1000)
    SELECT @FOLDERPATH = N'C:\TEMP'

    --- DECLARE FILE OBJECTS
    DECLARE @FILS INT

    --- DECLARE FILE ITMS
    DECLARE @FILITEM INT

    --- DECLARE COUTN ETC
    DECLARE @FILENUMCOUNT INT
    DECLARE @FILENAMES NVARCHAR (300)
    DECLARE @IID INT

    SELECT @IID =1;


    --- DECLARE ERROR HANDLEER
    DECLARE @output varchar(255)
    DECLARE @source varchar(255)
    DECLARE @description varchar(255)


    --- CREATE OBJECT
    EXEC @RECODE  = SP_OACREATE 'SCRIPTING.FILESYSTEMOBJECT',@SYSO OUTPUT;
    PRINT CONVERT (NVARCHAR (200),@RECODE)

    --- OPEN FOLDER METHOD
    EXEC @RECODE  = SP_OAMETHOD @SYSO,'GETFOLDER', @FOL OUT, @FOLDERPATH;
    PRINT CONVERT (NVARCHAR (200),@RECODE)

    --- GET FILE LISTS
    EXEC @RECODE  = SP_OAMETHOD @FOL, 'FILES', @FILS OUTPUT;
    PRINT CONVERT (NVARCHAR (200),@RECODE)

    --- GET FILE NUMBER
    EXEC @RECODE  = SP_OAGETPROPERTY @FILS, 'COUNT', @FILENUMCOUNT OUTPUT
    PRINT CONVERT (NVARCHAR (200),@RECODE)
    SELECT @FILENUMCOUNT AS [TOTAL FILES]

    --- LOOP THOUGH THE FILE LIST
    WHILE @IID<=@FILENUMCOUNT
    BEGIN
     EXEC @RECODE  = SP_OAGETPROPERTY @FILS, 'Item', @FILITEM OUT, @IID

    ************* -2147024809 error generated here *********
     PRINT CONVERT (NVARCHAR (20),@FILITEM)
     PRINT CONVERT (NVARCHAR (200),@RECODE)
     --EXEC dbo.Pro_Display_SQL_Errors @FILS,@RECODE


     EXEC @RECODE  = SP_OAGETPROPERTY @FILITEM, 'NAME', @FILENAMES OUT;
     PRINT @FILENAMES + ' FOR ITEM '+ CONVERT (NVARCHAR (20),@FILITEM)
     PRINT CONVERT (NVARCHAR (200),@RECODE)

     SELECT @IID = @IID + 1;
    END

     


    IF @SYSO>0
    BEGIN
     EXEC SP_OADESTROY @SYSO;
     END
     
     IF @FOL>0
     BEGIN
     EXEC SP_OADESTROY @FOL;
     END

    IF @FILS>0
    BEGIN
    EXEC SP_OADESTROY @FILS;
    END
     
    IF @FILENUMCOUNT > 0
    BEGIN
    EXEC SP_OADESTROY @FILENUMCOUNT;
    END

    IF @FILITEM > 0
    BEGIN
    EXEC SP_OADESTROY @FILITEM;
    END

    IF @FILENAMES>0
    BEGIN
    EXEC SP_OADESTROY @FILENAMES;
    END

    Wednesday, April 2, 2008 3:51 PM

Answers

  •  

    LuZ,

     

    Unfortunately you can't enumerate the files collection like you need using the SP_OA calls to get the file names, or at least I have never found a method to do so.  There are a lot of unanswered forum posts regarding what you are trying to do.  Since you are on SQL 2005, I'd like to propose an alternate solution to you.  You can create a CLR TVF to do what you need.  Here is an example that you can pretty much plug and play with on the SQL CLR blog.

    Thursday, April 3, 2008 1:59 PM

All replies

  • I ran this on SQL Server 2005 SP2. It gave the correct result as 5. It did not count subfolders.

     

    Code Snippet

    --- DECLARE VARIABLE FOR FILE OBJECTS

    DECLARE @SYSO INT

    DECLARE @RECODE INT

    --- DECLARE FOLDER OBJECT

    DECLARE @FOL INT

    DECLARE @FOLDERPATH NVARCHAR(1000)

    SELECT @FOLDERPATH = N'f:\data'

    --- DECLARE FILE OBJECTS

    DECLARE @FILS INT

    --- DECLARE FILE ITMS

    DECLARE @FILITEM INT

    --- DECLARE COUTN ETC

    DECLARE @FILENUMCOUNT INT

    DECLARE @FILENAMES NVARCHAR (300)

    DECLARE @IID INT

    SELECT @IID =1;

     

    --- DECLARE ERROR HANDLEER

    DECLARE @output varchar(255)

    DECLARE @source varchar(255)

    DECLARE @description varchar(255)

     

    --- CREATE OBJECT

    EXEC @RECODE = SP_OACREATE 'SCRIPTING.FILESYSTEMOBJECT',@SYSO OUTPUT;

    PRINT CONVERT (NVARCHAR (200),@RECODE)

    --- OPEN FOLDER METHOD

    EXEC @RECODE = SP_OAMETHOD @SYSO,'GETFOLDER', @FOL OUT, @FOLDERPATH;

    PRINT CONVERT (NVARCHAR (200),@RECODE)

    --- GET FILE LISTS

    EXEC @RECODE = SP_OAMETHOD @FOL, 'FILES', @FILS OUTPUT;

    PRINT CONVERT (NVARCHAR (200),@RECODE)

    --- GET FILE NUMBER

    EXEC @RECODE = SP_OAGETPROPERTY @FILS, 'COUNT', @FILENUMCOUNT OUTPUT

    PRINT CONVERT (NVARCHAR (200),@RECODE)

    SELECT @FILENUMCOUNT AS [TOTAL FILES]

    --- LOOP THOUGH THE FILE LIST

    WHILE @IID<=@FILENUMCOUNT

    BEGIN

    EXEC @RECODE = SP_OAGETPROPERTY @FILS, 'Item', @FILITEM OUT, @IID

     

    PRINT CONVERT (NVARCHAR (20),@FILITEM)

    PRINT CONVERT (NVARCHAR (200),@RECODE)

    --EXEC dbo.Pro_Display_SQL_Errors @FILS,@RECODE

     

    EXEC @RECODE = SP_OAGETPROPERTY @FILITEM, 'NAME', @FILENAMES OUT;

    PRINT @FILENAMES + ' FOR ITEM '+ CONVERT (NVARCHAR (20),@FILITEM)

    PRINT CONVERT (NVARCHAR (200),@RECODE)

    SELECT @IID = @IID + 1;

    END

     

    IF @SYSO>0

    BEGIN

    EXEC SP_OADESTROY @SYSO;

    END

    IF @FOL>0

    BEGIN

    EXEC SP_OADESTROY @FOL;

    END

    IF @FILS>0

    BEGIN

    EXEC SP_OADESTROY @FILS;

    END

    IF @FILENUMCOUNT > 0

    BEGIN

    EXEC SP_OADESTROY @FILENUMCOUNT;

    END

    IF @FILITEM > 0

    BEGIN

    EXEC SP_OADESTROY @FILITEM;

    END

    IF @FILENAMES>0

    BEGIN

    EXEC SP_OADESTROY @FILENAMES;

    END

     

     

    Thursday, April 3, 2008 11:22 AM
  • Hi:

    Thanks for your reply,

    I also run this on SQL 2005 SP2 also, SQL 2008 CTP Nov 2007 version and SQL 2008 (Feb 2008 version). I got error on all of them.

     

    I can get number of files now, but next step to get file's name still in error.

    EXEC @RECODE = SP_OAGETPROPERTY @FILS, 'Item', @FILITEM OUT, @IID

    always has @FILITEM  = 0

    seams the Item property is not working properly for sp_OAGetproperty

    Thanks

    Thursday, April 3, 2008 1:09 PM
  •  

    LuZ,

     

    Unfortunately you can't enumerate the files collection like you need using the SP_OA calls to get the file names, or at least I have never found a method to do so.  There are a lot of unanswered forum posts regarding what you are trying to do.  Since you are on SQL 2005, I'd like to propose an alternate solution to you.  You can create a CLR TVF to do what you need.  Here is an example that you can pretty much plug and play with on the SQL CLR blog.

    Thursday, April 3, 2008 1:59 PM
  • Thanks a lot Jonathan. I did CLR already. Using OLE automation is just my experimental.  Thanks for your reply.

     

    Thursday, April 3, 2008 2:02 PM
  • I tried to do this under SQL 2000 in late 2006 for a number of things.  What is interesting is that in DMO, the collections allow you to do .Items(1) and get the Item object associated with position #1, but the FileSystemObjects collections weren't built to be used that way it seems.  I could be wrong about this, but I searched online for over a month, and never found the solution to this.

    Thursday, April 3, 2008 2:07 PM
  • HI:

    Yes, I can get number of files too, But i got error when I try to print out each file's Name.

     

    Thanks

     

    Thursday, April 3, 2008 2:34 PM
  • You are seeing the file count which we can see just fine.  The error in question is for the enumeration of the file names based on the collection.  If you click the Messages tab after execution, you will see the error code printed by the execution.

     

    Thursday, April 3, 2008 2:34 PM