Usage :
DECLARE @destinationpath NVARCHAR(1000) SET @destinationpath = N'C:\Sql Scripts\' + @@SERVERNAME exec Utils.usp_OLECreateFolder @newfolder = @destinationpath
Proc :
CREATE PROCEDURE Utils.usp_OLECreateFolder (@newfolder varchar(1000)) AS BEGIN DECLARE @OLEresult INT DECLARE @OLEfilesytemobject INT DECLARE @OLEfolder INT DECLARE @OLEsource VARCHAR(255) DECLARE @OLEdescription VARCHAR(255) -- create file system object (will fail if OLE automation not enabled) EXEC @OLEresult=sp_OACreate 'Scripting.FileSystemObject', @OLEfilesytemobject OUT IF @OLEresult <> 0 BEGIN EXEC sp_OAGetErrorInfo @OLEfilesytemobject RETURN END -- check if folder exists EXEC @OLEresult=sp_OAMethod @OLEfilesytemobject, 'FolderExists', @OLEfolder OUT, @newfolder -- if folder doesnt exist, create it IF @OLEfolder=0 BEGIN EXEC @OLEresult=sp_OAMethod @OLEfilesytemobject, 'CreateFolder', @OLEfolder OUT, @newfolder END -- if error has occured, report it! IF @OLEresult <> 0 BEGIN EXEC sp_OAGetErrorInfo @OLEfilesytemobject, @OLEsource OUT, @OLEdescription OUT SELECT @OLEdescription='Could not create folder: ' + @OLEdescription RAISERROR (@OLEdescription, 16, 1) END EXECUTE @OLEResult = sp_OADestroy @OLEfilesytemobject END GO
Note : Remember to enable OLE Automation for this to work.
Also bear in mind that the service account SQL is running under will need the correct permissions to be able to create a folder!
