Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

File Exists using network path Expand / Collapse
Author
Message
Posted Saturday, December 12, 2009 5:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 28, 2014 9:38 AM
Points: 33, Visits: 176
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 Exists File is a Directory Parent 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 Exists File is a Directory Parent Directory Exists
1 0 1

I can go to IE and enter \\server\path and it goes there fine.
Why is this failing?

Joe
Post #833420
Posted Saturday, December 12, 2009 6:33 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, July 12, 2014 12:00 PM
Points: 74, Visits: 206
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.
Post #833424
Posted Sunday, December 13, 2009 9:35 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 36,726, Visits: 31,175
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #833510
Posted Sunday, December 13, 2009 10:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:22 AM
Points: 33,055, Visits: 15,170
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #833518
Posted Sunday, December 13, 2009 1:26 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 28, 2014 9:38 AM
Points: 33, Visits: 176
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
Post #833535
Posted Sunday, December 13, 2009 4:16 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 9:22 AM
Points: 33,055, Visits: 15,170
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #833553
Posted Sunday, December 13, 2009 5:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:15 AM
Points: 36,726, Visits: 31,175
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #833566
Posted Sunday, December 13, 2009 7:50 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #833585
Posted Monday, December 14, 2009 1:15 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, July 12, 2014 12:00 PM
Points: 74, Visits: 206
Using xp_cmdshell pretty much isn't ever a good idea, as for security reasons xp_cmdshell should be disabled.
Post #833638
Posted Monday, December 14, 2009 1:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:36 AM
Points: 11,192, Visits: 11,091
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #833644
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse