Post reply

Check if a file exists ...

  • 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.

  • May 18, 2004 at 8:49 am

    #506799

    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

  • May 19, 2004 at 12:23 am

    #506895

    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

  • May 19, 2004 at 3:16 pm

    #507040

    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

  • May 19, 2004 at 7:01 pm

    #507046

    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

  • May 19, 2004 at 7:05 pm

    #507048

    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.

  • May 19, 2004 at 7:15 pm

    #507049

    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

  • May 19, 2004 at 7:21 pm

    #507051

    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!!

  • May 19, 2004 at 7:48 pm

    #507052

    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

  • May 19, 2004 at 9:10 pm

    #507054

    Thanks Julian

  • May 19, 2004 at 10:11 pm

    #507056

    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

  • May 19, 2004 at 10:23 pm

    #507059

    Thats great mslava.

    I'll give it a try

  • May 20, 2004 at 3:13 am

    #507082

    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.

  • May 20, 2004 at 3:26 am

    #507084

    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

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

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