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

[Colin Burton]

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

Colin

 

[Chris Miller]

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.

Chriss

 

[gmmastros]

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'

Results

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.

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