Use xp_fileexist against multiple files? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Use xp_fileexist against multiple files?

Author

Topic

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-04-26 : 06:34:29

I'm trying to use xp_fileexist against list of files. After reading the documentation I could find, I came up with the idea of creating a function then using it in the column list. The code previously displayed a list of filenames:


3454.pdf
7435.pdf
213.docx
6442.pdf
2363.docx


create function myfn_FileExists 
	(
	@fullfilename nvarchar(max)
	) 
returns bit
AS
	BEGIN
		DECLARE @i int
		EXEC master..xp_fileexist @fullfilename, @i out
		RETURN @i
	END
GO



SELECT
	CASE WHEN se.ID IS NULL THEN 
		NULL
	ELSE
		CAST(se.ID as NVARCHAR(MAX)) + se.fileExt
	END as [File Name],
	dbo.myfn_FileExists ('C:\Users\SomeDude\Desktop\Docs\' + CAST(se.ID as NVARCHAR(max)) + se.fileExt) as [File Exists]
FROM
	.....
GO

Although the code compiles and runs, the [File Exists] column always shows a NULL value. Can anyone spot my mistake?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-04-26 : 09:43:44

[code]
alter function myfn_FileExists
(
@fullfilename nvarchar(max)
)
returns bit
AS
BEGIN
DECLARE @i int
set @i = -1
EXEC master..xp_fileexist @fullfilename, @i out
RETURN @i
END
GO[/code]

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-04-26 : 09:57:25

Thanks for that. Doesn't work though. It just returns 1 every time.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-26 : 10:14:31

Yeah, -1 will convert to a 1 as a bit.

Looks like the choice of parameter type is the problem, because this works:

alter function dbo.myfn_FileExists 
	(
	@fullfilename varchar(512)
	) 
returns bit
AS
	BEGIN
		DECLARE @i int
		EXEC master..xp_fileexist @fullfilename, @i out
		RETURN @i
	END

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-04-26 : 10:50:03

Hi robvolk

You've solved it - nice work.
Here's the big question then, why does nvarchar(max) not work, but nvarchar(512) work perfectly?!? In fact, anything up to nvarchar(4000) works well...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-04-26 : 11:31:39

Not sure, but you don't need nvarchar(max), UNC paths are limited to 32K in Windows, and 260 characters for NTFS. And if you have a path that even approaches 4000 characters, your folder structure is waaaaaaaay too complicated.

R
Constraint Violating Yak Guru

328 Posts

Posted - 2012-04-26 : 12:48:06

Great stuff - thanks very much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources