How to Check If a Directory Exists Using SQL Server

How do I check if a directory exists using SQL Server?

 CREATE TABLE ResultSet (Directory varchar(200))

 INSERT INTO ResultSet
 EXEC master.dbo.xp_subdirs 'c:\'

 Select * FROM ResultSet where Directory = 'testing'

Will return a list of sub directories, you can then check the contents of the list.

Does file exist in current Folder using SQL Server?

I have another solution for you
it uses the xp_cmdshell command to retrieve and store all the files with their full pathes inside a given folder

Please replace "répertoire de" by the English translation "folder of ", I am using a French Edition of Windows

**

--Kamel Gazzah
    --19/03/2019
    --Script to retrieve all the files in a a folder, inside all the sub 
     directoris

declare @folder as varchar(100)
-----------------------------------------
set @folder='d:\'
-----------------------------------------
declare @script as varchar(2000)
set @script='exec master..xp_cmdshell "dir '+@folder+'  /N /s"'
declare @mytab as table(id int identity(1,1),date_time datetime,folder int,filename varchar(1000),parent_folder varchar(200))
insert into @mytab(filename) exec(@script)
update @mytab set date_time= substring(filename,1,18) where date_time is null and isdate(substring(filename,1,18))=1
update @mytab set folder=1 where filename like '%répertoire de%' and folder is null
update @mytab set folder=0 where filename not like '%<DIR>%' and folder is null and date_time is not null
update @mytab set filename=replace(filename,'répertoire de ','') where folder=1
delete from @mytab where folder is null
update @mytab set parent_folder=t2.filename
--select t1.id,t1.folder,t1.filename,t2.filename
 from @mytab t1
outer apply (select top 1 filename from @mytab where id<t1.id  and folder=1 order by id desc) t2
where t1.folder=0
UPDATE @mytab SET FILENAME=substring(filename,37,len(filename)) WHERE FOLDER=0 
select id,replace(replace(parent_folder,'\',''),':',':\')+'\'+filename [Fullpath] from @mytab where folder=0 

**

Check for file exists or not in sql server?

Create a function like so:

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

Edit your table and add a computed column (IsExists BIT). Set the expression to:

dbo.fn_FileExists(filepath)

Then just select:

SELECT * FROM dbo.MyTable where IsExists = 1

Update:

To use the function outside a computed column:

select id, filename, dbo.fn_FileExists(filename) as IsExists
from dbo.MyTable

Update:

If the function returns 0 for a known file, then there is likely a permissions issue. Make sure the SQL Server's account has sufficient permissions to access the folder and files. Read-only should be enough.

And YES, by default, the 'NETWORK SERVICE' account will not have sufficient right into most folders. Right click on the folder in question and select 'Properties', then click on the 'Security' tab. Click 'Edit' and add 'Network Service'. Click 'Apply' and retest.

SQL Script to create Sub directories if they do not exist

This is the answer portion

Creating a directory that already exists doesn't error or remove files, so go ahead and create it every loop regardless. You wont loose any backups, all happy.

declare 
     @dirPath varchar(1024) 
    ,@msg varchar(1024) 
    ,@bkUp varchar(1024) 
    ,@fileDate VARCHAR(20) = CONVERT(VARCHAR(20),GETDATE(),112) 
    ,@name VARCHAR(128) -- database name  128 is max for sql objects

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master',
                    'model',
                    'msdb',
                    'tempdb',
                    'PracticaSd-Dallasmetro')  -- exclude these databases

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
        set @dirPath = '\\nas01\Admin\bbelden\test\' + @name
        --set @dirPath = 'D:\SQL Server\MSSQL\Backup\' + @name --my home path
        begin try
            exec xp_create_subdir @dirPath;
            set @msg = 'Created directory: ' + @dirPath
            raiserror(@msg,0,0);
            set @bkUp = 'backup database ' + quotename(@name) + ' to disk = ''' + @dirPath + '\' + @name + '_' + @fileDate + '.bak'' with compression';
            exec(@bkUp);
            set @bkUp = 'RESTORE VERIFYONLY FROM DISK = ''' + @dirPath + '\' + @name + '_' + @fileDate + '.bak''';
            exec(@bkUp);
        end try
        begin catch
            set @msg = 'something went wrong!!! with: ' + @name + '    ' + error_message()
            raiserror(@msg,0,0);
        end catch
       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

This is more of a comment w/some script.

Also, be aware that the user attempting to access/create files or folders is the service account.

to check/verify you can

sp_configure 'advanced',1;
reconfigure;
GO
sp_configure 'xp_cmdshell', 1;
reconfigure;
GO
xp_cmdshell 'echo %USERDOMAIN%\%USERNAME%';

and like the previous first comment there are many undocumented stored procedures for file system operations.

• xp_delete_file
• xp_dirtree
• xp_fileexist
• xp_fixeddrives
• xp_subdirs
• xp_create_subdir

Set the path when checking if a file exists in a folder via SQL Server 2016

Please create two new folders under Users & put in the file & use the below code to test it:

 DECLARE @result INT
 EXEC master.dbo.xp_fileexist 'C:\Users\New folder\New folder\$11F7BC3A.jpg', @result OUTPUT
 select @result as 'results'

If this does work, it means you do not have the full permission to manage James.James folder

Update:

Please follow the instruction in the link to configure the permission:

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-file-system-permissions-for-database-engine-access?view=sql-server-ver15

Check if file exists in folder or not (SSIS and SQL Server 2014/2016)

You can use a script task and set a variable using:

System.IO.File.Exists([filepath]);

Or you can use a foreach (file) and set the mask as the complete path. I tend to use this approach solely so I can hand the package off to someone that isn't scared of c#.

Either way you need to get the filepath from the table which you can do via execute SQL task in to a variable.

Here is the control flow to do your work:

Notice the function on the path leading to the send mail. It is set to counter==0

---

Related Topics