Using xp_fileexists with UNC path

[jmikow]

I am trying to get a query to run which calls the xp_fileexists function to see if a file exists. I am trying to pass a UNC path and concatenate that with one of the columns of data, but I keep getting an error at the "\" character for UNC or the ":" character for my local drive.

Can someone help me figure out where the issue is? I'm pretty sure it lies in all of the ' I have in the query.

The plan is to eventually move this code into a SP or UDF to be run from an ASP.Net page.

Also, any suggestions on how to improve the performance of this code would be appreciated.

TIA,

Josh

declare @ddlSortBy varchar(25)
declare @ddlSortDir nvarchar(3)
declare @SearchFor varchar(25)
declare @ContactID Int
declare @ClientID Int
declare @Site char(1)

set @ddlSortDir = 'a'
set @ddlSortBy = 'DateEntered'
set @ContactID = 0
set @ClientID = 143
set @SearchFor = '365'
set @Site = 'C'

declare @cols varchar(1000)
declare @tables varchar(100)
declare @where varchar(500)
declare @sortby varchar(50)
declare @sortdir varchar(50)
declare @stmt varchar(1500)
declare @ClientIDtxt nvarchar(5)
declare @SitePath varchar(100)

set @SitePath = case when @Site = 'C' then '\\\\asp01\\CSAAImages\\'
			when @Site = 'P' then '\\\\asp01\\ClientAccessImages\\'
			when @Site = 'D' then '\\\\asp01\\ClientAccessImages\\'
		End

set @ClientIDtxt = CAST(@ClientID as nvarchar(5))
set @sortby = @ddlSortBy
set @sortdir = @ddlSortDir

set @cols = 'select invoice_no,
		case when OnHold = ''Y'' then ''On Hold'' else convert(varchar(10),dateentered, 110) end as [Date Entered],
		isnull(convert(varchar(10),date_billed, 110),'' '') as [Date Billed],
		claim_no as [Claim Number],
		records_re as [Subject Name],
		ltrim(rtrim(ltrim(rtrim(s.first_name)) + '' '' + ltrim(rtrim(s.last_name)))) as [Source],
		ltrim(rtrim(ltrim(rtrim(s.city)) + '', '' + ltrim(rtrim(s.state)))) as [Source City],
		ltrim(rtrim(ltrim(rtrim(w.plaintiff)) + '' vs. '' + ltrim(rtrim(w.defendant)))) as [Case Title],
		f.f_DocumentID as [Doc ID],
		left(w.subpoena_type,1) as [SubType],
		w.date_due as [DateDue],
		xp_fileexists(' + '' + @SitePath + '' + ' rtrim(f.f_DocumentID) + ''.tif'')'
--		xp_fileexists(' + @SitePath + 'rtrim(f.f_DocumentID) + ''.tif'')'

set @tables = ' from worksheets w,
			sources s,
			clients c,
			FortisApplebyRecordsRep.sysadm.DailyWork f'

set @where = ' where w.client_id = ' + @ClientIDtxt +
		' and w.dateentered >= DateAdd(day,-' + @SearchFor + ',GetDate())
		and w.bill_id = c.client_id
		and (left(rtrim(ltrim(c.client_last_name)),4) = ''CSAA''
			or left(rtrim(ltrim(c.client_last_name)),21) = ''CALIFORNIA STATE AUTO'')
		and s.source_no = w.source_id
		and w.invoice_no = f.invoiceno'

set @sortby = Case when @sortby = 'DateEntered' then ' order by [Date Entered]'
            	   when @sortby = 'DateBilled' then ' order by [Date Billed]'
	           when @sortby = 'SubjectName' then ' order by [Subject Name]'
	           when @sortby = 'ClaimNumber' then ' order by [Claim Number]'
	           when @sortby = 'Source' then ' order by [Source]'
	      End

set @sortdir = Case when @sortdir = 'a' then ' asc'
            	    when @sortdir = 'd' then ' desc'
	       End

set @stmt = @cols + @tables + @where + @sortby + @sortdir

exec (@stmt)

 

[hmckillop]

is the function not xp_fileexist as opposed to xp_fileexists.
Also you have a space in the line just before the rtrim, which will make you name invalid.

xp_fileexists(' + '' + @SitePath + '' + ' rtrim(f.f_DocumentID) + ''.tif'')'

"I'm living so far beyond my income that we may almost be said to be living apart

 

[jmikow]

You're right, it is xp_fileexist. I typed it wrong.

I fixed that and got my quotations working properly, but now I am getting an error that xp_fileexist is not a recognized function name. Do I need to have the xp_fileexist function in my database, or will it find it in master if I'm in a separate db?

Here's the new line of code I'm using:

		xp_fileexist(' + '''' + @SitePath + 'rtrim(f.f_DocumentID)' + '.tif' + ''')'

 

[hmckillop]

try quantifying the database i.e.

master..xp_fileexist(' + '''' + @SitePath + 'rtrim(f.f_DocumentID)' + '.tif' + ''')'

"I'm living so far beyond my income that we may almost be said to be living apart

 

[jmikow]

I have tried that before and received an error:

"Invalid column prefix 'master.': No table name specified"

I took out the ( and ) because it did not like those once I added the master.. to the code.

My code looks like:

		master..xp_fileexist ' + '''' + @SitePath + 'rtrim(f.f_DocumentID)' + '.tif' + ''''