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