EXEC master..xp_fileexist doesn't work for files on a networkdrive?

Question

EXEC master..xp_fileexist doesn't work for files on a networkdrive?

R. van Laake

SSCommitted

Points: 1533
More actions
September 10, 2008 at 3:56 am

#193333

Hi guru's,
I made a SP that imports data from a spreadsheet, located somewhere on the network not on the computer running SQLServer; lets say Z:\mysheet.xls. It works fine (meaning: SQLServer finds the file and reads from it).
I want to add to this SP that first it checks if the given file exists. But the SP EXEC master..xp_fileexist 'a filename' gives a wrong result when the file is on a networked drive!
For example:
EXEC master..xp_fileexist 'c:\mysheet.xls' -> result is "file exists" -> this is correct
EXEC master..xp_fileexist 'z:\mysheet.xls' -> result is "file does not exist" -> this is not correct!
Why is this? And, how can I check if a file on a networked drive exists?
Thanks!

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

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

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Does the service account that SQL Server runs under have permissions to the networked drive?

If you are running SQL Server under the local system account, it will have no access to anything off the box it is running on.

R. van Laake SSCommitted Points: 1533 More actions · Answer 2

Does the service account that SQL Server runs under have permissions to the networked drive?

Yes it has... as I started with, the SP that imports data from that file works well.

Thanks,

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

Yes it has... as I started with, the SP that imports data from that file works well

Oops, sorry - helps if I read the whole post.:Whistling:

xp_fileexists won't work with networked drive letters, but it will work if you use the UNC share name.

R. van Laake SSCommitted Points: 1533 More actions · Answer 4

Oops, sorry - helps if I read the whole post.

I forgive thee....:D

Works like a charm, thanks very much!

Raymond

rksindwani SSC Enthusiast Points: 153 More actions · Answer 5

Hi!

I'm reading a file on a mapped network drive (Z:\log.txt) using Bulk Insert on the server. I'm neither able to read it nor able to check its existence. All this using SQL Server 2000.

Pls help.

Tx.

Rishi.

Sergiy SSC Guru Points: 110208 More actions · Answer 6

Replace "Z:\" with full network path:

"\\Server\Folder\Folder\...\log.txt"

Must help.

_____________
Code for TallyGenerator

rksindwani SSC Enthusiast Points: 153 More actions · Answer 7

Hey Thanks!!

I used \\10.64.131.16\E:\ivr\calllog\55694\VCMIS\log.txt but even this isn't working.

Pls suggest.

Tx again.

Regards.

Sergiy SSC Guru Points: 110208 More actions · Answer 8

IP address does not belong here.

Use proper UNC notation.

_____________
Code for TallyGenerator

rksindwani SSC Enthusiast Points: 153 More actions · Answer 9

rksindwani

SSC Enthusiast

Points: 153

November 3, 2008 at 10:38 pm

#893427

How can I see the UNC notation on my server?

Sergiy SSC Guru Points: 110208 More actions · Answer 10

If you don't know what is UNC notation you may always google it.

_____________
Code for TallyGenerator

Peter Parker-354572 SSC Enthusiast Points: 111 More actions · Answer 11

Hello,

I have a strange problem , when I use :

declare @filefound int

exec master..xp_fileexist '\\bedata1\echanges\projets\datafile.txt', @filefound output

The result is 0 but file exist, because if I do a bulk insert, it's ok :

BULK INSERT MyTable FROM '\\bedata1\echanges\projets\datafile.txt'

exec master..xp_fileexist 'P:\projets\datafile.txt', @filefound output

0

exec master..xp_fileexist '\\10.164.3.100\echanges\projets\datafile.txt', @filefound output

0

??? Can you help me ?

I tried with unc name, drive network letter and IP Adress of my server and xp_fileexist return always 0

I'm working on server and I can access to this file ...

Carl Federl One Orange Chip Points: 25384 More actions · Answer 12

IP address will work but you must reference a share on the remote server and the SQL Server service account must have access to that share. Each partitions has a default Administrative share,which is referenced with the partition letter followed by a dollar sign ( $ )

Example:

EXEC master..xp_fileexist '\\10.218.4.71\C$\boot.ini'

Results are:

File Exists File is a Directory Parent Directory Exists

----------- ------------------- -----------------------

1 0 1

Please be aware that if the SQL Server service account does not have access to the share, then "xp_fileexist" will report that the file does not exist and the return code indicates success. "xp_cmdshell" however,does return a non-zero return code.

declare @sprc int

EXEC @sprc = master..xp_cmdshell 'dir \\10.218.32.159\c$\boot.ini'

select @sprc as CmdShell_ReturnCode

output

--------------------------------------------------

Logon failure: unknown user name or bad password.

NULL

CmdShell_ReturnCode

-------------------

1

SQL = Scarcely Qualifies as a Language

atsql Right there with Babe Points: 740 More actions · Answer 13

atsql

Right there with Babe

Points: 740

June 11, 2009 at 10:48 am

#1008151

You recheck path:

portion "E:\" does not look right.

Peter Parker-354572 SSC Enthusiast Points: 111 More actions · Answer 14

I does not function :crying:

I treid all ... and I don't understand

I use SQL Server 2005 and in my server SQL Server 2000 that is function ...

I changed my account, permissions on this machine, ... nothing function !

???