[转]COPY OR MOVE FILES AND FOLDERS USING OLE AUTOMATION

本文转自：http://sqlindia.com/copy-move-files-folders-using-ole-automation-sql-server/

 

I 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.

--The below configuration must be enabled 

/* 
sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE with override; 
GO 

sp_configure 'Ole Automation Procedures', 1; 
GO 
RECONFIGURE with override; 
GO 
*/ 


DECLARE @source varchar(100) = 'E:\others\Lab_SQLIndia\src\TextFile.txt' 
, @destination varchar(100) = 'E:\others\Lab_SQLIndia\dest' 
, @operation tinyint = 1 -- 1=CopyFile, 2=CopyFolder, 3=MoveFile, 4=MoveFolder,   


DECLARE @fso int 
, @init int 
, @errFso int 
, @return varchar(100) 
SET @destination = @destination + '\' 

EXEC @init = sp_OACreate 'Scripting.FileSystemObject', @fso OUTPUT 
IF @init = 0 SELECT @errFso = @fso 

IF @init = 0 AND @operation = 1 


BEGIN 

IF @init = 0 SELECT @errFso = @fso 

EXEC @init = sp_OAMethod    @fso, 
                'CopyFile', 
                NULL, 
                @source, 
                @destination 

SET @return = 'FILE SUCCESSFULLY COPIED' 


END 


ELSE IF @init = 0 AND @operation = 2 


BEGIN 


IF @init = 0 SELECT @errFso = @fso 


EXEC @init = sp_OAMethod    @fso, 
                'CopyFolder', 
                NULL, 
                @source, 
                @destination 


SET @return = 'FOLDER SUCCESSFULLY COPIED' 


END 


ELSE IF @init = 0 AND @operation = 3 


BEGIN 


IF @init = 0 SELECT @errFso = @fso 


EXEC @init = sp_OAMethod    @fso, 
                'MoveFile', 
                NULL, 
                @source, 
                @destination 


SET @return = 'FILE SUCCESSFULLY MOVED' 


END 


ELSE IF @init = 0 AND @operation = 4 


BEGIN 


IF @init = 0 SELECT @errFso = @fso 


EXEC @init = sp_OAMethod    @fso, 
                'MoveFolder', 
                NULL, 
                @source, 
                @destination 


SET @return = 'FOLDER SUCCESSFULLY MOVED' 


END 


  


IF @init <> 0 


BEGIN 


DECLARE 


@Description varchar (255), 


@src varchar (255), 


@Helpfile varchar (255), 


@HelpID int 


  


EXECUTE sp_OAGetErrorInfo   @errFso, 


                        @src OUTPUT, 


                        @Description OUTPUT, 


                        @Helpfile OUTPUT, 


                        @HelpID OUTPUT 


  


PRINT COALESCE(@src + @Description + @Helpfile + CAST(@HelpID as varchar(20)), 'Error Occurred') 


END 


ELSE 


PRINT @return 


  


EXEC sp_OADestroy @fso