SQL Server – Create Folders using T-SQL & OLE automation

SQL Server has some standard stored procedures that allow OLE automation.

First step, is to check whether Ole Automation Procedures are enabled in SQL Server or not ? It can be enabled by simply executing below T-SQL commands.

T-SQL to enable Ole Automation Procedures in SQL Server

        sp_configure 'show advanced options', 1;
	GO
	RECONFIGURE;
	GO
	sp_configure 'Ole Automation Procedures', 1;
	GO
	RECONFIGURE;
	GO

Next step is to create a stored procedure, which will use an OLE Automation procedures and create a folder on system drive

--	DATE	:	23-Nov
--	AUTHOR	:	Jitendra Zaa

CREATE PROCEDURE [dbo].[CreateFolder] (@newfolder varchar(1000)) AS  
BEGIN  
DECLARE @OLEfolder   INT  
DECLARE @OLEsource   VARCHAR(255)  
DECLARE @OLEdescription  VARCHAR(255) 
DECLARE @init   INT  
DECLARE @OLEfilesytemobject INT  
 
-- it will fail if OLE automation not enabled
EXEC @init=sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT  
IF @init <> 0  
BEGIN  
	EXEC sp_OAGetErrorInfo @OLEfilesytemobject  
	RETURN  
END  
-- check if folder exists  
EXEC @init=sp_OAMethod @OLEfilesytemobject, 'FolderExists', @OLEfolder OUT, @newfolder  
-- if folder doesnt exist, create it  
IF @OLEfolder=0  
	BEGIN  
	EXEC @init=sp_OAMethod @OLEfilesytemobject, 'CreateFolder', @OLEfolder OUT, @newfolder  
END  
-- in case of error, raise it   
IF @init <> 0  
	BEGIN  
		EXEC sp_OAGetErrorInfo @OLEfilesytemobject, @OLEsource OUT, @OLEdescription OUT  
		SELECT @OLEdescription='Could not create folder: ' + @OLEdescription  
		RAISERROR (@OLEdescription, 16, 1)   
	END  
EXECUTE @init = sp_OADestroy @OLEfilesytemobject  
END  

Its time to finally see OLE Automation in action. We would try to create three nested folders in tmp folder of c drive. Assumption is c:\tmp folder already exists.

Below code iterates through string which contains folder path @folderName and iteratively it will create folders using stored procedure CreateFolder.

--Prerequisite , make sure that @path exists on drive
DECLARE @path varchar(80) = 'C:\tmp',
 @folderName varchar(100)= 'folder1\folder2\folder3\folder4',
 @fullPath varchar(500),
 @progressivePath varchar(500)

SET @fullPath = @path +'\'+@folderName
 
DECLARE @pos INT
DECLARE @len INT
DECLARE @curentFolder varchar(8000)
 
set @pos = 0
set @len = 0
SET @progressivePath = @path

--Loop through path and create folder iteratively
WHILE CHARINDEX('\', @folderName, @pos+1) > 0
BEGIN
    set @len = CHARINDEX('\', @folderName, @pos+1) - @pos 
    set @curentFolder = SUBSTRING(@folderName, @pos, @len) 
	SET @progressivePath = @progressivePath + '\'+@curentFolder
    PRINT @progressivePath 
	EXEC CreateFolder @progressivePath
    set @pos = CHARINDEX('\', @folderName, @pos+@len) +1
END