FileExists stopped working - SQL Server Forums

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)

FileExists stopped working

Author

Topic

dlh
Starting Member

28 Posts

Posted - 2010-12-13 : 22:35:50

I have a stored function that returns 1 if the file specified by @Path exists and 0 if it doesn't. This previously worked, but recently it started returning 0 for all files.

create function [dbo].[FileExists] 
	( @Path varchar(500) )
returns int
as

begin

declare @objFSys int
declare @Result int

exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @Result out, @Path 
exec sp_OADestroy @objFSys 

return @Result

end

I tried checking for reported errors with the following, but apparently no error codes are being thrown.

exec sp_OAGetErrorInfo @objFSys, @Source out, @Description out

Can anyone suggest a reason this would stop working? And can anyone suggest a better way of doing this for an entire column of Paths?

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-12-13 : 23:26:14

I am not familiar with that procedure, but SQL Server runs all file system operations as the account set to run the service, so the most obvious things to check are the following.

1. You are using a path that is available on the machine running your sql instance.
2. That the Account that is running the service has access to view that file.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-12-13 : 23:53:21

What's wrong with the builtin extended stored procedure: xp_fileexists?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dlh
Starting Member

28 Posts

Posted - 2010-12-16 : 19:16:34

Thanks, Tara, for the reminder about xp_fileexist. I originally avoided it because it is "undocumented".

But I recently discovered a new problem with it. It incorrectly reports that any file with a path longer than 255 characters does not exist. (It's filenames that are limited to 255 characters; see [url]http://msdn.microsoft.com/en-us/library/aa365247.aspx[/url].)

And thanks for the sanity check, Vinnie. Since xp_fileexist does work for shorter filenames, I think the problem with my original function was not related to permissions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources