Как правильно вызвать хранимую процедуру MS SQL Server из 1С через ADO

← На главную

При интеграции 1С с СУБД Microsoft SQL Server часто возникает необходимость вызова системных или пользовательских хранимых процедур (например, процедур создания трассировки sp_trace_create и sp_trace_setevent). При прямом вызове через COM-объект ADODB.Command разработчики регулярно сталкиваются с ошибками синтаксиса или несоответствием типов данных параметров.

В этой статье мы разберем причины возникновения ошибок при работе с ADO, изучим синтаксис передаваемых параметров и рассмотрим несколько практических способов вызова хранимых процедур из кода 1С.

Основные причины ошибок при вызове процедур через ADO

Проанализируем, почему при выполнении внешних запросов к MS SQL Server из 1С могут возникать исключительные ситуации:

Неверный тип команды (CommandType): Если свойство CommandType установлено в значение 4 (adCmdStoredProc — выполнение хранимой процедуры), в тексте команды CommandText нужно указывать только имя процедуры, без ключевого слова EXEC и без списка параметров в строке.
Строгая типизация параметров: Системные процедуры SQL Server строго проверяют типы передаваемых аргументов. Если процедура ожидает тип int или nvarchar, передача строки несвойственного формата или неверно инициализированного COM-параметра приведет к ошибке СУБД.
Ошибки при работе с выходными параметрами (OUTPUT): Некоторая процедура (например, sp_trace_create) возвращает значение идентификатора через выходной параметр. Если не объявить параметр с правильным направлением передачи данных, процедура не сможет отработать.
Права доступа в SQL Server: Для работы с системными процедурами трассировки у учетной записи, указанной в строке подключения, должно быть явно предоставлено право ALTER TRACE или роль sysadmin на сервере базы данных.

Передача параметров с помощью метода CreateParameter

Рассмотрим стандартный механизм ADO для передачи параметров через коллекцию Parameters объекта ADODB.Command. Метод CreateParameter имеет следующую структуру:

CreateParameter([Name], [Type], [Direction], [Size], [Value])

Разберем основные аргументы этого метода:

Name: Имя параметра процедуры (например, "@TraceID").
Type: Числовой код типа данных ADO (например, 3 — целое число adInteger, 130 — Юникод-строка adVarWChar, 135 — дата и время adDBTimeStamp).
Direction: Направление передачи параметра (1 — входной параметр adParamInput, 2 — выходной adParamOutput, 3 — входно-выходной adParamInputOutput).
Size: Размер передаваемых данных в байтах или символах.
Value: Передаваемое значение.

Пример 1: Создание сеанса трассировки через sp_trace_create

Выясним, как правильно создать сеанс трассировки MS SQL Server и получить его идентификатор (@TraceID). В этом примере параметр @TraceID является выходным (Direction = 3 или 2):


ИмяФайлаТрассировки = "C:\Traces\my_trace"; // Путь на стороне сервера SQL
ДлинаСтроки = СтрДлина(ИмяФайлаТрассировки);

Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = ADOСоединение;
Command.CommandText = "sp_trace_create";
Command.CommandType = 4; // adCmdStoredProc

// 1. Выходной параметр @TraceID (adInteger = 3, adParamInputOutput = 3, Размер = 4)
Command.Parameters.Append(Command.CreateParameter("@TraceID", 3, 3, 4));

// 2. Входной параметр @options (adInteger = 3, adParamInput = 1, Размер = 4)
Command.Parameters.Append(Command.CreateParameter("@options", 3, 1, 4));
Command.Parameters(1).Value = 2; // Опция: автоматическая ротация файлов

// 3. Входной параметр @tracefile (adVarWChar = 130, adParamInput = 1, Размер = Длина)
Command.Parameters.Append(Command.CreateParameter("@tracefile", 130, 1, ДлинаСтроки));
Command.Parameters(2).Value = ИмяФайлаТрассировки;

Command.Prepared = Истина;

Попытка
    Command.Execute();
    ИдентификаторТрассировки = Command.Parameters(0).Value;
    Сообщить("Трассировка успешно создана. TraceID: " + Строка(ИдентификаторТрассировки));
Исключение
    Сообщить("Ошибка при создании трассировки: " + ОписаниеОшибки());
КонецПопытки;

Пример 2: Добавление событий и колонок через sp_trace_setevent

Для каждого события и каждой отслеживаемой колонки процедуру sp_trace_setevent необходимо вызывать отдельно. Проанализируем пример кода для включения события RPC:Completed (ID = 10) с колонкой TextData (ID = 1):


Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = ADOСоединение;
Command.CommandText = "sp_trace_setevent";
Command.CommandType = 4;

// Передаем параметр @TraceID
Command.Parameters.Append(Command.CreateParameter("@TraceID", 3, 1, 4));
Command.Parameters(0).Value = ИдентификаторТрассировки;

// Передаем номер события @eventid (например, 10 - RPC:Completed)
Command.Parameters.Append(Command.CreateParameter("@eventid", 3, 1, 4));
Command.Parameters(1).Value = 10;

// Передаем номер колонки @columnid (например, 1 - TextData)
Command.Parameters.Append(Command.CreateParameter("@columnid", 3, 1, 4));
Command.Parameters(2).Value = 1;

// Передаем флаг включения @on (1 - включить, 0 - отключить)
Command.Parameters.Append(Command.CreateParameter("@on", 3, 1, 4));
Command.Parameters(3).Value = 1;

Command.Prepared = Истина;

Попытка
    Command.Execute();
Исключение
    Сообщить("Ошибка добавления события в трассировку: " + ОписаниеОшибки());
КонецПопытки;

Пример 3: Использование процедур-оберток в MS SQL Server

Чтобы упростить код на стороне 1С и избежать постоянного добавления десятков параметров в коллекцию Command.Parameters, целесообразно вынести логику настройки трассировки в отдельную процедуру-обертку на стороне SQL Server.

Создадим процедуру в базах данных MS SQL Server:


CREATE PROCEDURE [dbo].[sp_StartMyCustomTrace]
    @pFile NVARCHAR(245)
AS
BEGIN
    DECLARE @RC INT, @TraceID INT, @on BIT = 1;

    -- Создаем трассировку
    EXEC @RC = sp_trace_create @TraceID OUTPUT, 2, @pFile;

    IF (@RC = 0)
    BEGIN
        -- Настраиваем необходимые события и колонки
        -- Событие 10: RPC:Completed, Колонка 1: TextData
        EXEC sp_trace_setevent @TraceID, 10, 1, @on;
        
        -- Событие 12: SQL:BatchCompleted, Колонка 1: TextData
        EXEC sp_trace_setevent @TraceID, 12, 1, @on;

        -- Запускаем трассировку (status = 1)
        EXEC sp_trace_setstatus @TraceID, 1;
        
        SELECT @TraceID AS TraceID;
    END
END;

Теперь вызов такой комплексной логики из 1С сводится к передачи всего одного параметра с путем к файлу:


ПутьКФайлу = "C:\Traces\TestLog";
ДлинаСтроки = СтрДлина(ПутьКФайлу);

Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = ADOСоединение;
Command.CommandText = "sp_StartMyCustomTrace";
Command.CommandType = 4;

Command.Parameters.Append(Command.CreateParameter("@pFile", 130, 1, ДлинаСтроки));
Command.Parameters(0).Value = ПутьКФайлу;

Command.Prepared = Истина;

Попытка
    RecordSet = Command.Execute();
    Если НЕ RecordSet.EOF Тогда
        TraceID = RecordSet.Fields("TraceID").Value;
        Сообщить("Трассировка запущена под ID: " + Строка(TraceID));
    КонецЕсли;
Исключение
    Сообщить("Ошибка вызова процедуры-обертки: " + ОписаниеОшибки());
КонецПопытки;

Альтернативные варианты и рекомендации

При реализации сбора статистики запросов к СУБД обратите внимание на следующие нюансы:

Расположение каталога с файлами: Путь к файлу трассировки (например, C:\Traces\log) рассчитывается относительно сервера MS SQL Server, а не компьютера, где запущена 1С. У службы MSSQLSERVER должны быть права на запись в данный каталог.
Выполнение единого T-SQL скрипта: Если нет желания создавать хранимые процедуры-обертки в БД, весь процесс создания и запуска трассировки можно передать единым T-SQL блоком текста, установив CommandType = 1 (текстовый запрос).
Альтернатива — Технологический журнал 1С: Для анализа тяжелых и медленных запросов к СУБД штатным средством платформы 1С является Технологический журнал (настройка файла logcfg.xml с фильтром по событию DBMSSQL). Это избавляет от необходимости писать прямые запросы к MS SQL Server.