При переходе со старых версий платформы «1С:Предприятие 8» (например, 8.3.10) на более современные релизы (8.3.12, 8.3.14, 8.3.16 и выше) разработчики и системные администраторы часто сталкиваются со следующей критической ошибкой СУБД, приводящей к аварийному завершению работы базы данных:
Microsoft SQL Server Native Client 11.0: Недопустимый аргумент масштаба. Допустимыми выражениями для аргумента масштаба типа данных datetime2 являются целочисленные константы и выражения с целочисленными константами. HRESULT=80040E14, SQLSrvr: SQLSTATE=42000, state=1, Severity=10, native=10760
В этой статье мы подробно проанализируем причины возникновения этой ошибки, разберем физический смысл происходящего на уровне СУБД Microsoft SQL Server и предложим несколько эффективных способов решения проблемы — от точечного исправления кода запросов до системных настроек серверов баз данных.
Для начала выясним первопричину сбоя. В старых версиях платформы 1С (до релиза 8.3.12) для хранения дат использовался классический тип данных DATETIME, а функция языка запросов 1С НАЧАЛОПЕРИОДА на уровне СУБД транслировалась в конструкции с использованием системных функций DATEADD и DATEDIFF.
Начиная с версии платформы 8.3.12, технологическая платформа перешла на более точный и современный тип данных DATETIME2(0). В связи с этим изменилась и трансляция функции НАЧАЛОПЕРИОДА. Теперь при получении начала периода (например, месяца) платформа формирует SQL-запрос с использованием системной функции MS SQL Server DATETIME2FROMPARTS:
DATETIME2FROMPARTS(YEAR(Дата), MONTH(Дата), 1, 0, 0, 0, 0, 0)
Согласно официальной спецификации Microsoft, функция DATETIME2FROMPARTS принимает 8 параметров. Последний, восьмой параметр отвечает за точность (масштаб) дробной части секунд и строго обязан быть целочисленной константой (литералом) на этапе компиляции запроса. Он не может быть переменной, параметром СУБД или динамически вычисляемым выражением.
Сбой происходит из-за механизма автоматической параметризации (Parameterization) запросов в MS SQL Server. Когда СУБД выполняет простой запрос, где функция НАЧАЛОПЕРИОДА вызывается в полях выборки SELECT, оптимизатор без проблем распознает литерал 0. Но как только функция используется в условиях соединения таблиц (секциях ПО / ON конструкции JOIN) или в сложных вложенных запросах, оптимизатор SQL Server пытается построить универсальный план выполнения запроса. Для этого он принудительно заменяет литералы параметрами (например, превращает константу 0 в параметр @P1). Как только литерал масштаба превращается в параметр, MS SQL Server моментально отвергает запрос на этапе компиляции плана с ошибкой native=10760.
Рассмотрим детально пример классического проблемного запроса, на котором воспроизводится ошибка:
ВЫБРАТЬ
&Дата1 КАК Дата1
ПОМЕСТИТЬ вр1
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
&Дата2 КАК Дата2
ПОМЕСТИТЬ вр2
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
вр1.Дата1 КАК Дата1,
вр2.Дата2 КАК Дата2
ИЗ
вр1 КАК вр1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вр2 КАК вр2
ПО вр1.Дата1 = НачалоПериода(вр2.Дата2, Месяц)
При выполнении этого запроса на новой платформе и старой базе данных мы гарантированно получим аварийное завершение работы сеанса 1С.
Существует несколько методов решения данной проблемы. Выбор конкретного пути зависит от того, есть ли у вас доступ к конфигуратору для изменения запросов или задача должна быть решена исключительно силами системного администратора на уровне настроек СУБД.
Это самый быстрый и приоритетный системный метод, который не требует изменения метаданных конфигурации 1С и переписывания огромного количества старых запросов.
Очень часто в базах данных, которые мигрировали со старых серверов или обновлялись годами, в свойствах СУБД включен режим принудительной параметризации. Это заставляет SQL Server агрессивно параметризовать абсолютно все константы в запросах, провоцируя сбой компиляции DATETIME2FROMPARTS. Нам необходимо вернуть этот параметр к значению по умолчанию.
Разберем по шагам, как изменить настройку:
После изменения режима параметризации обязательно нужно очистить процедурный кэш СУБД, чтобы старые проблемные планы запросов удалились из памяти, и обновить статистику. Для этого выполните следующий SQL-скрипт:
-- Переключаем контекст выполнения на вашу базу данных
USE [Имя_Вашей_Базы_Данных];
GO
-- Очищаем кэш планов запросов для базы данных
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO
-- Обновляем статистику для всех таблиц базы данных
EXEC sp_updatestats;
GO
После выполнения этих манипуляций СУБД перестанет принудительно параметризовать константы в функциях преобразования дат, и ошибка в большинстве случаев исчезнет.
Если ошибка возникает в конкретном критическом месте конфигурации (например, при расчете себестоимости, проведении закрытия месяца или в доработанном отчете), и вам нужно гарантированно обойти её программным путем, мы можем применить небольшую хитрость.
Опытным путем установлено, что если обернуть дату внутри функции НАЧАЛОПЕРИОДА в функцию ДОБАВИТЬКДАТЕ с нулевым смещением, оптимизатор SQL Server меняет логику построения плана запроса и не пытается параметризовать итоговый аргумент масштаба. Этот прием полностью безопасен и не меняет логику расчетов.
Посмотрим на пример модифицированного условия связи:
ВЫБРАТЬ
вр1.Дата1 КАК Дата1,
вр2.Дата2 КАК Дата2
ИЗ
вр1 КАК вр1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ вр2 КАК вр2
ПО вр1.Дата1 = НачалоПериода(ДОБАВИТЬКДАТЕ(вр2.Дата2, ДЕНЬ, 0), Месяц)
Использование конструкции ДОБАВИТЬКДАТЕ(вр2.Дата2, ДЕНЬ, 0) заставит транслятор запросов 1С построить другую структуру выражений, и выполнение запроса завершится успешно без аварийного закрытия базы данных.
С точки зрения стандартов разработки «1С:Совместимо» и общих правил оптимизации производительности СУБД, любые сложные вычисления в секциях связи (после ключевого слова ПО / ON) крайне нежелательны. Выполнение функции НАЧАЛОПЕРИОДА непосредственно в момент соединения таблиц заставляет процессор СУБД вычислять значение для каждой строки «на лету», что блокирует использование индексов и приводит к полному сканированию таблиц (Index Scan или Table Scan).
Правильным архитектурным решением будет предварительный расчет начала периода и запись результатов во временную таблицу. Разберем этот подход по шагам:
НАЧАЛОПЕРИОДА в секции ВЫБРАТЬ.Проанализируем пример правильной реализации запроса:
// Шаг 1: Подготавливаем данные во временной таблице и вычисляем начало периода
ВЫБРАТЬ
вр2.Ссылка КАК Ссылка,
НАЧАЛОПЕРИОДА(вр2.Дата2, МЕСЯЦ) КАК ДатаМесяц
ПОМЕСТИТЬ врПодготовленная
ИЗ
вр2 КАК вр2
ИНДЕКСИРОВАТЬ ПО
ДатаМесяц
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Выполняем чистое соединение без тяжелых функций в секции ПО
ВЫБРАТЬ
вр1.Дата1 КАК Дата1,
врПодготовленная.Ссылка КАК Ссылка
ИЗ
вр1 КАК вр1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ врПодготовленная КАК врПодготовленная
ПО вр1.Дата1 = врПодготовленная.ДатаМесяц
Такой подход не только полностью устраняет ошибку native=10760, но и существенно ускоряет выполнение запроса на больших объемах данных за счет возможности эффективного использования индексов оптимизатором MS SQL Server.
Если вы заметили, что ошибка возникает на рабочей базе данных, но при этом на тестовой копии, развернутой на том же самом сервере из файла резервной копии .dt, всё работает стабильно, это указывает на повреждение метаданных кэша СУБД, устаревшую статистику или некорректно перенесенные системные таблицы при физическом копировании базы данных средствами SQL (например, при восстановлении из .bak).
Заливка копии базы данных через механизм .dt полностью пересоздает структуру таблиц, индексов и системных представлений средствами технологической платформы 1С «с нуля», очищая весь накопленный за годы «мусор» в процедурном кэше СУБД. При создании базы «с нуля» СУБД также автоматически выставляет дефолтный и правильный режим параметризации SIMPLE.
Для исправления ситуации выполните следующие действия:
.dt.SIMPLE)..dt.Этот метод является радикальным и для очень больших баз данных (более 100-200 Гб) может потребовать значительного времени на выгрузку и загрузку. Однако для баз данных небольшого и среднего размера это один из самых надежных способов оздоровления структуры таблиц и исправления неочевидных ошибок платформы.
Мы подробно разобрали природу ошибки масштаба DATETIME2 в 1С при работе с MS SQL Server. Чтобы избежать аварийного завершения работы базы данных при переходе на новые платформы, придерживайтесь следующих правил:
НАЧАЛОПЕРИОДА) непосредственно внутри условий связей таблиц.ДОБАВИТЬКДАТЕ(..., ДЕНЬ, 0).