File Exists using network path

Question

File Exists using network path

joepacelli

SSCrazy

Points: 2088
More actions
December 12, 2009 at 5:38 pm

#228537

We are writing an application which is reading data from one database and moving into another database. In this one database we have a table which holds a path name to files. The data would look like the following
imageid path file
1 \\server\path\ file1.jpg
2 \\server\path\ file2.jpg
3 \\server\path\ file3.jpg
4 \\server\path\ file4.jpg
We concatenate these fields together into a variable called @path and then running the following logic
-- Does the file exist?
create table #fe (fileexists int, fileasdir int, parent int)
insert into #fe
exec master..xp_fileexist @path
if exists (select 1 from #fe where fileexists=0)
Begin
Select @MsgText = 'Scanned Document ' + @path + ' does not exist, scanid=' + CONVERT(Varchar,@imageid)
EXECUTE [conv].[WriteToLog] @ModuleName,@procedureName,@MsgText,'C', @rc
drop table #fe
Return
End
drop table #fe
The problem is this keeps returning that the file does not exist
If I run the following code from SSMS
exec master..xp_fileexist '\\server\path\file1.jpg'
it returns all zeros
File ExistsFile is a DirectoryParent Directory Exists
0 0 0
If I map this drive to y: and run it again as
exec master..xp_fileexist 'Y:\file1.jpg'
It returns
File ExistsFile is a DirectoryParent Directory Exists
1 0 1
I can go to IE and enter \\server\path and it goes there fine.
Why is this failing?
Joe

Viewing 15 posts - 1 through 14 (of 14 total)

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

mrdenny SSCrazy Points: 2898 More actions · Answer 1

Sounds like the account which is running the SQL Server is either a domain account, or doesn't have access to the network share.

In either case the account needs to be a domain account and will need to have read access to the network share for the xp_fileexists procedure to work.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

In other words, the account that SQL Server logs in with must have the necessary privs to actually "see" the paths that you're trying to explore using sp_FileExists.

--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)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 3

Mr Denny and Jeff have given you a good explanation. When you run something in SQL Server, using SSMS or your client, it doesn't run under your windows account, or from the perspective of your machine. It's submitted to SQL Server and then executed AS IF the SQL Server itself, or it's service account/proxy (depending on how you've implemented it) sees the files. Even if you are on the console, it's executed from the context of the service account.

joepacelli SSCrazy Points: 2088 More actions · Answer 4

I'm logged into the system as an administrator. The account I'm logged into SSMS as is a sysadmin account. And I went to the server where the files exist and looked at the security for this path and it has Everyone. So if it has Everyone shouldn't this be sufficient priviledges.

Joe

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

Again, it's not you, or the account you use in SSMS. It's the SERVICE account that SQL Server uses that executes things, not any of your accounts.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

Steve is correct. It's not what you're logged in as... it's what the server is logged in as. Do you know how to find which login SQL Server uses?

--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)

Paul White SSC Guru Points: 150467 More actions · Answer 7

Jeff Moden (12/13/2009)
Steve is correct. It's not what you're logged in as... it's what the server is logged in as. Do you know how to find which login SQL Server uses?

It seems (for current builds anyway) that is only true if the client logs in with 'sa' permissions - either as 'sa', or as a member of the sysadmin server role. 'Normal' users will have their own permissions checked, unless a proxy has been defined.

I must say though that using xp_fileexist is a bad choice. It is undocumented and its behaviour has changed at least once that I can remember. Other better choices include a CLR routine, xp_cmdshell, or sp_OA methods.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

mrdenny SSCrazy Points: 2898 More actions · Answer 8

Using xp_cmdshell pretty much isn't ever a good idea, as for security reasons xp_cmdshell should be disabled.

Paul White SSC Guru Points: 150467 More actions · Answer 9

mrdenny (12/14/2009)
Using xp_cmdshell pretty much isn't ever a good idea, as for security reasons xp_cmdshell should be disabled.

It has the distinct advantage of at least being documented ;-). xp_cmdshell is apparently acceptable in some organizations, especially if a proxy is used. My personal preference would be to use .NET routines (either completely outside the database, or using the hosted CLR). I do try to avoid file manipulation from SQL Server (it's a database!)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

mrdenny (12/14/2009)
Using xp_cmdshell pretty much isn't ever a good idea, as for security reasons xp_cmdshell should be disabled.

Properly setup proxies take care of such concerns... especially on non-public facing ETL systems where using some of the natural DOS commands make life very easy.

The only time I've seen someone get into hot water with xp_cmdshell proxies is when the passwords are given out. That, of course, includes unbridled SA access by developers and applications.

--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)

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 11

I'd tend to agree with Jeff. You can secure this pretty well these days, and in limited use, it makes sense. I wouldn't want someone to have unbridled access, especially with power windows level privileges, but I wouldn't completely say this is bad. Especially when combined with EXECUTE AS

alalexis210 SSC Rookie Points: 34 More actions · Answer 12

Long Path Tool deals with long path files. It works well to copy or delete long path files.

alalexis210 SSC Rookie Points: 34 More actions · Answer 13

Long Path Tool probably ends your problems in unlocking, managing and renaming files that appear to have a long filename.

muhammed.arour2006 Newbie Points: 9 More actions · Answer 14

Hello,

i habe the same Problem and i still have to add that i can rename files using cmdshell ren ... or copy , move ...

but by using the fileexist i get the same Message which is " the file does not exist" eventhough if it is there...

is it the same Problem ? and why can i rename , copy and move the files but cant know if files are there?

thanks in advance

Memo