При интеграции решений на платформе 1С с внешними базами данных MS SQL Server посредством технологии ADO (ActiveX Data Objects) разработчики часто сталкиваются с неприятным исключением: «Произошла исключительная ситуация (ADODB.Recordset): Operation is not allowed when the object is closed». Обычно эта ошибка прерывает работу программы при попытке вызвать метод Close(), обратиться к полям выборки или обойти набор записей в цикле.
В этой статье мы подробно разберем физику взаимодействия механизмов 1С и SQL Server через COM-соединение ADODB, выясним истинные причины возникновения этой ошибки и рассмотрим по шагам надежные методы ее устранения.
Давайте проанализируем поведение объектов ADO. Когда мы выполняем запрос к базе данных MS SQL или вызываем хранимую процедуру через метод Execute() объекта ADODB.Command, драйвер пытается вернуть результат в объект ADODB.Recordset. Если выполнение запроса завершилось без возврата табличной части (или с генерацией промежуточных служебных сообщений), возвращаемый набор данных оказывается в закрытом состоянии (свойство State равно 0, что соответствует константе adStateClosed).
Попытка вызвать любой метод (например, MoveNext(), Fields() или Close()) у закрытого набора данных неизбежно приводит к аварийному завершению работы кода 1С. Рассмотрим основные причины, почему рекордсет возвращается закрытым, и способы их решения.
---
Это самая распространенная причина при вызове хранимых процедур. По умолчанию SQL Server после выполнения любой модифицирующей инструкции (такой как INSERT, UPDATE, DELETE, создание временных таблиц или даже присвоение значений переменным) отправляет клиенту служебное сообщение вида «(X строк обработано)» или DONE_IN_PROC.
Драйвер ADO воспринимает каждое такое сообщение как отдельный результирующий набор данных. Однако этот набор является пустым и закрытым. В результате первый рекордсет, который 1С получает от метода Execute(), оказывается закрытым, хотя сама процедура в финале может возвращать валидную таблицу.
Для того чтобы отключить отправку служебных сообщений клиенту, необходимо в самом начале тела хранимой процедуры SQL прописать директиву SET NOCOUNT ON;. Проанализируем правильную структуру SQL-процедуры:
ALTER PROCEDURE [dbo].[SFForSAP]
@intWhs INT,
@dtStartDate DATETIME,
@dtEndDate DATETIME
AS
BEGIN
-- Отключаем отправку счетчика обработанных строк клиенту
SET NOCOUNT ON;
-- Логика процедуры и финальная выборка данных
SELECT Nomenclature, Count, DatePlan, WarehouseID
FROM GoodsTable
WHERE WarehouseID = @intWhs
And DatePlan BETWEEN @dtStartDate AND @dtEndDate;
END
Если у вас нет прав на изменение кода хранимой процедуры на стороне MS SQL Server, мы можем обойти эту проблему непосредственно в коде 1С. Для этого мы будем использовать метод NextRecordset(), который позволяет перешагивать через пустые служебные наборы данных до тех пор, пока мы не доберемся до открытого набора с результатами выборки.
Рассмотрим, как правильно реализовать этот обход в коде 1С:
// Выполняем команду
DateTable = cmd.Execute();
// Пропускаем все закрытые наборы данных, порожденные промежуточными операциями в SQL
Пока DateTable <> Неопределено И DateTable.State = 0 Цикл
Попытка
DateTable = DateTable.NextRecordset();
Исключение
Прервать;
КонецПопытки;
КонецЦикла;
// Теперь можно безопасно обрабатывать данные
Если DateTable <> Неопределено И DateTable.State = 1 Тогда
Пока DateTable.EOF() = 0 Цикл
// Обработка полей выборки
Nomenclature = DateTable.Fields("Nomenclature").Value;
DateTable.MoveNext();
КонецЦикла;
КонецЕсли;
---
Если внутри хранимой процедуры создаются временные таблицы (имена которых начинаются с символа решетки #, например, CREATE TABLE #TempTable), то при первом обращении SQL Server может генерировать дополнительные метаданные для компиляции плана запроса. Это также заставляет ADO возвращать закрытый рекордсет при запуске.
Разберем, как обойти эту особенность:
#TempTable внутри SQL-процедуры объявляйте табличные переменные: DECLARE @TempTable TABLE (Column1 INT, Column2 VARCHAR(50)). Они не вызывают генерации лишних метаданных плана компиляции и работают стабильнее через ADO.SET FMTONLY OFF в начале процедуры может решить проблему с временными таблицами.---
Обратим внимание на частую ошибку в структуре кода 1С. Если инициализировать объект рекордсета, а затем переопределить его результатом выполнения команды cmd.Execute(), которая отработала с ошибкой на стороне SQL, управление переходит в блок Исключение. При этом переменная рекордсета либо остается равной Неопределено, либо содержит закрытый объект.
Попытка вызвать метод DateTable.Close() без проверки состояния гарантированно вызовет повторную ошибку. Давайте напишем правильный шаблон безопасного закрытия COM-объектов:
Попытка
DateTable = cmd.Execute();
// Обработка данных...
Исключение
ЗаписьЖурналаРегистрации("ADO.Подключение", УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки());
КонецПопытки;
// Безопасное закрытие рекордсета
Если DateTable <> Неопределено Тогда
Если DateTable.State = 1 Тогда // 1 соответствует adStateOpen
DateTable.Close();
КонецЕсли;
DateTable = Неопределено;
КонецЕсли;
// Безопасное закрытие соединения
Если Подключение <> Неопределено Тогда
Если Подключение.State = 1 Тогда
Подключение.Close();
КонецЕсли;
Подключение = Неопределено;
КонецЕсли;
---
По умолчанию ADO использует серверный курсор (значение свойства CursorLocation по умолчанию равно 2, что соответствует константе adUseServer). Серверный курсор крайне чувствителен к активным транзакциям и стабильности сетевого соединения. Если транзакция закрывается на стороне SQL до того, как 1С успевает прочитать данные, рекордсет самопроизвольно закроется.
Для решения этой проблемы мы рекомендуем переключать расположение курсора на сторону клиента (значение 3, константа adUseClient). В этом случае ADO предварительно выкачивает весь объем данных в оперативную память клиентского компьютера (сервера 1С), после чего рекордсет отвязывается от состояния транзакций на SQL Server.
Применим эту настройку при создании рекордсета:
DateTable = Новый COMОбъект("ADODB.Recordset");
// Устанавливаем клиентский курсор перед выполнением запроса
DateTable.CursorLocation = 3;
DateTable.Open(cmd);
---
При явном добавлении параметров через коллекцию Parameters объекта ADODB.Command, использование символа @ в именах параметров (например, @intWhs) может интерпретироваться некоторыми OLE DB драйверами некорректно.
Рекомендуется передавать имена параметров без символа @, так как сопоставление параметров провайдером данных чаще всего происходит по их порядковому индексу в коллекции, а не по имени.
Проанализируем правильное заполнение параметров:
cmd = Новый COMОбъект("ADODB.Command");
cmd.ActiveConnection = Подключение;
cmd.CommandText = "SFForSAP";
cmd.CommandType = 4; // adCmdStoredProc
// Создаем параметры без символа "@" в имени
prm1 = cmd.CreateParameter("intWhs", 3, 1,, Whs); // 3 = adInteger, 1 = adParamInput
cmd.Parameters.Append(prm1);
prm2 = cmd.CreateParameter("dtStartDate", 7, 1,, StartDate); // 7 = adDate
cmd.Parameters.Append(prm2);
prm3 = cmd.CreateParameter("dtEndDate", 7, 1,, EndDate);
cmd.Parameters.Append(prm3);
DateTable = cmd.Execute();
---
Если работа с объектом ADODB.Command и коллекцией параметров вызывает сложности или продолжает выдавать труднодиагностируемые ошибки из-за специфики OLE DB провайдера, мы всегда можем прибегнуть к проверенной альтернативе — прямому вызову через конструкцию EXECUTE в тексте запроса.
Этот способ проще в отладке, так как весь текст запроса формируется в 1С в явном виде. Рассмотрим пример реализации:
// Форматируем параметры для передачи в SQL
// Строковые параметры и даты должны быть обернуты в одинарные кавычки
СтрокаДатаНачала = Формат(StartDate, "ДФ=yyyyMMdd");
СтрокаДатаКонца = Формат(EndDate, "ДФ=yyyyMMdd");
ТекстЗапроса = "EXECUTE SFForSAP "
+ " @intWhs = " + Строка(Whs)
+ ", @dtStartDate = '" + СтрокаДатаНачала + "'"
+ ", @dtEndDate = '" + СтрокаДатаКонца + "'";
// Выполняем запрос напрямую через Recordset
DateTable = Новый COMОбъект("ADODB.Recordset");
DateTable.CursorLocation = 3; // Клиентский курсор для стабильности
DateTable.Open(ТекстЗапроса, Подключение);
Пока DateTable.EOF() = 0 Цикл
// Чтение данных...
Сообщить(DateTable.Fields("Nomenclature").Value);
DateTable.MoveNext();
КонецЦикла;
Если DateTable.State = 1 Тогда
DateTable.Close();
КонецЕсли;
Использование этого подхода позволяет обойти тонкости работы OLE DB драйверов с параметризованными командами и делает ваш код интеграции более прозрачным и легким для чтения.