XP_FileExists Not Seeing File

• mitchismoney

  Mr or Mrs. 500

  Points: 533

  More actions

  August 25, 2008 at 11:14 am

  #125840

  Hello all, long time reader first time poster.

  My issue is this;

  I'm working with a script that is using xp_fileexists.

  When I run the most basic example:

  exec master.dbo.xp_fileexist 'c:\temp'

  I get the expected results:

  File Exists|File is a Directory|Parent Directory Exists

  0|1|1

  Now I turn around and run this looking for files on the network, using UNC:

  exec master.dbo.xp_fileexist '\\hopper\Database\Ford\Reporting\Dashboards\20080817\10772_DealerDashboard_20080817.pdf'

  I get the following results:

  File Exists|File is a Directory|Parent Directory Exists

  0|0|0

  If I cut and paste the above referenced file into the address bar of Windows Explorer the pdf opens, I can also paste up to the pdf portion and the directory opens, in which I can see all of the existing files.

  1) The file resides on a network share that all domain users have access to.

  2) SQL Server 2005

  3) SQL Login via Windows Authentication

  4) I have verified the file can be opened from the server (via remote desktop)

  Am I possibly still looking at some sort of security/rights issue?

  Thanks in advance,

  Mitchell

  _______________________________________________________________
  Its not how far you go, its how go you far...:crazy:

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  August 25, 2008 at 11:36 am

  #862684

  Problems like this are usually security/access restrictions on the Windows Login that SQL Server is executing the file access under. What account that is, depends on the installation and configuration of your SQL Server (but it is not your windows account).

  [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
  Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

• Lowell

  SSC Guru

  Points: 323546

  More actions

  August 25, 2008 at 11:52 am

  #862693

  Rbarryyoung is exactly right...typically, the account used for running the SQL server service never logs into the domain, and so does not have permissions/cannot browse to a different machine on the network.

  you need to go to Administrative tools/Services, find the SQL Server in question, and change the username/pw to someone who CAN browse the networks objects, whether yourself or a domain accout you specifically set up for this purpose.

  

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mitchismoney

  Mr or Mrs. 500

  Points: 533

  More actions

  August 25, 2008 at 11:59 am

  #862697

  Thanks guys, I'll pop into the server and make some adjustments and let you know how it goes.

  Thanks again

  _______________________________________________________________
  Its not how far you go, its how go you far...:crazy: