COPY OR MOVE FILES AND FOLDERS USING OLE AUTOMATION

cut-copy-pasteI love playing around with automation stuff. In a recent automation task, I was to copy or move files and/or folders from one location to another in SQL Server without using SQLCMD.

If you are novice to OLE automation in SQL server then I would recommend you to read my previous articles on OLE automation first.

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

7 thoughts on “COPY OR MOVE FILES AND FOLDERS USING OLE AUTOMATION

  • October 19, 2016 at 12:04 am
    Permalink

    How to copy a file from one SERVER to another SERVER.I tried these methods and it is not working.
    It works if server is same.

    Reply
    • October 19, 2016 at 12:25 am
      Permalink

      This depends on the file system/folder permission. Are you trying to copy a file to another shared folder? If so, then make sure the login has enough permission on that folder.

      Reply
      • October 19, 2016 at 1:32 am
        Permalink

        I gave full permission of that folder , but still does not work.

        Reply
  • October 19, 2016 at 12:05 am
    Permalink

    Do I need to set any configuration. I tried xp_cmdshell command and it is not working

    Reply
    • October 19, 2016 at 12:27 am
      Permalink

      You need to enable the xp_cmdshell first. Do not enable this on your production server, consult with your senior before doing so.

      — To allow advanced options to be changed.
      EXEC sp_configure ‘show advanced options’, 1;
      GO
      — To update the currently configured value for advanced options.
      RECONFIGURE;
      GO
      — To enable the feature.
      EXEC sp_configure ‘xp_cmdshell’, 1;
      GO
      — To update the currently configured value for this feature.
      RECONFIGURE;
      GO

      Reply
  • September 25, 2017 at 3:49 pm
    Permalink

    Can I use this to retrieve images from a web site, for example (but not working): –

    DECLARE @FsObjId INTEGER
    DECLARE @Source VARCHAR(4096)
    DECLARE @Destination VARCHAR(4096)
    SET @Source = ‘https://somewebsite.com/s/files/1/0898/5824/products/Product123456.jpg’
    SET @Destination= ‘D:\Customers\Images’
    –creare OLE Automation instance
    EXEC sp_OACreate ‘Scripting.FileSystemObject’, @FsObjId OUTPUT
    –call method of OLE Automation
    EXEC sp_OAMethod @FsObjId, ‘CopyFile’, NULL, @Source, @Destination
    –once you finish copy, destroy object
    EXEC sp_OADestroy @FsObjId
    GO

    Reply
    • October 26, 2017 at 12:27 pm
      Permalink

      No this script will not work as we are dealing with only file system objects. But, I think it can be done by using web objects and fetching images from URL.

      Reply

Leave a Reply