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 File Exists

Author  Topic 

pattonjo
Starting Member

11 Posts

Posted - 2013-07-28 : 18:56:00
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

549 Posts

Posted - 2013-07-28 : 20:14:38
[CODE]

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

[/CODE]

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

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

[/CODE]
Go to Top of Page

pattonjo
Starting Member

11 Posts

Posted - 2013-07-28 : 23:02:00
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)

17689 Posts

Posted - 2013-07-28 : 23:10:36
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
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

pattonjo
Starting Member

11 Posts

Posted - 2013-07-28 : 23:45:52
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)

17689 Posts

Posted - 2013-07-29 : 03:17:30
use while loop or cursor to loop through your records and check one by one


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-29 : 05:06:02
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
   

- Advertisement -