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
This question is related to
sql
sql-server
sql-server-2008
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
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
Source: Stackoverflow.com