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

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

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

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

6 comments

1. Hi i am attempting to do this along with your other article “Export Documents saved as Blob / Binary from SQL Server”. I’ve ran into a problem I don’t quite know how to solve. I’ve ran the above code nearly identical with the exception of the @Path being (DECLARE @path varchar(80) = ‘C:\test’) Instead of your (C:\temp). I Keep receiving an error message whenever, i execute, that doesn’t indicate why im recieving it the message is:

   C:\test\folder1
   Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 75

   C:\test\folder1\folder2
   Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 75

   C:\test\folder1\folder2\folder3
   Msg 50000, Level 16, State 1, Procedure CreateFolder, Line 75

   Do you know why I Could be getting these?

   Reply

   1. For future reference who are getting MSG 50000 errors: please run this script in an older version of MGTST. Or at least in the Management Studio which is correct for your SQL instance. I was getting the MSG 50000 error when using this in the MGTST 2019 but when run in 2008r2 it works fine.

      Reply

      1. I get that error in 2016 – is there any solution?

         Reply

2. Does the OLE Automation authentication to create folders?

   Reply

3. Any Recommendations on how to Accomplish the Above in SQL SERVER 2017 ?

   Reply

4. Hi All, I got this to work for SQL Server 2019 by replacing sp_OACreate ‘Scripting.FileSystemObject’ with sp_OACreate ‘SQLDMO.SQLServer’ .

   https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-oacreate-transact-sql?view=sql-server-2016

   Please see below

   USE [Integration]
   GO
   /****** Object: StoredProcedure [dbo].[CreateFolder] Script Date: 4/25/2022 12:22:11 PM ******/
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   — DATE : 23-Nov
   — AUTHOR : Jitendra Zaa
   — SOURCE : https://www.jitendrazaa.com/blog/sql/sqlserver/sql-server-create-folders-using-t-sql-ole-automation/
   — MODIFIED: 2022-04-24 T Jiles – replaced sp_OACreate ‘Scripting.FileSystemObject’ with sp_OACreate ‘SQLDMO.SQLServer’ for this to work with SQL Server 2019
   ALTER 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 ‘SQLDMO.SQLServer’, @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

   Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.