Почему не срабатывает UPDATE через ADO при работе с MS SQL и как это исправить

← На главную

При работе с внешними базами данных через механизм ADO (ActiveX Data Objects) программисты 1С часто сталкиваются с ситуацией, когда запросы на обновление данных (UPDATE) отрабатывают выборочно. Представим ситуацию: в цикле выполняется 3000 запросов, но по факту изменяются лишь несколько сотен записей, при этом система не выдает никаких ошибок. Рассмотрим подробнее, почему это происходит и как построить надежное взаимодействие с MS SQL Server.

Анализируем причины «тихого» отказа UPDATE

Когда мы отправляем запрос через Соединение.Execute(), 1С ожидает, что команда будет выполнена успешно или вернет исключение. Однако в цепочке «1С — COM-объект ADO — Драйвер БД — SQL Server» может возникнуть сбой, который не дойдет до уровня 1С. Разберем основные причины:

Использование устаревших драйверов. В строке соединения часто указывается driver={SQL Server}. Это старый драйвер ODBC, который может некорректно работать с современными версиями SQL Server, особенно при высокой интенсивности запросов и использовании специфических типов данных.
Проблемы с неявными транзакциями. Если в настройках соединения или сервера не установлен четкий режим фиксации, драйвер может накапливать команды в буфере и не фиксировать их (отсутствует COMMIT), если сессия завершилась преждевременно или произошел внутренний сбой.
Ошибки форматирования данных. При прямой склейке строки запроса (например, "SET ID = '" + Код + "'") в текст могут попасть невидимые символы, такие как неразрывные пробелы (разделители триад в числах), которые делают условие WHERE ложным. В итоге SQL Server честно выполняет запрос, но обновляет 0 строк, что не является ошибкой.
Эскалация блокировок (Lock Escalation). При массовых одиночных обновлениях сервер может заблокировать таблицу целиком. Если следующий запрос в цикле попадает в момент смены типа блокировки, он может быть отменен по таймауту на уровне драйвера.

Шаг 1: Переход на современный OLE DB провайдер

Первым делом проанализируем строку подключения. Использование нативного OLE DB Provider работает быстрее и стабильнее, чем прослойка через ODBC. Выясним, как правильно настроить соединение.

Вместо устаревшего driver={SQL Server} рекомендуется использовать Provider=SQLNCLI11 (для SQL Server Native Client) или самый актуальный Provider=MSOLEDBSQL (Microsoft OLE DB Driver for SQL Server). Посмотрим на пример правильной инициализации соединения:


// Настройка стабильного соединения через OLE DB
Соединение = Новый COMОбъект("ADODB.Connection");
СтрокаПодключения = "Provider=SQLNCLI11;Data Source=" + ИмяСервера 
                  + ";Initial Catalog=" + ИмяБазы 
                  + ";User ID=" + Логин + ";Password=" + Пароль + ";";
                  
Соединение.ConnectionString = СтрокаПодключения;
Соединение.ConnectionTimeout = 30; // Таймаут установки соединения
Соединение.CommandTimeout = 90;    // Таймаут выполнения команды
Соединение.Open();

Шаг 2: Отказ от склейки строк в пользу параметров

Проанализируем типичную ошибку: формирование текста запроса через сложение строк. Это не только подвергает систему риску SQL-инъекций, но и создает проблемы с форматами дат, чисел и строк. Рассмотрим, как использовать объект ADODB.Command и параметры.

Использование параметров позволяет передавать данные в «сыром» виде, а драйвер сам позаботится о правильном экранировании и типах. Это гарантирует, что ТабельныйНомер с пробелом или специфическим символом будет обработан корректно.


Команда = Новый COMОбъект("ADODB.Command");
Команда.ActiveConnection = Соединение;
Команда.CommandText = "UPDATE PERSON SET ID_1C = ? WHERE TAB_NUM = ?";

// Создаем параметры (Тип, Размер, Значение)
// 200 — это adVarChar (строка)
ПарамКод = Команда.CreateParameter("ID_1C", 200, 1, 50, СокрЛП(ВыборкаКарт.Код));
Команда.Parameters.Append(ПарамКод);

ПарамТаб = Команда.CreateParameter("TAB_NUM", 200, 1, 50, СокрЛП(ТабельныйНомер));
Команда.Parameters.Append(ПарамТаб);

// Выполняем обновление
Команда.Execute(,,128); // 128 = adExecuteNoRecords (ускоряет выполнение)

Шаг 3: Управление транзакциями и SET NOCOUNT ON

Выясним причину, по которой запросы могут «пропадать». При выполнении каждого UPDATE сервер возвращает сообщение о количестве затронутых строк. При 3000 запросов эти сообщения могут переполнять сетевой буфер. Чтобы этого избежать, добавим в начало скрипта команду SET NOCOUNT ON;.

Также, если нам важна целостность данных, следует группировать запросы в транзакции. Однако выполнение 3000 отдельных транзакций — это огромная нагрузка на дисковую подсистему (из-за записи в лог транзакций). Рассмотрим вариант с группировкой по 100-500 записей.


Соединение.BeginTrans(); // Начало транзакции
Попытка
    Для Каждого Строка Из ТаблицаДанных Цикл
        // Настройка параметров и выполнение Команда.Execute()
        // ...
        
        Если Счетчик % 500 = 0 Тогда
            Соединение.CommitTrans(); // Фиксация группы
            Соединение.BeginTrans();  // Открытие новой
        КонецЕсли;
    КонецЦикла;
    Соединение.CommitTrans();
Исключение
    Соединение.RollbackTrans(); // Откат при ошибке
    Сообщить("Ошибка: " + ОписаниеОшибки());
КонецПопытки;

Шаг 4: Архитектурный подход (Bulk Update)

Разберем самый эффективный способ обновления больших массивов данных. Вместо того чтобы мучить сервер 3000 отдельными вызовами, мы можем передать данные одним пакетом. Для этого в MS SQL часто используют временные таблицы.

Создаем временную таблицу в SQL Server.
Загружаем в нее данные из 1С (можно сформировать один большой INSERT INTO ... VALUES (...), (...), (...)).
Выполняем один-единственный запрос UPDATE с соединением (JOIN) основной таблицы и временной.

Пример запроса для массового обновления:


UPDATE P
SET P.ID_1C = T.NewID
FROM PERSON P
INNER JOIN #TempUpdates T ON P.TAB_NUM = T.TabNum;

Этот метод исключает проблему «срабатывания через раз», так как обновление происходит в рамках одной атомарной операции на стороне сервера.