При работе с механизмом Внешние источники данных в платформе «1С:Предприятие» разработчики часто сталкиваются со специфическими ошибками СУБД. Одной из наиболее коварных является ошибка с кодом 8114 (SQLSTATE: 42000): «Ошибка при преобразовании типа данных varchar к numeric». В режиме «1С:Предприятие» при простом просмотре таблицы через стандартные функции данные могут отображаться корректно, однако при попытке построить отчет, выполнить запрос или открыть динамический список система выдает критический сбой.
В этой статье мы подробно разберем природу возникновения данной ошибки, рассмотрим пошаговые методы её диагностики на стороне SQL Server и 1С, а также изучим эффективные способы её устранения.
Эта ошибка генерируется на стороне СУБД (например, MS SQL Server) в процессе так называемого неявного приведения типов (Implicit Conversion). В SQL Server существует строго определенный приоритет типов данных. Тип numeric (или decimal) имеет значительно более высокий приоритет, чем строковые типы varchar или nvarchar.
Когда платформа 1С обращается к таблице внешнего источника данных, она транслирует запрос на понятный для СУБД язык T-SQL. Если в метаданных 1С поле внешнего источника данных описано как Число, а в самой таблице SQL Server это поле фактически определено как текстовое (varchar/nvarchar), СУБД попытается автоматически привести строковое значение к числовому типу перед выполнением операций фильтрации, сортировки или группировки.
Сбой происходит в тот момент, когда SQL Server натыкается на строку, которую невозможно преобразовать в число стандартными средствами. Проанализируем основные причины таких ситуаций:
'') или строку, состоящую только из пробелов, к типу numeric в MS SQL Server 2008 и выше всегда вызывает ошибку 8114.2 018) или знаков валют.LANGUAGE=русский. При русской локализации SQL Server ожидает в качестве разделителя дробной части запятую (например, 15,3). Если же в текстовом поле данные хранятся по стандарту US (с точкой — 15.3), автоматическое преобразование завершится ошибкой.Рассмотрим подробнее три основных способа лечения этой проблемы.
Самый надежный и архитектурно правильный способ — синхронизировать описание типов метаданных в 1С с реальной схемой данных в СУБД. Если поле в базе-источнике является строковым, оно должно быть строковым и в 1С.
Разберем процесс корректировки по шагам:
varchar, а в 1С указано Число, заменим тип в 1С на Строка.Если в дальнейшем вам потребуется производить арифметические вычисления с этим полем внутри 1С, прочитайте данные как строку, выполните предварительную очистку от лишних символов и нечисловых знаков, а затем преобразуйте к числу с помощью встроенной функции:
// Пример очистки строки и приведения к числу
ОчищеннаяСтрока = СокрЛП(Выборка.ИмяТекстовогоПоля);
ОчищеннаяСтрока = СтрЗаменить(ОчищеннаяСтрока, " ", ""); // Удаляем пробелы-разделители
ОчищеннаяСтрока = СтрЗаменить(ОчищеннаяСтрока, ",", "."); // Приводим разделитель к точке
Попытка
ЧисловоеЗначение = Число(ОчищеннаяСтрока);
Исключение
ЧисловоеЗначение = 0; // Обработка некорректных данных
КонецПопытки;
Если ошибка вызвана конфликтом разделителей дробной части из-за языковых настроек, мы можем скорректировать параметры подключения. Посмотрим на исходную строку подключения:
DRIVER={SQL Server};SERVER=10.1.1.9;UID=sa;PWD=xxxxx;APP=1c;DATABASE=db01_state;LANGUAGE=русский
Параметр LANGUAGE=русский заставляет драйвер ODBC использовать русские стандарты форматирования. Попробуем изменить этот параметр на английский или полностью удалить его из строки подключения:
DRIVER={SQL Server};SERVER=10.1.1.9;UID=sa;PWD=xxxxx;APP=1c;DATABASE=db01_state;LANGUAGE=us_english
Это изменит правила интерпретации символов точки и запятой при неявном приведении типов и во многих случаях позволит избежать ошибки без изменения кода.
Зачастую ошибка возникает из-за наличия единичных «грязных» записей в таблице-источнике (например, опечатка оператора, занесшего в числовую по смыслу колонку текстовые символы). Чтобы локализовать такие строки, запустим среду SQL Server Management Studio (SSMS) и выполним специальный проверочный запрос.
Воспользуемся встроенной функцией T-SQL ISNUMERIC. Она возвращает 1, если выражение может быть преобразовано в число, и 0 в противном случае. Напишем следующий запрос:
SELECT *
FROM ИмяТаблицыИсточника
WHERE ISNUMERIC(ИмяПроблемнойКолонки) = 0
AND ИмяПроблемнойКолонки IS NOT NULL
AND ИмяПроблемнойКолонки <> '';
Этот запрос вернет список всех строк, содержащих некорректные символы, буквы или пробелы внутри числовой строки. Проанализировав результат, мы сможем вручную исправить некорректные значения непосредственно в СУБД или написать скрипт очистки данных.
Поскольку ошибка преобразования типов возникает в рамках конкретной пользовательской сессии при выполнении динамического запроса, СУБД не записывает её в стандартный системный лог ошибок SQL Server (Error Log). Для её отслеживания нам понадобятся специализированные инструменты.
Для перехвата точного текста SQL-запроса, отправляемого платформой 1С во внешнюю базу данных, настроим сбор Технологического Журнала (ТЖ). Для этого создадим или отредактируем файл logcfg.xml в каталоге конфигурирования сервера 1С (обычно C:\Program Files\1cv8\conf\):
<config xmlns="http://v8.1c.ru/v8/tech-log">
<log location="C:\v8_logs" history="24">
<event>
<eq property="Name" value="DBMSSQL"/>
</event>
<event>
<eq property="Name" value="EXCP"/>
</event>
<property name="All"/>
</log>
</config>
Событие DBMSSQL зафиксирует все исходящие запросы к СУБД, а событие EXCP запишет возникшее исключение с подробным описанием ошибки ODBC. Это позволит точно определить, на каком именно поле спотыкается система.
Если у нас есть доступ к серверу баз данных, запустим утилиту SQL Server Profiler перед воспроизведением ошибки в 1С. В свойствах трассировки выберем следующие события:
User Error Message (класс событий Errors and Warnings) — зафиксирует системное сообщение об ошибке 8114.SQL:BatchStarting или SQL:StmtStarting (класс событий TSQL) — покажут точный текст SQL-запроса, отправленного из 1С через ODBC-драйвер.Скопировав полученный из трассировки SQL-запрос, мы сможем запустить его в среде SSMS и точечно локализовать сбойное поле.
Часто внешние источники данных физически располагаются на устаревших СУБД. Если база данных мигрировала со старой версии (например, с SQL Server 2000) на современную версию (SQL Server 2008 и выше) путем восстановления резервной копии (Backup/Restore), простого изменения уровня совместимости базы данных (Compatibility Level) в свойствах базы будет недостаточно.
Устаревшая статистика и некорректное распределение страниц могут приводить к тому, что оптимизатор запросов SQL Server строит неверные планы выполнения. В результате СУБД пытается сканировать и преобразовывать строки, которые при оптимальном плане выполнения должны были отсекаться фильтрами ещё до этапа конвертации типов.
После миграции базы данных настоятельно рекомендуется выполнить следующие регламентные операции в SSMS:
EXEC sp_updatestats;
DBCC UPDATEUSAGE(0);
Эти действия помогут оптимизатору СУБД корректно обрабатывать запросы от платформы 1С и существенно снизят вероятность возникновения непредвиденных ошибок при неявном приведении типов данных.