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 File Exists
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pattonjo
Starting Member

USA
11 Posts

Posted - 07/28/2013 :  18:56:00  Show Profile  Reply with Quote
I have a table with two columns.
Name and FilePath (varchar(255)).

How can I query to check to see if that particular file exists, and only return "broken" paths.

MuMu88
Aged Yak Warrior

547 Posts

Posted - 07/28/2013 :  20:14:38  Show Profile  Reply with Quote


SELECT * From @TEMP WHERE Name like '%File1%'



What do you mean by "broken" paths?
like this:


SELECT * From @TEMP WHERE Name like '%File1%' and FilePath like '%broken%';


Go to Top of Page

pattonjo
Starting Member

USA
11 Posts

Posted - 07/28/2013 :  23:02:00  Show Profile  Reply with Quote
two columns

Name FilePath
ABC N:\files\abc.pdf
DEF N:\files\def.pdf
GHI N:\files\ghi.pdf

How can I query this to validate that the document exists at the given path. In other words, I want to return a dataset of all of the FilePaths where the document DOESN'T exist.

It would have to check the FilePath for each row to see if it exists or not, and return the ones that do not.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/28/2013 :  23:10:36  Show Profile  Reply with Quote
you can use extended stored procedure xp_fileexist

google for it, there are lots of example

here is a reference in this site
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=174009


KH
Time is always against us


Edited by - khtan on 07/28/2013 23:11:25
Go to Top of Page

pattonjo
Starting Member

USA
11 Posts

Posted - 07/28/2013 :  23:45:52  Show Profile  Reply with Quote
I can get it to return the correct bit when explicitly giving the filepath.

SET NOCOUNT ON
DECLARE @iFileExists INT

EXEC master..xp_fileexist 'N:\files\abc.pdf',
@iFileExists OUTPUT

PRINT @iFileExists


How can I use this to loop through a column and check each row, while displaying 'name', 'filepath', 'bit'
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 07/29/2013 :  03:17:30  Show Profile  Reply with Quote
use while loop or cursor to loop through your records and check one by one


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 07/29/2013 :  05:06:02  Show Profile  Reply with Quote
I would have done this using SSIS File System task.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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.06 seconds. Powered By: Snitz Forums 2000