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
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?
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.
Does the OLE Automation authentication to create folders?