Как получить выборку из многострочного запроса SQL через ADODB в 1С?

При работе с внешними базами данных MS SQL Server из 1С через механизм ADODB.Recordset разработчики часто сталкиваются с ошибкой: Произошла исключительная ситуация (ADODB.Recordset): Operation is not allowed when the object is closed. Данная исключительная ситуация обычно возникает при попытке обратиться к свойству EOF() или выгрузить данные из выборки.

Рассмотрим подробно причины возникновения этой ошибки и разберем по шагам, как правильно выполнять многострочные T-SQL пакеты, содержащие создание временных таблиц, операторы INSERT, UPDATE, а также несколько последовательных инструкций SELECT.

Выясним причину ошибки «Operation is not allowed when the object is closed»

Проанализируем ситуацию: когда мы отправляем на SQL Server пакет T-SQL инструкций, например:

CREATE TABLE #temp (id INT, name VARCHAR(100));
INSERT INTO #temp EXEC MySuperProc;
SELECT * FROM #temp;
DROP TABLE #temp;

По умолчанию MS SQL Server после выполнения каждого оператора изменения данных (INSERT, UPDATE, DELETE), а также DDL-команд (CREATE TABLE, DROP TABLE) возвращает клиенту служебное сообщение о количестве обработанных строк (DONE_IN_PROC). Провайдер OLE DB / ODBC воспринимает каждое такое служебное сообщение как отдельный (но пустой или закрытый) объект Recordset.

В итоге, когда 1С пытается обратиться к объекту RS сразу после метода Open(), в нем находится не результат итогового SELECT, а первый закрытый служебный набор данных. При попытке вызвать метод RS.EOF() система генерирует ошибку о том, что объект закрыт.

Решение 1. Отключение вывода служебных сообщений с помощью SET NOCOUNT ON

Самый простой и эффективный способ исправить данную проблему для пакетов с модификацией данных — добавить в самом начале T-SQL скрипта директиву SET NOCOUNT ON;.

Эта команда отключает отправку сообщений о количестве затронутых строк для каждого оператора в пакете, и SQL Server возвращает в ADO только реальные таблицы результатов (выборки SELECT).

Посмотрим на пример правильного формирования текста запроса в 1С:

ТекстЗапроса = "
|SET NOCOUNT ON;
|
|CREATE TABLE #temp (
|    Код INT,
|    Наименование VARCHAR(150)
|);
|
|INSERT INTO #temp EXEC MySuperProc;
|
|SELECT * FROM #temp;
|
|DROP TABLE #temp;
|";

RS = Новый COMОбъект("ADODB.Recordset");
RS.Open(ТекстЗапроса, Соединение);

Пока RS.EOF() = 0 Цикл
    // Обработка строк выборки
    Код = RS.Fields("Код").Value;
    Наименование = RS.Fields("Наименование").Value;
    
    RS.MoveNext();
КонецЦикла;

RS.Close();

Решение 2. Получение нескольких выборок из одного запроса с помощью NextRecordset()

Бывают ситуации, когда один пакет запросов содержит несколько инструкций SELECT (аналог метода ВыполнитьПакет() в 1С). Разберем, как получить все наборы данных последовательно.

Для перехода к следующему набору данных у объекта ADODB.Recordset используется метод NextRecordset(). Проанализируем пример обхода нескольких выборок:

ТекстЗапроса = "
|SET NOCOUNT ON;
|SELECT * FROM Table1;
|SELECT * FROM Table2;
|SELECT 123 AS Число;
|";

RS = Новый COMОбъект("ADODB.Recordset");
RS.Open(ТекстЗапроса, Соединение);

Пока Не RS = Неопределено Цикл
    // Проверяем, открыт ли текущий Recordset (State = 1)
    Если RS.State = 1 Тогда
        Пока RS.EOF() = 0 Цикл
            // Чтение полей текущей выборки
            RS.MoveNext();
        КонецЦикла;
    КонецЕсли;
    
    // Переходим к следующей выборке в пакете
    RS = RS.NextRecordset();
КонецЦикла;

Рекомендации по повышению надежности работы с ADODB

Разберем дополнительные практические советы, которые помогут избежать проблем при работе с многострочными запросами из 1С:

1. Проверяйте состояние объекта: Перед обращением к полям или вызовом RS.EOF() убедитесь, что свойство RS.State равно 1 (что соответствует открытому состоянию adStateOpen). Если RS.State = 0, набор закрыт и его следует пропустить через RS.NextRecordset().
2. Используйте табличные переменные вместо временных таблиц: Замена временных таблиц #temp на табличные переменные DECLARE @temp TABLE (...) снижает количество генерируемых DDL-событий на стороне сервера.
3. Отключайте системные предупреждения: В начале сложных пакетов с агрегатными функциями рекомендуется добавлять команду SET ANSI_WARNINGS OFF;, чтобы служебные предупреждения о NULL-значениях не создавали пустые наборы данных.
4. Используйте клиентский курсор при необходимости: При работе со сложными пакетами можно явно установить местоположение курсора на клиентской стороне: RS.CursorLocation = 3; (adUseClient).