Tuesday 27 January 2009

OLE Automation : Create file folder from TSQL

Create a folder on the file system from TSQL via OLE.

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!

2 comments:

WoundedEgo said...

Should I be able to use a UNC with this? IE: \\server\path...?

Thanks.

WoundedEgo said...

By the way, great post.