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

Posted

in

by

Comments

7 responses to “SQL Server – Create Folders using T-SQL & OLE automation”

  1. adfdaf Avatar
    adfdaf

    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. Michel Avatar
      Michel

      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.

      1. Z Avatar

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

  2. Jesse Avatar
    Jesse

    Does the OLE Automation authentication to create folders?

  3. Willow Avatar
    Willow

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

  4. T Jiles Avatar
    T Jiles

    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

  5. jim Avatar
    jim

    nice solution when it comes for local paths
    sadly, it does not work for unc paths

    thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

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