title: sp_OAMethod 'DeleteFile' Method not working in SQL Server 2008 R2
body: <p>I have an sql stored procedure that I use to delete files from the windows file system using the sp_OAMethod.  This used to work fine when we were using sql server 2005, however, it does not work at all now when using sql server 2008 R2.  I have read that you can use SQLDMO/SQLCLR however, I cannot find any decent information regarding these methods. My previous code is below:</p>
  
  <pre><code>-- declare variables
  declare     @ObjectID       nvarchar(10),
              @ObjectType     nvarchar(255),
              @BackupName     nvarchar(255),
              @BackupLocation nvarchar(255),
              @ExpiryDate     datetime,
              @DeletedStatus  bit,
              @SQL            nvarchar(4000),
              @SQL1           nvarchar(4000),
              @SQL2           nvarchar(4000),
              @Result         int,
              @FSO_Token      int,
              @FileLocation   nvarchar(4000)
  
  -- declare cursor for table backups
  declare backupexpired_cursor cursor for
  select dbo.tbl_BackupObjects.ObjectID, dbo.tbl_BackupObjects.ObjectType, dbo.tbl_BackupObjects.BackupName, 
      dbo.tbl_BackupObjects.BackupLocation, dbo.tbl_BackupObjects.ExpiryDate, dbo.tbl_BackupObjects.Deleted
  from dbo.tbl_BackupObjects
  where dbo.tbl_BackupObjects.Deleted &lt;&gt; 1
  
  -- open cursor
  open backupexpired_cursor
  
  -- fetch the next record from the cursor
  fetch next from backupexpired_cursor into @ObjectID, @ObjectType, @BackupName, @BackupLocation, @ExpiryDate, @DeletedStatus
  
  while (@@FETCH_STATUS &lt;&gt; -1)
  begin
      if (@@FETCH_STATUS &lt;&gt; -2)
      begin
          if (@ExpiryDate &lt; GetDate())
          begin
              if (@ObjectType = 'Table')
              begin
                  begin try
                      begin transaction
                          -- Only done if the object type is a table object
                          -- Remove old backup
                          select @SQL = 'drop table dbo.' + quotename(@BackupName)
                          exec sp_executesql @SQL
                          -- update the deleted status and the date deleted of the deleted object
                          select @SQL1 = 'update tbl_BackupObjects
                                          set Deleted = 1,
                                          DeletedDate = GetDate()
                                          where ObjectID = ''' + @ObjectID + ''''
  
                          exec sp_executesql @SQL1
  
                      commit transaction
                  end try
                  begin catch
                      rollback transaction
  
                      select @SQL1 = 'update tbl_BackupObjects
                                  set Deleted = 0,
                                  DeletedDate = NULL
                                  where ObjectID = ''' + @ObjectID + ''''
  
                      exec sp_executesql @SQL1
                  end catch   
              end
              else
              begin
                  begin try 
                      begin transaction
                          -- Only done if the object(view, stored procedure, and/or function is saved 
                          -- in a file located on the windows file system.
                          -- Create File Location 
                          set @FileLocation = 'G:\Backup Registry Script Files\' + @BackupLocation + '\' + @BackupName + ''
  
                          -- Create a token of the object 
                          EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @FSO_Token OUTPUT
  
                          -- Call the deletefile method using the @FileLocation parameter and the token created above:
                          --      - The object token created by sp_OACreate
                          --      - The method name
                          --      - The method's return value
                          --      - Parameters that will be used by the object method
                          EXEC @Result = sp_OAMethod @FSO_Token, 'DeleteFile', NULL, @FileLocation
  
                          -- Execute ole method
                          EXEC @Result = sp_OADestroy @FSO_Token      
                          -- update the deleted status and the date deleted of the deleted object
  
  
                          select @SQL1 = 'update tbl_BackupObjects
                                          set Deleted = 1,
                                          DeletedDate = GetDate()
                                          where ObjectID = ''' + @ObjectID + ''''
  
                          exec sp_executesql @SQL1
  
                      commit transaction
                  end try
                  begin catch
                      rollback transaction
  
                      select @SQL1 = 'update tbl_BackupObjects
                                  set Deleted = 0,
                                  DeletedDate = GetDate()
                                  where ObjectID = ''' + @ObjectID + ''''
  
                      exec sp_executesql @SQL1
                  end catch
              end
          end
      end
      -- fetch the next record from the cursor
      fetch next from backupexpired_cursor into @ObjectID, @ObjectType, @BackupName, @BackupLocation, @ExpiryDate, @DeletedStatus
  end
  
  -- set the Last and Next Removal Dates
  select @SQL2 = 'update tbl_BackupRemovalDate
                  set LastRemovalDate = GetDate(),
                      NextRemovalDate = GetDate() + 7'
  exec sp_executesql @SQL2
  
  -- close cursor
  close backupexpired_cursor
  deallocate backupexpired_cursor
  </code></pre>
  
  <p>I have seen that SQLDMO is quite similar to what I have, however I cannot find any information on how to delete a file system file using this method.  Can anyone help?</p>