Check the files exist before the result of sql query

I have a website with admin panel, which people can upload files and data. My website is on 2 servers and use nlb, and also two servers are synced together with dfs, also I have 2 SQL Server on both servers and they are synced too.

The problem is syncing files on both servers take sometimes,
Now just imagine I have a table with these fields:

  • How to map Image type in NHibernate?
  • Is it possible to define error message for UNIQUE KEY constraint in SQL SERVER when it is called by EF 4.0?
  • simple way to know the updates in table
  • Querying data by joining two tables in two database on different servers
  • SQL - find total records that occur before another
  • How to reference a custom field in SQL
  • Name, price, fileName

    File address is point to somewhere of physical disk which is synced in 2 servers.

    Now imagine the website run this query:

    Select * from myTable

    How can I tell SQL that just show that records which the fileName field exists in physical disk?

    Note: I want it to do it with SQL, not in my application.

    One Solution collect form web for “Check the files exist before the result of sql query”

    There are two options:

    1. You already mentioned it. It is a good idea to add a column (FileExists bit (Y/N)) which tells you directly the status of physical copy.

    2. Second option is tedious as you need to create custom logic which identifies the physical copy at runtime as per the first commented link given by @MichaƂKomorowski.

    Ideally a database is used to store data and communicate with the application. Outer world interfaces within a database will be tedious work as well as decrease performance.

    For example, if you implement a function to check physical file and in select query as above you want, you used that function, so extra headache to checking the status will definitely increase the time and performance decrease. Just think again.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.