SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


File Exists using network path


File Exists using network path

Author
Message
joepacelli
joepacelli
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 291
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
mrdenny
mrdenny
Say Hey Kid
Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)

Group: General Forum Members
Points: 701 Visits: 224
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
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212095 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Jones
Steve Jones
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: Administrators
Points: 144630 Visits: 19424
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
My Blog: www.voiceofthedba.com
joepacelli
joepacelli
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 291
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
Steve Jones
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: Administrators
Points: 144630 Visits: 19424
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
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)SSC Guru (212K reputation)

Group: General Forum Members
Points: 212095 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35046 Visits: 11359
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
SQLblog.com
@SQL_Kiwi
mrdenny
mrdenny
Say Hey Kid
Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)Say Hey Kid (701 reputation)

Group: General Forum Members
Points: 701 Visits: 224
Using xp_cmdshell pretty much isn't ever a good idea, as for security reasons xp_cmdshell should be disabled.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35046 Visits: 11359
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search