Check if a file exists ...

Question

Post reply

Check if a file exists ...

andreask

Mr or Mrs. 500

Points: 542
More actions
May 18, 2004 at 2:19 am

#107926

Dear All,
Does anyone know how can I verify the existence of a specific file from a Stored Procedure?
i.e. I want to perform an action using the specific file, let's say "c:\MyFile.txt".
IF <c:\MyFile.txt> EXISTS
<PERFORM ACTION>
ELSE
<CONTINUE>
Any help is greatly appreciated.
Thanks,
Andreas
PS: I got the answer. I should use the undocumented Extended SP xp_fileexist.
EXEC master..xp_fileexist 'c:\MyFile.txt'
Thanks anyway.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

SteveB-74825 Hall of Fame Points: 3290 More actions · Answer 1

or you could use this

declare @result int

declare @exists bit

exec @result = sp_MSget_file_existence 'C:\myfile.txt', @exists = @exists output

print @exists

cheers

dbgeezer

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 2

DECLARE @fileexists int

EXEC master..xp_fileexist 'c:\myfile.txt', @fileexists OUT

IF @fileexists = 1 -- The file exists

PRINT 'File Exists' -- Do what you want here

Julian Kuiters
juliankuiters.id.au

sqlpro Hall of Fame Points: 3859 More actions · Answer 3

Hi Julian

i tried like following

DECLARE @fileexists int

EXEC master..xp_fileexist '\\work\C Drive\abcd.dbf', @fileexists OUTput

IF @fileexists = 1 -- The file exists

PRINT 'File Exists' -- Do what you want here

else

PRINT 'File does not Exist' -- Do what you want here

but it always going into "else "?? even though there is a file with that name(i use sql server 2000 dev)

am i missing anything

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 4

Most likely the problem is to do with the UNC path '\\work\C Drive\abcd.dbf'

SQL Server by default runs under the SYSTEM account, which does not have network access, so cannot access a UNC or \\ path.

Try just using 'C:\abcd.dbf' instead and it should work.

Julian Kuiters
juliankuiters.id.au

sqlpro Hall of Fame Points: 3859 More actions · Answer 5

Hi Julian

Actually i tried that one too but to no avail.

actually i am running the code from my workstation

(i.e work) but server is on different machine.

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 6

hmm. Remember that the sql is executed on the server, so:

A. the file must exist on the server, and

B. the SYSTEM user (or the user SQL Server logins in as) must have read permissions to the file.

Try getting SQL Server to see if a really commmon file exists like 'c:\config.sys' or 'c:\autoexec.bat' exists (check in explorer if you can see it). If this doesn't work, check that the permissions on those files allow SYSTEM read access.

SQL Server would not be able to find any files on your workstation, all the code you execute in sql server is run on the server.

Julian Kuiters
juliankuiters.id.au

sqlpro Hall of Fame Points: 3859 More actions · Answer 7

Hi Julian

It worked

By the way i'm wondering how can we check if file exist on network drive??

as u know in the abv example it always returns false and never know the file actually exists bcoz

sql server not returning any errors!!

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 8

To allow SQL Server to access network shares:

1. SQL Server service must login as a windows domain user

2. The Sql Server domain user must have 'Log on as a service'

3. The Sql Server domain user must have permissions to access the share and the file.

SQL Server doesn't throw an error, because that would break your programming flow. xp_fileexists only works when SQL Server can access the file, and has read permissions.

Julian Kuiters
juliankuiters.id.au

sqlpro Hall of Fame Points: 3859 More actions · Answer 9

sqlpro

Hall of Fame

Points: 3859

May 19, 2004 at 9:10 pm

#507054

Thanks Julian

mSlava SSC Veteran Points: 258 More actions · Answer 10

Not all declared will be used!

DECLARE @object int

DECLARE @hr int

DECLARE @property varchar(255)

DECLARE @return varchar(255)

DECLARE @src varchar(255), @desc varchar(255)

-- Create an object.

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @object OUT

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

RETURN

END

-- Call a method that returns a value.

EXEC @hr = sp_OAMethod @object, 'FileExists', @return OUT, 'c:\log.txt'

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

RETURN

END

PRINT @return

EXEC @hr = sp_OADestroy @object

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT

SELECT hr=convert(varbinary(4),@hr), Source=@src, <A href="mailtoescription=@desc">Description=@desc

RETURN

END

sqlpro Hall of Fame Points: 3859 More actions · Answer 11

sqlpro

Hall of Fame

Points: 3859

May 19, 2004 at 10:23 pm

#507059

Thats great mslava.

I'll give it a try

mSlava SSC Veteran Points: 258 More actions · Answer 12

Sorry for English.

Yes, It is great. But I don't understand when that OLE Object is installed. Probably with Win. If you know it, please tell me.

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 13

I would use mslava's method.

The problems with many of the earlier posts, which will tell you whether the file exists, is that they do not tell you whether you can open the file. For example, if a process is FTPing a large file to your server, it may take many minutes.

The solutions which essentially use a directory listing to determine whether the file exists, will give you a return code saying that the file does exist. If your next step then opens the file then it might fail if the file is still being copied to the server. mslava's method will give a non zero return code which you can put in a loop to wait for the file to be ready (with a small delay between checks) for opening.

Jeremy