Make directory in Using SQL xp_create_subdir

Question

CREATE PROCEDURE SPCheckDirectoryExists
  (
     @chkdirectory as nvarchar(4000)
  )
  AS
  SET NOCOUNT ON
  BEGIN
     DECLARE @folder_exists as int
     DECLARE @file_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)

    INSERT INTO @file_results
    (file_exists, file_is_a_directory, parent_directory_exists)
    EXEC MASTER.dbo.xp_fileexist @chkdirectory

    SELECT @folder_exists = file_is_a_directory
    FROM @file_results

    --script to create directory        
    IF @folder_exists = 0
     BEGIN
        EXECUTE master.dbo.xp_create_subdir @chkdirectory
        PRINT @chkdirectory +  ' created on  ' + @@servername
     END       
    ELSE
    PRINT 'Directory already exists'
END

By using above stored procedure ,

EXEC SPCheckDirectoryExists '\\SampleNetworkpath\Test\Test1'

It will check and create Test1 folder in Test Folder ,

If Test Folder is not present in \\SampleNetworkpath\Path Its throwing Error ,

How to Fix this issue , Thanks in Advance,

Answer 1

You need to check that directory as well. Something like this, using your same logic...

CREATE PROCEDURE SPCheckDirectoryExists
  (
     @chkdirectory as nvarchar(4000)
  )
  AS
  SET NOCOUNT ON
  BEGIN
     DECLARE @folder_exists as int
     DECLARE @file_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)

     DECLARE @folder_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)
     DECLARE @chkdirectory2 nvarchar(4000) = reverse(right(reverse(@chkdirectory),len(@chkdirectory) - charindex('\',reverse(@chkdirectory))))
     DECLARE @folder_exists2 int

    INSERT INTO @file_results
    (file_exists, file_is_a_directory, parent_directory_exists)
    EXEC MASTER.dbo.xp_fileexist @chkdirectory

    INSERT INTO @folder_results
    (file_exists, file_is_a_directory, parent_directory_exists)
    EXEC MASTER.dbo.xp_fileexist @chkdirectory2

    SELECT @folder_exists = file_is_a_directory
    FROM @file_results

    SELECT @folder_exists2 = file_is_a_directory
    FROM @folder_results

    --script to create directory        
    IF @folder_exists = 0 and @folder_exists2 = 1
     BEGIN
        EXECUTE master.dbo.xp_create_subdir @chkdirectory
        PRINT @chkdirectory +  ' created on  ' + @@servername
     END       
    ELSE
    PRINT 'Directory already exists or parent directory was invalid'
END

Answer 2

Below store procedure will create directory and sub directory also. Hope will it works for you.

CREATE PROCEDURE sp_CreateBackupPath 
(
     @directory_path as nvarchar(4000) ='F:\BACKUP\YEAR\MONTH\DAY' --For Local Computer directory
     --@directory_path varchar(256)='\\BACKUP\DBNAME\YEAR\MONTH\DAY'  --For Network Sharing directory
  )
AS

SET NOCOUNT ON
DECLARE @tmp_path varchar(256)
DECLARE @dir_name varchar(256)
DECLARE @idx int 
DECLARE @delimiter varchar(1) ='\'
DECLARE @folder_exists as int
DECLARE @file_results table(file_exists int,file_is_a_directory int,parent_directory_exists int)


IF CHARINDEX(@delimiter,@directory_path) = 3
BEGIN
    SET @dir_name = LEFT(@directory_path, CHARINDEX(@delimiter,@directory_path))
    SET @tmp_path = REPLACE(@directory_path, @dir_name,'')
    
    SET @dir_name =REPLACE(@dir_name, @Delimiter, '')
END
ELSE IF CHARINDEX(@delimiter,@directory_path) = 1 and CHARINDEX(@delimiter,@directory_path,2) = 2
BEGIN
    SET @dir_name = LEFT(@directory_path, CHARINDEX(@delimiter,@directory_path,3))
    SET @tmp_path = REPLACE(@directory_path, @dir_name,'')
    
    SET @dir_name ='\\'+REPLACE(@dir_name, @Delimiter, '')

END
ELSE
BEGIN
    Print 'Path not valid.'
    RETURN
END

SET @idx = 1  
WHILE @idx!= 0       
BEGIN
    SET @idx = CHARINDEX(@Delimiter, @tmp_path)       

    IF @idx!=0       
    SET @dir_name = @dir_name + @Delimiter + LEFT(@tmp_path,@idx - 1)       
    ELSE       
    SET @dir_name = @dir_name + @Delimiter + @tmp_path       
            
    IF LEN(@dir_name) > 0
    BEGIN
        DELETE FROM @file_results
        INSERT INTO @file_results
        (file_exists, file_is_a_directory, parent_directory_exists)
        EXEC MASTER.dbo.xp_fileexist @dir_name

        SELECT @folder_exists = file_is_a_directory
        FROM @file_results

        IF @folder_exists = 0
        EXECUTE master.dbo.xp_create_subdir @dir_name       
    END
   
    SET @tmp_path = RIGHT(@tmp_path, LEN(@tmp_path) - @idx)       
    IF LEN(@tmp_path) = 0 BREAK   
End
PRINT @directory_path +  ' created on  ' + @@servername
SET NOCOUNT OFF

By using above stored procedure ,

EXEC sp_CreateBackupPath 'F:\BACKUP\YEAR\MONTH\DAY'

or

EXEC sp_CreateBackupPath '\\BACKUP\DBNAME\YEAR\MONTH\DAY'

Hope it will works for you. Thanks you.!

Answer 3

Dynamic script using your logic.

CREATE PROCEDURE SPCheckDirectoryExists
(
    @chkdirectory as nvarchar(4000)
)
AS
SET NOCOUNT ON
BEGIN
    DECLARE @pos INT,@NewPath AS NVARCHAR(256) 
    WHILE(charindex('\',@chkdirectory) > 0)
    begin
        select  @pos  = CHARINDEX('\',@chkdirectory,0)  
        IF (@NewPath <> '')
            SET @NewPath = @NewPath + '\'
        SET @NewPath =ISNULL(@NewPath,'') + SUBSTRING(@chkdirectory,0,CHARINDEX('\',@chkdirectory,@pos-1)) 

        DECLARE @folder_exists as int
        DECLARE @file_results table(file_exists int,file_is_a_directory int,parent_directory_exists int) 

        INSERT INTO @file_results
        (file_exists, file_is_a_directory, parent_directory_exists)
        EXEC MASTER.dbo.xp_fileexist @NewPath

        SELECT @folder_exists = file_is_a_directory
        FROM @file_results

        --script to create directory        
        IF @folder_exists = 0
            BEGIN
            EXECUTE master.dbo.xp_create_subdir @NewPath
            PRINT @NewPath +  ' created on  ' + @@servername
            END       
        ELSE
            PRINT 'Directory already exists'

        SET @chkdirectory = SUBSTRING(@chkdirectory,@pos+1,len(@chkdirectory))
    END
END