XP_FileExists

Question

Post reply

XP_FileExists

darrenkelly

SSCommitted

Points: 1624
More actions
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

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

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

Paul Mu SSCertifiable Points: 5547 More actions · Answer 1

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

darrenkelly SSCommitted Points: 1624 More actions · Answer 2

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

sree-226516 SSCrazy Points: 2590 More actions · Answer 3

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

darrenkelly SSCommitted Points: 1624 More actions · Answer 4

darrenkelly

SSCommitted

Points: 1624

April 26, 2007 at 3:42 am

#702552

Thanks SR that approach will def work.

Darren

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

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)