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

Мы годами использовали «недокументированную» хранимую процедуру xp_fileexist в SQL Server 2000, и у нас не было проблем с ней. В 2005 году кажется, что они немного изменили поведение, чтобы всегда возвращать 0, если учетная запись исполняющего пользователя не является системным администратором. Также кажется, что он возвращает ноль, если служба SQL Server работает под учетной записью LocalSystem и вы пытаетесь проверить файл в сети.

Я хотел бы уйти от xp_fileexist. У кого-нибудь есть лучший способ проверить наличие файла в сетевом расположении внутри хранимой процедуры?

19.08.2008 18:01:43
4 ОТВЕТА
РЕШЕНИЕ

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

4
19.08.2008 18:13:13

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

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

5
19.08.2008 18:47:21

Я все еще верю, что процедура CLR может быть лучшим выбором. Итак, я принимаю этот ответ. Однако либо я не такой яркий, либо это крайне сложно реализовать. Наша служба SQL Server работает под локальной учетной записью, потому что, согласно Mircosoft, это единственный способ получить связанный сервер iSeries, работающий из 64-разрядного экземпляра SQL Server 2005. Когда мы изменяем службу SQL Server для запуска с учетной записью домена, команда 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 в своей целевой базе данных из 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. Я могу повторить попытку позже, но сейчас я попытаюсь пойти другим путем. Если бы у кого-то был свет, он был бы очень признателен.

3
20.08.2008 13:52:21

@ Пол, этот код выглядит так, как будто он должен работать. Вы пытались включить в этот метод какую-либо трассировку, чтобы убедиться Convert.ToString(fileName), что путь не сбивается?

0
11.08.2012 16:04:36