SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Check if Files exsist in Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zero1de
Yak Posting Veteran

Germany
87 Posts

Posted - 08/19/2014 :  09:24:11  Show Profile  Reply with Quote
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

USA
37296 Posts

Posted - 08/19/2014 :  12:53:05  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Yak Posting Veteran

Germany
87 Posts

Posted - 08/19/2014 :  13:42:34  Show Profile  Reply with Quote
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

Edited by - zero1de on 08/20/2014 09:50:50
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37296 Posts

Posted - 08/19/2014 :  13:52:58  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000