CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Lloyd Banks
Lloyd Banks

Reputation: 36709

XP_FileExists Does Not Work With Network Drives

I am able to run the xp_fileexists command successfully on the local drives of my SQL Server instance. If a file exists, I get an output of 1. If a file does not exists, the output is 0. When I run the same command on a network drive that is mapped on the SQL Server machine, the output is always 0.

For example, if I have a file with a URL of '\\10.188.20.5\myfolder\myfile.txt'. I would run 

SET @MYFILE = '\\10.188.20.5\myfolder\myfile.txt'
EXEC MASTER.DBO.XP_FILEEXIST @MYFILE, @MYOUTPUT OUT
PRINT @MYOUTPUT

The result would return 0.

If I run a bulk insert command on the same URL, the file would be successfully imported

BULK INSERT #mytable
FROM '\\10.188.20.5\myfolder\myfile.txt'

What is causing xp_fileexists to malfunction on network drives?

Upvotes: 1

Views: 13326

Answers (3)

posdev
posdev

Reputation: 1

Go to >> Sql Server Configuration Manager >> on left panel go to SQL Server Services >> select your own Instance (MyPc\Sqlexpress) and enter the account name, the username and the password.

now it should work

Upvotes: -1

Lloyd Banks
Lloyd Banks

Reputation: 36709

The problem turned out to be with the Admin account password used to run SQL Server. The password was changed, but SQL Server continued to run. It just happened 'MASTER.DBO.XP_FILEEXIST' didn't work because of the password conflict, but other SQL Server functions did. Once I changed the password associated with the Admin account used to run SQL Server, the function worked as expected.

Upvotes: 4

bummi
bummi

Reputation: 27394

It depends on the credetials of the Service usualy local system, which has not network access

Upvotes: 2

Related Questions