June 5, 2014 at 10:57 am
I'm using sp_OACreate in a scalar function to create a folder if it doesn't exist, and it works fine if you're asking it to create a single folder. For instance: C:\Newfolder
It creates "Newfolder"
However, if I try to ask it to create C:\Newfolder\Newsubfolder
It doesn't work
Here's the code:DECLARE @Exists int, @ObjFile int, @ObjFileSystem int, @Folder nvarchar(500) = 'C:\',
@Action tinyint = 1 --(0 to check if folder exists, 1 to actually create it)
EXEC dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT
EXEC dbo.sp_OAMethod @ObjFileSystem, 'FolderExists', @Exists OUT, @Folder
IF @Action = 1
BEGIN
IF @Exists = 0
BEGIN
EXEC dbo.sp_OAMethod @ObjFileSystem, 'CreateFolder', @Folder OUT, @Folder
IF @@ERROR = 0 AND @Exists <> 0
BEGIN
SET @Exists = 1
END
ELSE
BEGIN
SET @Exists = -1
END
END
END
EXEC dbo.sp_OADestroy @ObjFileSystem
RETURN ISNULL(@Exists, -1)
Any thoughts?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
June 5, 2014 at 11:20 am
I know this is probably a retarded way to go about it, but got it to work using this:
CREATE FUNCTION [dbo].[fx_FolderExists] (
@Folder nvarchar(500),
@Action tinyint
)
RETURNS int AS
BEGIN
DECLARE @Exists int, @ObjFile int, @ObjFileSystem INT, @Idx tinyint
DECLARE @Folders TABLE (idx INT IDENTITY(1,1) , FLevel varchar(100))
EXEC dbo.sp_OACreate 'Scripting.FileSystemObject', @ObjFileSystem OUT
EXEC dbo.sp_OAMethod @ObjFileSystem, 'FolderExists', @Exists OUT, @Folder
IF @Action = 1
BEGIN
IF @Exists = 0
BEGIN
INSERT INTO @Folders
SELECT Item FROM F1Settings.dbo.fx_DelimitedSplit8K(@Folder, '\')
WHERE Item <> ''
SET @Idx = (SELECT TOP 1 idx FROM @Folders)
SET @Folder = '\\'
WHILE (@idx <= (SELECT MAX(idx) FROM @Folders))
BEGIN
SELECT @Folder = @Folder + (SELECT FLevel FROM @Folders WHERE idx = @Idx) + '\'
EXEC dbo.sp_OAMethod @ObjFileSystem, 'CreateFolder', @Folder OUT, @Folder
SET @Idx = @idx + 1
IF @@ERROR = 0 AND @Exists <> 0
BEGIN
SET @Exists = 1
END
ELSE
BEGIN
SET @Exists = -1
END
END
END
END
EXEC dbo.sp_OADestroy @ObjFileSystem
RETURN ISNULL(@Exists, -1)
END
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
August 21, 2017 at 8:34 am
Hello,
seems to me you should change
BEGIN
SELECT @Folder = @Folder + (SELECT FLevel FROM @Folders WHERE idx = @Idx) + '\'
EXEC dbo.sp_OAMethod @ObjFileSystem, 'CreateFolder', @Folder OUT, @Folder
SET @Idx = @Idx + 1
IF @@ERROR = 0 AND @Exists <> 0
BEGIN
SET @Exists = 1
END
ELSE
BEGIN
SET @Exists = -1
END
END
to:
BEGIN
SELECT @Folder = @Folder + (SELECT FLevel FROM @Folders WHERE idx = @Idx) + '\'
EXEC dbo.sp_OAMethod @ObjFileSystem, 'CreateFolder', @Folder OUT, @Folder
IF @@ERROR = 0 AND @Exists <> 0
BEGIN
SET @Exists = 1
END
ELSE
BEGIN
SET @Exists = -1
ENDSET @Idx = @Idx + 1
END
becauseSET @Idx = @Idx + 1
IF @@ERROR = 0 AND @Exists <> 0
will always result in having @@error = 0
Best regards
karl
August 21, 2017 at 2:04 pm
If you don't mind a little help from "undocumented code", the following will easily do exactly what you want. This is what SQL Server uses to make directories for backups. The neat thing about it is that if the directory already exists, it causes no errors or damage and it already lives on every SQL Server that you have.
EXECUTE xp_create_subdir N'C:\Newfolder\Newsubfolder'
--Jeff Moden
Change is inevitable... Change for the better is not.
August 22, 2017 at 3:36 am
It's always good finding easier ways...Thank you very much!!
Best regards
karl
August 24, 2017 at 11:07 pm
you can also incorporate creating folders via mkdir:
SET @Path = '\\xxxxxxx\yyyyyy\'
SET @ssql = ' mkdir ' + @Path + '\' +@DBName
EXEC xp_cmdshell @ssql, no_output
August 24, 2017 at 11:42 pm
barsuk - Thursday, August 24, 2017 11:07 PMyou can also incorporate creating folders via mkdir:
SET @Path = '\\xxxxxxx\yyyyyy\'
SET @ssql = ' mkdir ' + @Path + '\' +@DBName
EXEC xp_cmdshell @ssql, no_output
What happens if the path already exists?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2017 at 12:16 am
Jeff Moden - Thursday, August 24, 2017 11:42 PMbarsuk - Thursday, August 24, 2017 11:07 PMyou can also incorporate creating folders via mkdir:
SET @Path = '\\xxxxxxx\yyyyyy\'
SET @ssql = ' mkdir ' + @Path + '\' +@DBName
EXEC xp_cmdshell @ssql, no_outputWhat happens if the path already exists?
Then you will dump the results of EXEC master.dbo.xp_fileexist @Path to temp table and then query that temp table with if else:
IF EXISTS(SELECT IsDirectory FROM #temp WHERE IsDirectory=1)
BEGIN
PRINT @@ServerName + ': Folder already Exists on ' + @PATH
END
ELSE
BEGIN
SET @mkdir = 'mkdir ' + @PATH
EXEC xp_cmdshell @mkdir
PRINT 'Folder has been created on ' + @PATH
END
August 25, 2017 at 7:16 am
barsuk - Friday, August 25, 2017 12:16 AMJeff Moden - Thursday, August 24, 2017 11:42 PMbarsuk - Thursday, August 24, 2017 11:07 PMyou can also incorporate creating folders via mkdir:
SET @Path = '\\xxxxxxx\yyyyyy\'
SET @ssql = ' mkdir ' + @Path + '\' +@DBName
EXEC xp_cmdshell @ssql, no_outputWhat happens if the path already exists?
Then you will dump the results of EXEC master.dbo.xp_fileexist @Path to temp table and then query that temp table with if else:
IF EXISTS(SELECT IsDirectory FROM #temp WHERE IsDirectory=1)
BEGIN
PRINT @@ServerName + ': Folder already Exists on ' + @PATH
END
ELSE
BEGIN
SET @mkdir = 'mkdir ' + @PATH
EXEC xp_cmdshell @mkdir
PRINT 'Folder has been created on ' + @PATH
END
Ok... a little deeper then. What are you using to populate the #Temp table? DirTree or another DOS command?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 27, 2017 at 2:15 am
Jeff Moden - Friday, August 25, 2017 7:16 AMbarsuk - Friday, August 25, 2017 12:16 AMJeff Moden - Thursday, August 24, 2017 11:42 PMbarsuk - Thursday, August 24, 2017 11:07 PMyou can also incorporate creating folders via mkdir:
SET @Path = '\\xxxxxxx\yyyyyy\'
SET @ssql = ' mkdir ' + @Path + '\' +@DBName
EXEC xp_cmdshell @ssql, no_outputWhat happens if the path already exists?
Then you will dump the results of EXEC master.dbo.xp_fileexist @Path to temp table and then query that temp table with if else:
IF EXISTS(SELECT IsDirectory FROM #temp WHERE IsDirectory=1)
BEGIN
PRINT @@ServerName + ': Folder already Exists on ' + @PATH
END
ELSE
BEGIN
SET @mkdir = 'mkdir ' + @PATH
EXEC xp_cmdshell @mkdir
PRINT 'Folder has been created on ' + @PATH
ENDOk... a little deeper then. What are you using to populate the #Temp table? DirTree or another DOS command?
CREATE TABLE #temp(FileExists int, IsDirectory int, ParentDirExists int)
INSERT INTO #temp
EXEC master.dbo.xp_fileexist @PATH
June 1, 2018 at 1:30 pm
I know this is old but try changing this line.
IF @@ERROR = 0 AND @Exists <> 0
to
IF @@ERROR = 0
--You can only get here when @Exists = 0
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply