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

SQL Server – Create Folders using T-SQL & OLE automation
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 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

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?

    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.

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

Leave a comment

Your email address will not be published.

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