How do I verify my directory paths in my user tables

• Eli Hardy

  Grasshopper

  Points: 20

  More actions

  January 31, 2005 at 1:33 pm

  #183959

  Is there a script that can be built to verify the existance of a file, assuming the file's directory is given in a specified field in a user table? 

  For example:

  I have a locator Column in my Media.dbo user table.  In that column for every entry I have a directory path (ex: //BCFILEMGR/Mediafiles/BCXP1/test.omf) Can you have SQL verify that the "test.omf" file actually exists at the location given? Assuming the volume indicated is mounted on the server.

   

  Thanks in advance

  'hboypro

• hoo-t

  SSChampion

  Points: 12713

  More actions

  January 31, 2005 at 1:55 pm

  #539794

  There is an undocumented extended stored procedure that you can use for this.  It is xp_fileexist and resides in the master database.  Following is a sample execution -

  exec master..xp_fileexist 'x:\SQL_Backups\test.BKP'

  If the file exists, you will receive a '1' in the File Exists "column" of the output.

  Here's a sample of its use in a stored procedure -

       EXEC master..xp_fileexist @DestFile, @result output

       IF (@result = 0) 

        BEGIN

  -- Steve

• Eli Hardy

  Grasshopper

  Points: 20

  More actions

  January 31, 2005 at 3:24 pm

  #539819

  Great, that's what I needed.

   

  Thanks.