none
How to check File is Exist or Not in Directory using T-SQL Store Procedure - SQL Server 2005 ?

    Question

  • Hi friends,

    How can we check that, file inside the Directory is exist or not using T-SQL Store procedure ?

    My Scenario is that, if file is exist in folder then, i would like to send E-mail Attachment otherwise not.

    Thanks.

    Monday, June 21, 2010 8:20 PM

Answers

  • Adding pointer to Adam's suggestion , By below approach you can avoid the over head of a #temp tabel.

    DECLARE @output INT

    EXEC @output = XP_CMDSHELL 'DIR "E:\temp\TextTest.txt" /B', NO_OUTPUT

    IF @output = 1

          PRINT 'File Donot exists'

    ELSE

          PRINT 'File exists'

     

    • Marked as answer by KJian_ Monday, June 28, 2010 9:49 AM
    Tuesday, June 22, 2010 5:41 AM
  • This is normally best done outside of SQL Server. If you do not have any other choice, then here is an article that shows some methods for accessing the file system. You can use similar approach to get list of files in a folder and check if particular file exists.

    http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

     

    Other options are to use CLR or the xp_fileexist extended stored procedure:

    http://www.mssqltips.com/tip.asp?tip=1272



    Plamen Ratchev
    • Marked as answer by KJian_ Monday, June 28, 2010 9:49 AM
    Monday, June 21, 2010 8:34 PM
  • Hi,

    You will have to do this using extended stored procedure xp_cmdshell

    CREATE TABLE #TEMP(FileList Varchar(MAX))
    
    INSERT INTO #TEMP
    EXEC XP_CMDSHELL 'DIR "C:\FolderName"'
    
    IF EXISTS (SELECT * FROM #TEMP WHERE FileList LIKE '%Name of File%')
    BEGIN
    	Your code
    END


    Regards, Deven ------------------------------------------------------------

    Please vote if you find any of my post helpful.

    • Edited by dbp2784 Monday, June 21, 2010 8:36 PM Code Modification
    • Marked as answer by KJian_ Monday, June 28, 2010 9:49 AM
    Monday, June 21, 2010 8:35 PM
  • If you do decide to use xp_fileexist, you have to make sure the SQL Server service account has access to the directory where you will be looking for files.

    DECLARE @iFileExists INT
    
    EXEC master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\test.txt', 
     @iFileExists OUTPUT
    
    SELECT @iFileExists

    If you are using xp_cmdshell, i recommend that you use /B to get only the file/directory name. (Thanks to Dbp2784 for the sample script).  Again the SQL Server Service accounts needs access to the file or directory.

    CREATE TABLE #TEMP(FileList Varchar(MAX))
    
    INSERT INTO #TEMP
    EXEC XP_CMDSHELL 'DIR "C:\test.txt" /B', NO_OUTPUT
    
    IF EXISTS (SELECT * FROM #TEMP WHERE FileList = 'test.txt')
    BEGIN
    	SELECT 1
    END
    

    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi NModerator Monday, June 21, 2010 10:18 PM
    • Marked as answer by KJian_ Monday, June 28, 2010 9:49 AM
    Monday, June 21, 2010 10:03 PM

All replies

  • This is normally best done outside of SQL Server. If you do not have any other choice, then here is an article that shows some methods for accessing the file system. You can use similar approach to get list of files in a folder and check if particular file exists.

    http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-text-files/

     

    Other options are to use CLR or the xp_fileexist extended stored procedure:

    http://www.mssqltips.com/tip.asp?tip=1272



    Plamen Ratchev
    • Marked as answer by KJian_ Monday, June 28, 2010 9:49 AM
    Monday, June 21, 2010 8:34 PM
  • Hi,

    You will have to do this using extended stored procedure xp_cmdshell

    CREATE TABLE #TEMP(FileList Varchar(MAX))
    
    INSERT INTO #TEMP
    EXEC XP_CMDSHELL 'DIR "C:\FolderName"'
    
    IF EXISTS (SELECT * FROM #TEMP WHERE FileList LIKE '%Name of File%')
    BEGIN
    	Your code
    END


    Regards, Deven ------------------------------------------------------------

    Please vote if you find any of my post helpful.

    • Edited by dbp2784 Monday, June 21, 2010 8:36 PM Code Modification
    • Marked as answer by KJian_ Monday, June 28, 2010 9:49 AM
    Monday, June 21, 2010 8:35 PM
  • If you do decide to use xp_fileexist, you have to make sure the SQL Server service account has access to the directory where you will be looking for files.

    DECLARE @iFileExists INT
    
    EXEC master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\test.txt', 
     @iFileExists OUTPUT
    
    SELECT @iFileExists

    If you are using xp_cmdshell, i recommend that you use /B to get only the file/directory name. (Thanks to Dbp2784 for the sample script).  Again the SQL Server Service accounts needs access to the file or directory.

    CREATE TABLE #TEMP(FileList Varchar(MAX))
    
    INSERT INTO #TEMP
    EXEC XP_CMDSHELL 'DIR "C:\test.txt" /B', NO_OUTPUT
    
    IF EXISTS (SELECT * FROM #TEMP WHERE FileList = 'test.txt')
    BEGIN
    	SELECT 1
    END
    

    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi NModerator Monday, June 21, 2010 10:18 PM
    • Marked as answer by KJian_ Monday, June 28, 2010 9:49 AM
    Monday, June 21, 2010 10:03 PM
  • Adding pointer to Adam's suggestion , By below approach you can avoid the over head of a #temp tabel.

    DECLARE @output INT

    EXEC @output = XP_CMDSHELL 'DIR "E:\temp\TextTest.txt" /B', NO_OUTPUT

    IF @output = 1

          PRINT 'File Donot exists'

    ELSE

          PRINT 'File exists'

     

    • Marked as answer by KJian_ Monday, June 28, 2010 9:49 AM
    Tuesday, June 22, 2010 5:41 AM
  • Adding pointer to Adam's suggestion , By below approach you can avoid the over head of a #temp tabel.

    DECLARE @output INT

    EXEC @output = XP_CMDSHELL 'DIR "E:\temp\TextTest.txt" /B', NO_OUTPUT

    IF @output = 1

          PRINT 'File Donot exists'

    ELSE

          PRINT 'File exists'

     

    Declare @cmdShellCommand varchar(400) = 'DIR "' + @BackupDirectory +'" /B'  
    EXEC @output = XP_CMDSHELL @cmdShellCommand, NO_OUTPUT

    Thank you for your code! (I know this is 3+ years old now)

    I had a requirement to disallow hardcoding of the path and use a variable instead. This is how I accomplished that. Hope it helps someone else.

    Tuesday, January 28, 2014 4:20 PM