При внедрении систем бизнес-анализа (BI) в экосистему 1С разработчики часто сталкиваются с необходимостью выгрузки подготовленных срезов данных во внешние СУБД. Это позволяет разгрузить рабочую базу 1С и строить интерактивные дашборды без ущерба для производительности оперативного учета. Однако на этапе создания таблиц кэша во внешней базе MS SQL может возникнуть критическая ошибка синтаксиса. В этой статье мы подробно разберем, почему возникает сбой при объявлении индексов, как настроить уровень совместимости базы данных и как организовать безопасную архитектуру обмена данными.
В процессе выполнения прямого запроса из 1С к MS SQL через ADO или ODBC для создания временных или постоянных аналитических таблиц СУБД может вернуть следующую ошибку:
Ошибка при вызове метода контекста (Execute): Произошла исключительная ситуация (Microsoft OLE DB Provider for ODBC Drivers): [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Проанализируем ситуацию. Данный сбой происходит из-за того, что в SQL-запросе создание индексов совмещено с объявлением структуры таблицы в рамках одного оператора CREATE TABLE, например:
CREATE TABLE dbo.BI_Cache_Data (
id int NOT NULL,
Registrator nvarchar(150) NOT NULL,
Deal nvarchar(150) NOT NULL,
INDEX Registrator_IND (Registrator ASC),
PRIMARY KEY NONCLUSTERED (id ASC)
)
Такой синтаксис (встроенное объявление индексов внутри CREATE TABLE) поддерживается СУБД MS SQL Server начиная с версии 2014 (12.x) и выше. Если база данных-приемник развернута на более ранней версии СУБД (например, MS SQL Server 2008 / 10.50) или работает в режиме совместимости со старыми версиями, данный запрос завершится аварийно.
Если используемая вами версия MS SQL Server физически поддерживает современный синтаксис, но база данных-приемник была перенесена со старого сервера или создана с дефолтными настройками совместимости, необходимо повысить уровень совместимости (Compatibility Level) до значения не ниже 90 (соответствует SQL Server 2005) или оптимально до 100 и выше (SQL Server 2008+).
Рассмотрим по шагам, как изменить этот параметр через среду SQL Server Management Studio (SSMS):
Если вы предпочитаете использовать T-SQL команды, выполните следующий скрипт в окне создания запроса СУБД, предварительно указав имя вашей базы данных:
USE [master];
GO
ALTER DATABASE [Имя_Вашей_Базы_Приемника]
SET COMPATIBILITY_LEVEL = 100;
GO
Важное примечание: Изменять уровень совместимости следует именно у базы-приемника, куда BI-система записывает агрегированные данные. Основную рабочую базу 1С трогать не обязательно, что исключает риски нарушения ее работоспособности.
Если инфраструктура заказчика жестко ограничена старыми версиями СУБД (например, MS SQL Server 2008 без возможности обновления), то объединять создание таблицы и индекса в один запрос нельзя. Разберем, как правильно переписать алгоритм создания таблиц в коде 1С, сделав его универсальным и обратно совместимым.
Вместо одного комплексного запроса мы должны выполнить два последовательных обращения к СУБД с помощью объекта ADOConnection:
CREATE INDEX генерируем необходимые индексы по ключевым полям связи.Посмотрим на пример реализации такого механизма на языке 1С:
// Устанавливаем соединение с внешней базой данных SQL
Соединение = Новый COMОбъект("ADODB.Connection");
Соединение.ConnectionString = "Driver={SQL Server};Server=" + ИмяСервера + ";Database=" + ИмяБазыДанных + ";Uid=" + Логин + ";Pwd=" + Пароль + ";";
Соединение.Open();
// Шаг 1. Создание таблицы
ТекстСозданияТаблицы =
"CREATE TABLE dbo.BI_Cache_Sales (
| id INT NOT NULL,
| Registrator NVARCHAR(150) NOT NULL,
| Deal NVARCHAR(150) NOT NULL,
| Amount DECIMAL(19, 2) NOT NULL,
| CONSTRAINT PK_BI_Cache_Sales PRIMARY KEY CLUSTERED (id ASC)
|);";
Соединение.Execute(ТекстСозданияТаблицы);
// Шаг 2. Создание индексов отдельными командами
ТекстИндексаРегистратор = "CREATE NONCLUSTERED INDEX IX_BI_Cache_Sales_Registrator ON dbo.BI_Cache_Sales (Registrator ASC);";
Соединение.Execute(ТекстИндексаРегистратор);
ТекстИндексаСделка = "CREATE NONCLUSTERED INDEX IX_BI_Cache_Sales_Deal ON dbo.BI_Cache_Sales (Deal ASC);";
Соединение.Execute(ТекстИндексаСделка);
Соединение.Close();
Этот подход гарантирует стабильную работу на любых версиях MS SQL Server начиная с 2000 года и исключает синтаксические ошибки при развертывании аналитических модулей.
Обсуждая интеграцию 1С с СУБД напрямую, разработчики и системные администраторы часто выражают обоснованные опасения касательно безопасности ("закрытый код на проде", "прямой доступ к СУБД"). Недопустимо давать внешним коннекторам права администратора (sysadmin или db_owner) на боевую базу данных 1С.
Проанализируем правильную и безопасную схему архитектуры хранения аналитических данных:
db_datareader). Любые деструктивные операции (запись, изменение структуры, удаление таблиц) для этой учетной записи должны быть жестко заблокированы.В процессе развития технологий анализа данных простые BI-конструкторы, создававшиеся энтузиастами как внешние обработки, выросли в крупные интеграционные решения. Ярким примером такой эволюции является переход BI-конструктора под крыло крупного интегратора и его перерождение в современный продукт «БИТ.Бизнес-Анализ».
Современные промышленные BI-системы на базе 1С ушли далеко вперед от простой выгрузки таблиц на MS SQL:
Таким образом, решая локальные проблемы совместимости SQL-запросов и выстраивая архитектурно правильные схемы обмена данными, разработчик закладывает надежный фундамент для построения современной, масштабируемой и безопасной системы бизнес-анализа предприятия.