使用 SP_OAXXX 创建文件夹，注意区别于 xp_cmdshell --mkdir xxx

最新推荐文章于 2023-10-27 09:50:43 发布

dianxunma2886

最新推荐文章于 2023-10-27 09:50:43 发布

阅读量119

点赞数

原文链接：http://www.cnblogs.com/Frank99/p/6373573.html

版权

sp_configure 'show advanced options',1
go 
reconfigure with override
go
sp_configure 'Ole Automation Procedures',1
go
reconfigure  with override
go
ALTER PROC dbo.usp_OACreate  @folderName VARCHAR(256)='C:\Test\testfa'
AS
BEGIN 
	DECLARE @hr INT,@fso_token INT,@dirHandle INT,@IsDir_flag BIT,@IsParDirExists_Flag BIT
	DECLARE @tempTab TABLE(fileexists_flag BIT,IsDir_flag BIT,IsParDirExists_Flag BIT)
	INSERT INTO @tempTab
	EXEC xp_fileexist @folderName  --,@hr output
	SELECT @IsDir_flag=IsDir_flag,@IsParDirExists_Flag=IsParDirExists_Flag FROM @tempTab
	IF @IsDir_flag<>1 AND @IsParDirExists_Flag=1
		BEGIN
		     EXEC @hr=SP_OACREATE 'Scripting.FileSystemObject',@fso_token OUTPUT
			  IF @hr<>0
				BEGIN
					GOTO errorProcess
				END
				ELSE
					BEGIN
						EXEC @hr=SP_OAMethod @fso_token,'CreateFolder',@dirHandle OUTPUT,@folderName
					    
					    IF @hr<>0 GOTO errorProcess
						ELSE 
						 EXEC SP_OADestroy @fso_token
					END
		END
	ELSE
	GOTO fileexists
	errorProcess:
	EXEC SP_OAGetErrorInfo @fso_token
	RETURN 1; --- FAILED
	fileexists:
	RAISERROR('Folder %s exists OR its parent directory path is not exist!!! please check!!!',-1,-1,@folderName) WITH NOWAIT
END
GO

转载于:https://www.cnblogs.com/Frank99/p/6373573.html