Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Determine if a file exists on s different server to the SQL Server

Determine if a file exists on s different server to the SQL Server

Determine if a file exists on s different server to the SQL Server

HI All.

is it possible - and how, to determine if a file exists on a different server on the same network to my SQL Server

I have found out that i can't use xp_fileexists as for that to function the file in question must be on the same server.

I have a SQL Server with our Databases, and another server with some image files on. the database has a pointer filed to the folder / file name

I need to be able to run a query to ensure that the image file referred to in the table does actually exist in the filesystem of the "other" server

Thanks in Advance


RE: Determine if a file exists on s different server to the SQL Server

Well, I can't answer that directly. But SQL Server has introduced Filestream and file tables and it may be better to use these concepts instead of a combination of a table structure with filepath values and stored procs that verify the file actually exists by means of system procedures like xp_*.

With these new file related concepts that have been improved with SQL server versions, you let SQL Server handle directories and thus have that integrity checks by default without any ado. It just takes a bit, but less than a day, to dig into that new topic, set it up and work with it.


RE: Determine if a file exists on s different server to the SQL Server

You can use xp_fileexist, but there are things to consider.

First, you should understand that there are a lot of permissions involved with SQL Server. File system permissions are controlled by the SQL Server logon account. To see what you are currently using, open the services control panel, scroll down to "SQL Server". Right click this service, click properties. Click the "Log On" tab. Most of the time, this will be configured to use "Local System Account". With this configuration, you will only have access to files on the local server, and nowhere else.

You can change the "log on" to use any account you want, including one where you have access to network resources. If you do this, you can use xp_fileexist to check files on other servers. I just tested this to verify.

In my environment, the SQL Server is named SERVER-DATABASE.


EXEC Master.dbo.xp_fileexist '\\server-files\d$\install.log' 



File Exists File is a Directory Parent Directory Exists
----------- ------------------- -----------------------
1           0                   1 

Before you change the log on account, you MUST consider a few things....

If someone hacks your sql server, they would currently be limited to the sql server machine itself, but no network resources. If you change the account, they may be able to get to other resources on your network. Basically, changing the account expands your security vulnerability.

If you decide you are comfortable with that level of security risk, then you should create an account just for this purpose. This account should have full access to the database server, and access to the folder/share on your file server that you are interested in, and ONLY that folder. When you create this special account, give it a long/secure password. Also set the password to never expire.

Microsoft SQL Server MVP
My Blogs
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close