Как исправить ошибку ADO «Операция не допускается, если объект закрыт» при выполнении SQL-запросов из 1С?

← На главную

При интеграции 1С с внешними базами данных Microsoft SQL Server с помощью технологии ADO (объекты ADODB.Connection, ADODB.RecordSet, ADODB.Command) разработчики часто сталкиваются с ситуацией, когда добавление во внешний SQL-запрос временных таблиц, команд DDL/DML или вызова хранимых процедур приводит к аварийной остановке выполнения. В момент первого же обращения к объекту набора записей, например к свойству RecordSet.EOF, платформа 1С генерирует исключение: «Операция не допускается, если объект закрыт».

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

Почему возникает ошибка при работе с RecordSet

Проанализируем механику работы MS SQL Server и провайдера ADO. Когда в едином пакете инструкций T-SQL выполняются операции изменения данных или структуры (такие как SELECT ... INTO #TempTable, INSERT, UPDATE, DELETE или DROP TABLE), SQL Server по умолчанию генерирует и возвращает клиенту информационные сообщения о количестве затронутых строк (так называемые счетчики DONE_IN_PROC).

Объект ADODB.RecordSet воспринимает каждое такое системное сообщение как отдельный промежуточный результирующий набор. Так как команда создания временной таблицы не возвращает строк данных, первый набор данных сразу создается пустым и закрытым (со свойством RecordSet.State = 0). В результате результирующий SELECT, содержащий нужные строки, остается в очереди не прочитанным, а при попытке проверить свойство RecordSet.EOF в 1С возникает ошибка, поскольку объект фактически закрыт.

Кроме того, ошибка закрытого объекта может маскировать реальную проблему синтаксиса T-SQL или прав доступа. Если в процессе выполнения пакета возникает некритичная серверная ошибка, ADO не выбрасывает исключение сразу в метод Open(), а просто закрывает RecordSet, помещая подробное описание проблемы во внутреннюю коллекцию Connection.Errors.

Решение 1. Отключение передачи счетчиков с помощью SET NOCOUNT ON

Самое быстрое и эффективное решение — принудительно запретить SQL-серверу возвращать количество обработанных строк для каждой промежуточной команды. Для этого достаточно в самое начало передаваемого текста SQL-запроса добавить директиву SET NOCOUNT ON;. Если в запросе выполняются операции с возможными предупреждениями о значениях NULL, рекомендуется также добавить SET ANSI_WARNINGS OFF;.

Посмотрим на пример правильной структуры запроса при выгрузке данных в таблицу значений 1С:


Connection = Новый COMОбъект("ADODB.Connection");
Connection.Open(СтрокаПодключения);

ТекстЗапроса = 
"SET NOCOUNT ON;
|SET ANSI_WARNINGS OFF;
|
|IF OBJECT_ID('tempdb..#MasterTransactionIdForOutcome') IS NOT NULL 
|    DROP TABLE #MasterTransactionIdForOutcome;
|
|SELECT DISTINCT TraT.MasterTransactionId
|INTO #MasterTransactionIdForOutcome
|FROM ski2db2017.dbo.MasterTransaction AS Operations
|    LEFT JOIN ski2db2017.dbo.TransactionDetail AS Tra 
|        ON Tra.MasterTransactionId = Operations.MasterTransactionId
|        AND Tra.SuperAccountIdFrom = 2 
|        AND Tra.SuperAccountIdTo IS NULL
|    LEFT JOIN ski2db2017.dbo.TransactionDetail AS TraT 
|        ON TraT.MasterTransactionId = Tra.MasterTransactionId
|        AND TraT.SuperAccountIdFrom <> 2 
|        AND TraT.SuperAccountIdFrom IS NOT NULL
|        AND TraT.SuperAccountIdTo = 2
|WHERE TraT.MasterTransactionId IS NOT NULL;
|
|-- Основная выборка
|SELECT Operations.*
|FROM ski2db2017.dbo.MasterTransaction AS Operations
|    LEFT JOIN #MasterTransactionIdForOutcome AS TraOt 
|        ON TraOt.MasterTransactionId = Operations.MasterTransactionId;";

RecordSet = Новый COMОбъект("ADODB.RecordSet");
RecordSet.CursorLocation = 3; // adUseClient
RecordSet.Open(ТекстЗапроса, Connection);

Если RecordSet.State = 1 Тогда // adStateOpen
    Пока Не RecordSet.EOF Цикл
        НоваяСтрока = ТаблицаРезультат.Добавить();
        НоваяСтрока.Идентификатор = RecordSet.Fields.Item("MasterTransactionId").Value;
        RecordSet.MoveNext();
    КонецЦикла;
КонецЕсли;

RecordSet.Close();
Connection.Close();

Решение 2. Перебор результирующих наборов через NextRecordset

Если по какой-либо причине изменить текст SQL-запроса и включить SET NOCOUNT ON нельзя, либо процедура намеренно возвращает несколько таблиц данных, необходимо программно перебирать наборы данных в 1С с помощью метода NextRecordset().

Разберем по шагам, как пропустить пустые закрытые результаты и спозиционироваться на открытом наборе строк:


RecordSet = Новый COMОбъект("ADODB.RecordSet");
RecordSet.Open(ТекстЗапроса, Connection);

// Пропускаем служебные и закрытые наборы до первого открытого
Пока RecordSet <> Неопределено И RecordSet.State = 0 Цикл
    RecordSet = RecordSet.NextRecordset();
КонецЦикла;

Если RecordSet <> Неопределено И RecordSet.State = 1 Тогда
    Пока Не RecordSet.EOF Цикл
        НоваяСтрока = ТаблицаРезультат.Добавить();
        НоваяСтрока.Идентификатор = RecordSet.Fields.Item("MasterTransactionId").Value;
        RecordSet.MoveNext();
    КонецЦикла;
Иначе
    // Если открытый Recordset так и не получен, анализируем ошибки подключения
    Для Каждого ОшибкаSQL Из Connection.Errors Цикл
        Сообщить("SQL Ошибка: " + ОшибкаSQL.Description + " (Код: " + ОшибкаSQL.NativeError + ")");
    КонецЦикла;
КонецЕсли;

Решение 3. Разделение подготовительных команд через ADODB.Command

Другой надежный подход к архитектуре внешних запросов — разделение логики. Операции подготовки данных (проверка существования временных таблиц, их создание, первичное наполнение) выполняются как отдельные инструкции без возврата строк через ADODB.Command с флагом adExecuteNoRecords (значение 128), а результирующая выборка запрашивается отдельно.

Создаем единое подключение ADODB.Connection, сохраняющее сессию и временные таблицы текущего соединения.
Выполняем подготовительный SQL-скрипт с помощью ADODB.Command с типом команды adCmdText (значение 1) и опцией 128.
Финальный SELECT открываем через RecordSet.Open() без риска получить служебные пакеты.


Command = Новый COMОбъект("ADODB.Command");
Command.ActiveConnection = Connection;
Command.CommandTimeout = 120;
Command.CommandType = 1; // adCmdText

// Подготовка временной таблицы без возврата Recordset
Command.CommandText = 
"IF OBJECT_ID('tempdb..#MasterTransactionIdForOutcome') IS NOT NULL DROP TABLE #MasterTransactionIdForOutcome;
|SELECT DISTINCT TraT.MasterTransactionId
|INTO #MasterTransactionIdForOutcome
|FROM ski2db2017.dbo.TransactionDetail AS TraT
|WHERE TraT.SuperAccountIdTo = 2;";
Command.Execute(, , 128); // 128 = adExecuteNoRecords

// Получение результирующего набора
RecordSet = Новый COMОбъект("ADODB.RecordSet");
RecordSet.Open("SELECT * FROM #MasterTransactionIdForOutcome", Connection);

Решение 4. Замена временных таблиц на CTE или табличные переменные

Чтобы избежать обращений к базе данных tempdb и не создавать промежуточных маркеров выполнения, можно переписать SQL-запрос с использованием обобщенных табличных выражений (CTE — WITH ... AS) либо табличных переменных DECLARE @Table TABLE.

Конструкция с CTE выполняется как единый неделимый оператор SELECT, не генерирует системных ответов DONE_IN_PROC и исключает возникновение ошибки закрытого объекта:


ТекстЗапроса = 
"WITH TraOutcome AS (
|    SELECT DISTINCT TraT.MasterTransactionId
|    FROM ski2db2017.dbo.MasterTransaction AS Operations
|        LEFT JOIN ski2db2017.dbo.TransactionDetail AS Tra 
|            ON Tra.MasterTransactionId = Operations.MasterTransactionId
|            AND Tra.SuperAccountIdFrom = 2 
|            AND Tra.SuperAccountIdTo IS NULL
|        LEFT JOIN ski2db2017.dbo.TransactionDetail AS TraT 
|            ON TraT.MasterTransactionId = Tra.MasterTransactionId
|            AND TraT.SuperAccountIdFrom <> 2 
|            AND TraT.SuperAccountIdFrom IS NOT NULL
|            AND TraT.SuperAccountIdTo = 2
|    WHERE TraT.MasterTransactionId IS NOT NULL
|)
|SELECT Operations.*
|FROM ski2db2017.dbo.MasterTransaction AS Operations
|    LEFT JOIN TraOutcome AS TraOt 
|        ON TraOt.MasterTransactionId = Operations.MasterTransactionId;";

Оптимизация чтения данных в 1С: отказ от построчного обхода через COM

Построчный обход Пока Не RecordSet.EOF Цикл ... RecordSet.MoveNext() генерирует огромное количество межпроцессных COM-вызовов при обращении к Fields.Item().Value, что существенно замедляет загрузку больших объемов данных в 1С.

Для ускорения чтения данных используем встроенный метод GetRows(), который за один вызов передает весь результирующий набор в двумерный массив:


Если RecordSet.State = 1 И Не RecordSet.EOF Тогда
    // Выгружаем все данные в SafeArray (в 1С преобразуется в COMSafeArray/Массив)
    ДанныеМассива = RecordSet.GetRows();
    
    // Получаем границы массива: первое измерение - колонки, второе - строки
    КоличествоКолонок = ДанныеМассива.ВГраница(1);
    КоличествоСтрок = ДанныеМассива.ВГраница(2);
    
    Для НомерСтроки = 0 По КоличествоСтрок Цикл
        НоваяСтрока = ТаблицаРезультат.Добавить();
        НоваяСтрока.Идентификатор = ДанныеМассива.Получить(0, НомерСтроки);
    КонецЦикла;
КонецЕсли;

Применение инструкции SET NOCOUNT ON; в сочетании с проверкой свойства RecordSet.State и пакетом методов GetRows() полностью устраняет ошибку «Операция не допускается, если объект закрыт» и повышает скорость выборки внешних данных в 1С в несколько раз.