What is the best method for checking if a file exists from a SQL Server 2005 stored procedure?

Question

We used the "undocumented" xp_fileexist stored procedure for years in SQL Server 2000 and had no trouble with it. In 2005, it seems that they modified the behavior slightly to always return a 0 if the executing user account is not a sysadmin. It also seems to return a zero if the SQL Server service is running under the LocalSystem account and you are trying to check a file on the network.

I'd like to get away from xp_fileexist. Does anyone have a better way to check for the existence of a file at a network location from inside of a stored procedure?

Answer 1

You will have to mark the CLR as EXTERNAL_ACCESS in order to get access to the System.IO namespace, however as things go that is not a bad way to go about it.

SAFE is the default permission set, but it’s highly restrictive. With the SAFE setting, you can access only data from a local database to perform computational logic on that data. EXTERNAL_ACCESS is the next step in the permissions hierarchy. This setting lets you access external resources such as the file system, Windows Event Viewer, and Web services. This type of resource access isn’t possible in SQL Server 2000 and earlier. This permission set also restricts operations such as pointer access that affect the robustness of your assembly. The UNSAFE permission set assumes full trust of the assembly and thus imposes no "Code Access Security" limitations. This setting is comparable to the way extended stored procedures function—you assume all the code is safe. However, this setting does restrict the creation of unsafe assemblies to users who have sysadmin permissions. Microsoft recommends that you avoid creating unsafe assemblies as much as possible.

Answer 2

Maybe a CLR stored procedure is what you are looking for. These are generally used when you need to interact with the system in some way.