Check for file exists or not in sql server?

Solution : http://www.tech-recipes.com/rx/30527/sql-server-how-to-check-if-a-file-exists-in-a-directory/

Made a post about this question using stackoverflow question to help others.

id	filepath

1	C:\vishwanath\21776656.docx
2	C:\vishwanath\vish\s_srv_req_2009.txt
3	C:\Users\dalvi\DW\DW20SharedAmd64.exe
4	C:\Users\dalvi\1.txt

I've table like this created in my db server, I've stored file paths in it filepath column, now I've to check using sql whether the file exists in my machine, if it exists I need to add temporary column in my table showing yes if exists and no it doesn't exists.

I wrote this code which works for 1 file But I don't know how to use it for my table.

DECLARE @isExists INT
exec master.dbo.xp_fileexist 'C:\vishwanath\21776656.docx', 
@isExists OUTPUT
SELECT case @isExists 
when 1 then 'Yes' 
else 'No' 
end as isExists

The Final output should like this

id  filepath                                 Isexists

1   C:\vishwanath\21776656.docx               Yes
2   C:\vishwanath\vish\s_srv_req_2009.txt     Yes
3   C:\Users\dalvi\DW\DW20SharedAmd64.exe     Yes
4   C:\Users\dalvi\1.txt                      No

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.

Not tested but you can try something like this :

Declare @count as int
Set @count=1
Declare @inputFile varchar(max)
Declare @Sample Table
(id int,filepath varchar(max) ,Isexists char(3))

while @count<(select max(id) from yourTable)
BEGIN
Set @inputFile =(Select filepath from yourTable where id=@count)
DECLARE @isExists INT
exec master.dbo.xp_fileexist @inputFile , 
@isExists OUTPUT
insert into @Sample
Select @count,@inputFile ,case @isExists 
when 1 then 'Yes' 
else 'No' 
end as isExists
set @count=@count+1
END

Try the following code to verify whether the file exist. You can create a user function and use it in your stored procedure. modify it as you need:

Set NOCOUNT ON

 DECLARE @Filename NVARCHAR(50)
 DECLARE @fileFullPath NVARCHAR(100)

 SELECT @Filename = N'LogiSetup.log'
 SELECT @fileFullPath = N'C:\LogiSetup.log'

create table #dir

(output varchar(2000))

 DECLARE @cmd NVARCHAR(100)
SELECT @cmd = 'dir ' + @fileFullPath     

insert into #dir    

exec master.dbo.xp_cmdshell @cmd

--Select * from #dir

-- This is risky, as the fle path itself might contain the filename
if exists (Select * from #dir where output like '%'+ @Filename +'%')

       begin    
              Print 'File found'    
              --Add code you want to run if file exists    
       end    
else    
       begin    
              Print 'No File Found'    
              --Add code you want to run if file does not exists    
       end

drop table #dir

You can achieve this using a cursor but the performance is much slower than whileloop.. Here's the code:

set nocount on
declare cur cursor local fast_forward for
	(select filepath from Directory)
open cur;
declare @fullpath varchar(250);
declare @isExists int;

fetch from cur into @fullpath
while @@FETCH_STATUS = 0
    begin
		exec xp_fileexist @fullpath, @isExists out
		if @isExists = 1			
			print @fullpath + char(9) + char(9) + 'file exists'
		else			
			print @fullpath + char(9) + char(9) + 'file does not exists'
		fetch from cur into @fullpath
    end
close cur
deallocate cur

or you can put it in a tempTable if you want to integrate it in your frontend..

create proc GetFileStatus as
begin
	set nocount on
	create table #tempFileStatus(FilePath varchar(300),FileStatus varchar(30))
	declare cur cursor local fast_forward for
		(select filepath from Directory)
	open cur;
	declare @fullpath varchar(250);
	declare @isExists int;

	fetch from cur into @fullpath
	while @@FETCH_STATUS = 0
		begin
			exec xp_fileexist @fullpath, @isExists out
			if @isExists = 1				
				insert into #tempFileStatus values(@fullpath,'File exist')
			else
				insert into #tempFileStatus values(@fullpath,'File does not exists')
			fetch from cur into @fullpath
		end
	close cur
	deallocate cur
	select * from #tempFileStatus
	drop table #tempFileStatus
end

then call it using:

exec GetFileStatus