Как лучше всего проверить, существует ли файл из хранимой процедуры SQL Server 2005?

Вам придется отметить CLR как EXTERNAL_ACCESS, чтобы получить доступ к пространству имен System.IO,однако по мере развития событий это не так уж плохо.

SAFE-это набор разрешений по умолчанию, но он очень ограничен. С параметром SAFE можно получить доступ только к данным из локальной базы данных для выполнения вычислительной логики на этих данных. EXTERNAL_ACCESS - это следующий шаг в иерархии разрешений. Этот параметр позволяет получить доступ к внешним ресурсам, таким как файловая система, Средство просмотра событий Windows и веб-службы. Этот тип доступа к ресурсам невозможен в SQL Server 2000 и более ранних версиях. Этот набор разрешений также ограничивает такие операции, как доступ к указателю, которые влияют на надежность вашего assembly. Набор разрешений UNSAFE предполагает полное доверие assembly и, таким образом, не накладывает ограничений "Code Access Security". Этот параметр сопоставим с тем, как функционируют расширенные хранимые процедуры—предполагается, что весь код безопасен. Однако, этот параметр ограничивает создание сборки с набором разрешений unsafe, чтобы пользователи, которые имеют разрешения sysadmin. Корпорация Майкрософт рекомендует по возможности избегать создания небезопасных сборок.

Возможно, хранимая процедура CLR - это то, что вы ищете. Они обычно используются, когда вам нужно каким-то образом взаимодействовать с системой.

Я все еще считаю, что процедура CLR может быть лучшим выбором. Так что я принимаю этот ответ. Однако либо я не настолько умен, либо это чрезвычайно трудно реализовать. Наша служба SQL Server работает под локальной учетной записью, потому что, согласно Mircosoft, это единственный способ заставить iSeries linked server работать с 64-разрядным экземпляром SQL Server 2005. Когда мы меняем службу сервера SQL на запуск с доменной учетной записью, команда xp_fileexist отлично работает для файлов, расположенных в сети.

Я создал эту хранимую процедуру CLR и построил ее с уровнем разрешений, установленным на External, и подписал ее:

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 для выполнения из SSMS, вошедшего в систему с проверкой подлинности windows:

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) каким-то образом не поливает путь из шланга?