Recently I came across a situation where I need to check whether the directory is exists or not, in case if the directory does not exist, I have to create new one.
As a solution, I have created below script to fix the issue.
declare @chkdirectory as nvarchar(4000) declare @folder_exists as int set @chkdirectory = 'C:\SQLDBPool\SQL\Articles' 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 print 'Directory is not exists, creating new one' EXECUTE master.dbo.xp_create_subdir @chkdirectory print @chkdirectory + 'created on' + @@servername end else print 'Directory already exists' GO
xp_create_subdir excecutes successfully even if the directory already exists. Therefore all you need is one line to call it, and it will have the same effect as your code.
This solutions works only on a local machine. It’s possible to create a directory on a remote machine in the same networ?
you can write powershell and run it through xp_cmdshell
very good answer