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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] Check for file exists or not in sql server?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008