在SQL Server 2000中，我们使用"未公开说明"的xp_fileexist存储过程已经有很多年了，并且没有遇到任何麻烦。在2005年，似乎他们对行为进行了一些修改，如果执行用户帐户不是sysadmin，则始终返回0。如果SQL Server服务在LocalSystem帐户下运行，并且我们正在尝试检查网络上的文件，它似乎也返回零。

我想摆脱xp_fileexist。有没有人有更好的方法从存储过程内部检查网络位置的文件是否存在？

解决方案

回答

也许我们正在寻找CLR存储过程。这些通常用于需要以某种方式与系统交互的情况。

回答

为了访问System.IO命名空间，必须将CLR标记为EXTERNAL_ACCESS，但是随着事情的发展，这并不是一个坏方法。

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.

回答

我仍然相信CLR程序可能是最好的选择。所以，我接受这个答案。但是，或者我不够聪明，或者很难实施。我们的SQL Server服务在本地帐户下运行，因为根据Mircosoft所述，这是使iSeries链接服务器从64位SQL Server 2005实例运行的唯一方法。当我们更改SQL Server服务以使用域帐户运行时，xp_fileexist命令对于位于网络上的文件可以正常工作。

我创建了此CLR存储过程，并以将权限级别设置为"外部"进行了构建并对其进行了签名：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Principal;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void FileExists(SqlString fileName, out SqlInt32 returnValue)
    {
        WindowsImpersonationContext originalContext = null;

        try
        {
            WindowsIdentity callerIdentity = SqlContext.WindowsIdentity;
            originalContext = callerIdentity.Impersonate();

            if (System.IO.File.Exists(Convert.ToString(fileName)))
            {
                returnValue = 1;
            }
            else
            {
                returnValue = 0;
            }
        }
        catch (Exception)
        {
            returnValue = -1;
        }
        finally
        {
            if (originalContext != null)
            {
                originalContext.Undo();
            }
        }
    }
}

然后，我运行了以下TSQL命令：

USE master
GO
CREATE ASYMMETRIC KEY FileUtilitiesKey FROM EXECUTABLE FILE = 'J:\FileUtilities.dll' 
CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY FileUtilitiesKey 
GRANT EXTERNAL ACCESS ASSEMBLY TO CLRLogin 
ALTER DATABASE database SET TRUSTWORTHY ON;

然后，我将CLR存储的proc从Visual Studio部署到我的目标数据库，并使用此TSQL从使用Windows身份验证登录的SSMS执行：

DECLARE @i INT
--EXEC FileExists '\\server\share\folder\file.dat', @i OUT
EXEC FileExists 'j:\file.dat', @i OUT
SELECT @i

无论我尝试本地文件还是网络文件，我总是得到0。我可能稍后再试，但是现在，我将尝试走另一条路。如果任何人都有一些亮点，将不胜感激。

回答

@Paul，该代码看起来应该可以工作。我们是否尝试过在该方法中进行一些跟踪以确保Convert.ToString(fileName)不以某种方式保留路径？