Checking if file exists or not

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 2005 Forums

Transact-SQL (2005)

Checking if file exists or not

Author

Topic

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-03-18 : 08:10:04

Greetings all.

How can I check for file exitence in a specified folder using TSQL?

I can think of a couple of ways to do this so any feedback would be appreciated.

Load the contents of a folder in to a temporay table and check the table for file name. I can do this with something like:


insert into myFiles
exec master..xp_cmdshell 'dir /B /OND filePath\*.txt'

Or maybe use OLE Automation Sproc? But I am not sure which method I should use.

Please advise.

Abu-Dina
Posting Yak Master

206 Posts

Posted - 2008-03-18 : 11:15:13

Okay, I've solved the problem. Thanks for looking anyway.


declare @Path		varchar(500)
,@objFSys		int
,@FileExistence		int
,@FilePath		varchar(1000)


exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @FileExistence out, @FilePath
exec sp_OADestroy @objFSys

If @FileExistence is 1 then file exists otherwise it doesn't. Great!