Post reply

XP_FileExists

  • April 25, 2007 at 5:11 am

    #118429

    Hi All,

    I am currently writing scripts to move and copy a various selection of files from 1 Server to another.

    Everything works fine, however, I just want to know if I can remove unnecessary information as it makes me scroll down the page to view my results.

    To check to see if a file exists I use the extended sp xp_fileexists, example as follows

    SET NOCOUNT ON

    DECLARE @iFileExists INT

    EXEC master..xp_fileexist 'D:\test.txt',

     @iFileExists OUTPUT

    PRINT @iFileExists

    All I want to do is capture True or False with the @iFileExists variable.  The results returned are

    (1 row(s) affected)

    0

    All I would like to have returned is 0 and not the (1 row(s) affected).

    Does anyone know a way to remove this unnecessary information.

    Thanking you in advance

    Darren

  • April 25, 2007 at 7:15 am

    #702364

    If you ran it with the SET NOCOUNT ON statement, then the system message should have been suppressed.

  • April 25, 2007 at 8:44 am

    #702396

    Hi Paul,

    I do run it with the SET NOCOUNT ON statement, which as you said should suppress the message, however in this case it does not.

    I figured it has something to do with the fact its an extended SP but not sure.

    This is the code I run

    SET NOCOUNT ON

    DECLARE @iFileExists INT

    EXEC master..xp_fileexist 'D:\test.txt',

     @iFileExists OUTPUT

    PRINT @iFileExists

    Darren

  • April 25, 2007 at 9:07 am

    #702402

    Try this.

    SET NOCOUNT ON

    DECLARE @iFileExists INT

    create table #table (File_exists int, File_directory int,parent_dir int)

    insert into #table EXEC master..xp_fileexist 'D:\test.txt'

    select @iFileExists=File_exists from #table

    PRINT @iFileExists

    drop table #table

    Thanks,

    SR

     

     

    Thanks,
    SR

  • April 26, 2007 at 3:42 am

    #702552

    Thanks SR that approach will def work.

    Darren

  • April 27, 2007 at 4:36 pm

    #703082

    IF you're going to use an undocumented sproc, why not go all the way and get ALL the filenames at once?

     CREATE TABLE #Directory (RowNum INT IDENTITY(1,1),FileName SYSNAME,Depth INT,IsFile INT)

     INSERT INTO #Directory (FileName,Depth,IsFile)

       EXEC Master.dbo.xp_DirTree 'C:\',1,1

     SELECT *

       FROM #Directory

      WHERE IsFile = 1

       DROP TABLE #Directory

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 6 posts - 1 through 5 (of 5 total)

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