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 · Accepted 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

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

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

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

[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