Checking for file existence

[MMund]

Is there a way for TSQL to determine if a .TXT file exists?

TIA,

MMund

 

[Neil Toulouse]

check thread183-1424634 see if that helps!

I like work. It fascinates me. I can sit and look at it for hours...

 

[PGO01]

Here is one way. You create a temporary table and fill it with the contents of a directory, then check the table for your file name, and finally delete your temporary table.

Just replace '%test.txt%' with your filename (keep the percentage signs) and replace 'dir C:\' with 'dir [yourpath]'

SET NOCOUNT ON

CREATE TABLE #tbl (tblcontents VarChar(2000)) 

INSERT INTO #tbl exec master.dbo.xp_cmdshell 'dir C:\'

IF EXISTS (SELECT * FROM #tbl WHERE tblcontents LIKE '%test.txt%')
    SELECT 'exists'
ELSE
    SELECT 'does not exist'

DROP TABLE #tbl

 

[mrdenny]

That method requires that xp_cmdshell be enabled, and that the user have the rights to use it. Try using xp_fileexist instead.

Info can be found here and here.

xp_cmdshell should be used as a method of last resort due to the potential security issues which arise from making it available.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[PGO01]

Denny is right, I've just found this myself. In the context of your query, you can use this:

declare @result int

exec xp_fileexist ‘filename’, @result output

IF (@result = 1)
    bulk insert tablename from 'filename'
else
  -- report that file does not exist