Check for File Exists or Not in SQL Server

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.

T-SQL Check whether a file exists

You have run into a case of data type precedence. The statement

SET @sql = N'xp_fileexist '+@filepath+ N', '+@file_exists+ N' OUT'

contains @file_exists which is declared as an INT, therefore all of the strings need to be converted to INT values for addition.

What you appear to want is:

declare @FilePath as NVarChar(256), @FileExists as Int = 0;

set @FilePath = Concat( N'C:\ABC\myfile_', Convert( NVarChar(8), GetDate(), 112), N'.xls' );

execute master.dbo.xp_fileexist @FilePath, @FileExists output;

if ( @FileExists = 1 )
    print 'File exists.'

Note that the data type specified in the Convert has a length.

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 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

How can I verify that a file exists in Windows with SQL?

You can use xp_fileexist however please note it is undocumented and unsupported.

You can use SQLCLR, however you didn't bother specifying what version of SQL Server you're using, so it may not be relevant - and in any case it is disabled by default, and security policies prevent its use in some places.

You can use a #temp table and xp_cmdshell, however xp_cmdshell is typically disabled for the same reasons as SQLCLR.

/* if you need to enable xp_cmdshell:

exec master..sp_configure 'show adv', 1;
reconfigure with override;
exec master..sp_configure 'xp_cmdshell', 1;
reconfigure with override;
exec master..sp_configure 'show adv', 0;
reconfigure with override;

*/

SET NOCOUNT ON;

DECLARE 
   @file VARCHAR(1000),
   @path VARCHAR(255),
   @cmd  VARCHAR(2048);

SELECT
   @file = 'foo.zip',
   @path = 'C:\wherever\';

SELECT @cmd = 'dir /b "' + @path + @file + '"';

CREATE TABLE #x(a VARCHAR(1255));
INSERT #x EXEC master..xp_cmdshell @cmd;
IF EXISTS (SELECT 1 FROM #x WHERE a = @file)
   PRINT 'file exists';
ELSE
   PRINT 'file does not exist';
DROP TABLE #x;

EDIT based on new requirements. It shows a list of files either in the table or in the database, and indicates whether the file is in only one location or both. It assumes that path + file is <= 900 characters long (merely to be able to use an index on at least one side).

USE tempdb;
GO

CREATE TABLE dbo.files(f VARCHAR(1000));

INSERT dbo.files(f) SELECT 'zip_that_does_not_exist.zip'
    UNION ALL SELECT 'c:\path\file_that_does_not_exist.zip'
    UNION ALL SELECT 'c:\path\file_that_exists.zip'
    UNION ALL SELECT 'zip_that_exists.zip';

DECLARE 
   @path VARCHAR(255),
   @cmd  VARCHAR(2048);

SELECT
   @path = path_column,
   @cmd  = 'dir /b "' + path_column + '"'
FROM 
   dbo.table_that_holds_path;

CREATE TABLE #x(f VARCHAR(900) UNIQUE);

INSERT #x EXEC master..xp_cmdshell @cmd;
DELETE #x WHERE f IS NULL;
UPDATE #x SET f = LOWER(f);

WITH f AS 
(
    SELECT f = REPLACE(LOWER(f), LOWER(@path), '')
        FROM dbo.files
)
SELECT 
    [file] = COALESCE(x.f, f.f),
    [status] = CASE 
        WHEN x.f IS NULL THEN 'in database, not in folder'
        WHEN f.f IS NULL THEN 'in folder, not in database'
        ELSE 'in both' END
FROM
    f FULL OUTER JOIN #x AS x
    ON x.f = f.f;

DROP TABLE #x, dbo.files;

Check if file exist on a webserver using sql server

After searching for a few days, I found the following which works well :-

When you have to put file, like BCP result, or a backup in a remote drive, just map this drive into windows don't work, it must be mapped on SQL Server to!, to do this, try like this:

exec xp_cmdshell 'net use p:\ \Server\Folder\Folder\Folder\ /Domain\Login /Password'

Reference :
https://social.msdn.microsoft.com/Forums/en-US/6eca2d62-eb86-4f23-9b86-6f917017f50c/bcp-utility-via-xpcmdshell-and-network-drive?forum=sqlsecurity

---

Related Topics