Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Check if Files exsist in Table

Author  Topic 

zero1de
Posting Yak Master

105 Posts

Posted - 2014-08-19 : 09:24:11
Hi all,

I must check if all files from C:\Import\*.doc were processed and imported in to a database Table Filename.
If the Filename exists in the Table Filename then it can be deleted from the Folder C:\Import otherwise the file must remain in the folder.
Can anyone Help me ?

Many Thx.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-19 : 12:53:05
I would not use T-SQL for this. I would probably use SSIS or would build this into the application. You do not want to open the security hole of having SQL Server access the file system through T-SQL.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

zero1de
Posting Yak Master

105 Posts

Posted - 2014-08-19 : 13:42:34
Hi,

why not? I have found this code here. So in that way should resolve my Problem. But i need to check many Files and not only one. Maybe with a loop or Cursor ?


SET NOCOUNT ON

DECLARE @FileName VARCHAR(255)

DECLARE @Results TABLE
(
[File Exists] INT,
[File is a Directory] INT,
[Parent Directory Exists] INT
)

SELECT @FileName = 'C:\Backup\readme.txt'

INSERT @Results
EXEC MASTER..xp_fileexist @FileName

IF EXISTS (SELECT 1 FROM @Results WHERE [File Exists] = 1)
PRINT 'File Found'
ELSE
PRINT 'File Not Found'


SET NOCOUNT OFF
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-19 : 13:52:58
Yes it can be done by T-SQL, but that doesn't mean that it should be. xp_fileexists is undocumented by Microsoft in Books Online. There are better ways of achieving what you want. Application code, Powershell, SSIS for instance.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -