Check for file exists or not in sql server?

Question

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 

Answer 1

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.