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
Leave a Reply