SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Use xp_fileexist against multiple files?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 04/26/2012 :  06:34:29  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/26/2012 :  09:43:44  Show Profile  Visit russell's Homepage  Reply with Quote

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
Go to Top of Page

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 04/26/2012 :  09:57:25  Show Profile  Reply with Quote
Thanks for that. Doesn't work though. It just returns 1 every time.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 04/26/2012 :  10:14:31  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 04/26/2012 :  10:50:03  Show Profile  Reply with Quote
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...

Edited by - R on 04/26/2012 10:51:09
Go to Top of Page

robvolk
Most Valuable Yak

USA
15675 Posts

Posted - 04/26/2012 :  11:31:39  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

R
Constraint Violating Yak Guru

United Kingdom
328 Posts

Posted - 04/26/2012 :  12:48:06  Show Profile  Reply with Quote
Great stuff - thanks very much!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000