本文轉(zhuǎn)自：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

?

與50位技術(shù)專家面對面20年技術(shù)見證，附贈技術(shù)全景圖

總結(jié)

以上是生活随笔為你收集整理的[转]COPY OR MOVE FILES AND FOLDERS USING OLE AUTOMATION的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。