Прямой доступ к данным информационных баз на MS SQL Server

Вступление

Я начал программировать в 1С, уже имея опыт разработки в MS SQL и MS Access. Поэтому чтение данных из MS SQL в 1С у меня не вызвало никаких проблем. Я знал богатые возможности t-sql, потрясающую эффективность доступа к данным у MS SQL и был расстроен немощностью языка запросов 1С.

Вот некоторые задачи из моей практики.

Крупная фирма. Мощная собственная ERP система (не 1С), данные лежат в БД на MS SQL. Казначейство – часть ERP. Лютая финансовая дисциплина. План поступлений-платежей в полном порядке. С фактом выполнения тоже все хорошо. Этот факт лежит в стандартной БП предприятия 1С8 Специфика деятельности фирмы требует проведения некоторых платежей в любое время банковского дня. Да и поступления от покупателей приходят в любое время. Запрос банковских выписок ведется непрерывно.

Руководству фирмы для оперативной корректировки кредитной политики необходим достоверный online-график достаточности денежных средств: график функции ТекущийОстаток+Поступления-Платежи по дням. То есть в любой момент руководитель открывает этот график и видит реальные данные.

В итоге один высокооплачиваемый бухгалтер тратит примерно половину рабочего времени на перенос данных о фактах движения ДС из 1С в систему ERP.

Решение: факт выполнения читаем напрямую из БД 1С, t-sql код был добавлен прямо в хранимую процедуру, возвращающую график. Работало быстро. Окно графика открывалось меньше чем за секунду.

Крупный автотранспортный холдинг. Обширный парк легковых такси. Транспортные перевозки, Автосервис – подразделение, занимающееся обслуживанием и ремонтом собственных авто. Учетом работы такси заведует самописная 1С8 Такси, учетом работы автосервиса глубоко измененная 1С8 ФИНУПП.

Руководство автосервиса хочет видеть в 1С ФИНУПП отчет, представляющий собой список в каждой строке которого:

Автомобиль; ближайшее регламентное обслуживание (наименование); оставшийся пробег до этого обслуживания.

Ровно такой же отчет хочет наблюдать диспетчер в интерфейсе 1С Такси.

Текущий пробег автомобиля можно взять из документа ПутевойЛист базы данных Такси, данные о сделанных регламентных обслуживаниях и пробегах к моменту обслуживания лежат в ФИНУПП. Логическая связка: госномер автомобиля.

Решение: Внешний отчет, хранимая процедура (ХП), осуществляющая прямое чтение данных из Такси и ФИНУПП, возвращающая строки нужной таблицы. Результат вызова ХП записывается в таблицу значений, являющуюся объектом набора данных СКД отчета.

Количество кода: 1 страница языка 1С, 1 страница t-sql. Разработанных внешний отчет без малейших изменений был добавлен в конфигурации обоих систем 1С. Формирование отчета занимает около 1 секунды.

В этой же фирме была задачи о непрерывной фиксации данных, поступающих из API Яндекс Такси, тут и выполненные заказы, и расчеты по этим заказам. Данные могут поступать с задержками и уточнениями. Требуется практически непрерывно проверять регистры сведений на предмет уточнения и коррекции или добавления вновь поступивших данных. Данные из АПИ читаются большими порциями (раз в 3 минуты производится запрос данных за несколько последних часов). Работа с объектами РегистрСведенийМенеджерЗаписи или РегистрСведенийНаборЗаписей показалась мне медленной и неэффективной. Я выбрал прямую запись в регистры сведений с использованием оператора merge языка t-sql. Потрясающая скорость! Тяжелый xml с данными за несколько последних часов корректно ложится в регистр сведений за пару секунд!

Не очень много абсолютно прозрачного кода 1С и примерно 1 страница t-sql.

Данным вступлением я хотел сказать следующее, если у вас больше одной ИБД 1С, то рано ли поздно придется создавать отчеты, включающие в себя чтение различных БД. И здесь прямое чтение баз будет весьма эффективным, как по скорости разработки, так и по скорости получение и в некоторых случаях, записи данных.

Организация доступа к данным

Разработчики 1С любезно предоставили функцию глобального контекста ПолучитьСтруктуруХраненияБазыДанных, и подробную статью Размещение данных 1С:Предприятия 8. С этой статьей настоятельно рекомендую ознакомиться всем, кто хочет читать данные напрямую. Казалось бы, всё: структура данных ясна, описание и принципы хранения данных понятны. Можно приступать. Я видел несколько разработок с прямым чтением данных, причем в весьма крупных компаниях, и я был удручен. Вот ряд моментов, которые мне категорически НЕ понравились:

Во многих случаях строки соединения с БД заданы хардкодом!

В коде кишат COMобъекты ADODB.Connection, ADODB.Recoredset.

Чтение данных осуществляется напрямую из таблиц 1С. То есть пользователь SQL –соединения имеет права чтения данных БД. Я был удивлен, что не только права чтения, но и записи! Да, ведь рано или поздно потребуется писать данные напрямую!

Код чтения данных выглядит примерно так:

Cnn=Новый COMОбъект("ADODB.Connection");
Cnn.ConnectionString=СтрокаСоединения;
Cnn.Open();
Rs=Cnn.Execute("
	|select 
	| _Code as Код,
	| _Description as Наименование,
	|from
	| dbo._Reference23
	|where
	|	_Description like N'%'"+СтрокаПоиска+"N'%'"); 

При этом переменная СтрокаПоиска содержит строку, полученную пользовательским вводом! Я не шучу, я своими глазами это видел! Здравствуй, sql injection!

Правила безопасного доступа

На MS SQL сервере:

SQL БД 1С находится в неприкосновенности, никаких пользователей и ролей, никаких объектов в этой БД не создается. Никакого SQL соединения с этой базой в качестве текущей.
На сервере MS SQL создается база данных-посредник, содержащая view для чтения данных из различных ИБД 1С. Имена полей view это имена реквизитов объектов. Таким образом, мы уходим от непонятных имен полей базовых таблиц. Запросы или запись данных осуществляются только через эти view.
Запросы данных оформляются только хранимыми процедурами, расположенными в той же БД, что и view.
Именно эта БД-посредник указывается в качестве текущей при SQL соединении. Для этого на MS SQL сервере создается имя входа, сопоставленное с учетной записью, под которой работает сервер 1С. Как правило, это [имя домена]USR1CV8. В базе-посреднике создается пользователь, сопоставленный с логином USR1CV8. Никаких прав нет у этого пользователя! Этому пользователю будут выданы права на запуск конкретных хранимых процедур.
Повторюсь еще раз: все запросы через вызов хранимых процедур. 

Со стороны 1С:

1. Доступ к sql-серверу осуществляется только со стороны сервера 1С. Подключение к MS SQL осуществляется с аутентификацией на основе учетных записей Windows. В 1С строка соединения хранится в константе или справочнике. Для места хранения учетных записей всем ролям 1С, кроме роли «ПолныеПрава» дано только право чтения.
2. Чтение данных осуществляется вызовом методов специальных общих модулей. Разработчикам, осуществляющим прямой доступ, запрещается работать непосредственно с ADODB.

Шаг за шагом на примере

Пусть у нас есть 1С ИБД с конфигурацией БП30. Я взял именно эту конфигурацию, как одну из самых распространённых. Если у кого-то нет такой конфигурации, то пользуйтесь тем, что есть и действуйте по аналогии.
Допустим, поставлена задача: создать обработку, выводящую в заданном временном диапазоне список документов СчетНаОплатуПокупателю со следующими колонками:
1. Номер
2. Дата
3. СуммаДокумента
4. Комментарий
5. Список товаров через запятую, содержащих в своем наименовании подстроку, заданную параметром. Список ограничить 10-ю позициями.
На встроенном языке запросов 1С достаточно трудно предоставить 5-ю колонку.

Пусть 1С БП30 на MS SQL лежит в базе именем wdb30. Для удобства пусть имя ИБД на сервере 1С будет тем же самым.
Пользуясь MS SQL Server Management Studio создадим на том же сервере БД c именем OneSBridge. Ваши имена баз данных могут быть другими, вносите исправления в код, приводимый ниже.
Итак, wdb30 это база ИБД 1С БП30, OneSBridge это база, из которой будем осуществлять прямой доступ к wdb30. Это будут хранимые процедуры с опцией with execute as owner

1. Обе базы должны иметь одинаковых владельцев. Это должно быть действующее имя входа с серверной ролью sysadmin. Чаще всего используется sa. В общем, не заморачивайтесь, а выполните в MS SQL SMS следующий скрипт:

ALTER AUTHORIZATION ON DATABASE::[wdb30] TO [sa]
;
ALTER AUTHORIZATION ON DATABASE::[OneSBridge] TO [sa]

2. База OneSBridge должна иметь «доверенность» на доступ к другим базам на этом сервере. Выполните в SMS следующий скрипт:

 ALTER DATABASE OneSBridge SET TRUSTWORTHY ON;

3. Создадим логин, следующий скрипт:

USE [master]
GO
 
CREATE LOGIN [ИмяВашегоWinДоменаUSR1CV8] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

А также пользователя БД OneSBridge с этим логином:

USE [OneSBridge]
GO

CREATE USER [Usr1cV8] FOR LOGIN [ИмяВашегоWinДоменаUSR1CV8] WITH DEFAULT_SCHEMA=[dbo]
GO

4. Теперь создадим view для интересующих нас объектов: Это Документ.СчетНаОплатуПокупателю, Документ.СчетНаОплатуПокупателю.Товары, Справочник.Номенклатура.
Потребуется предоставленная обработка «Структура Таблиц ИБД 1С». Запускаем 1С, открываем ИБД wdb30, запускаем обработку СтруктураТаблицИБД:

Еще добавим Справочник.Номенклатура и сгенерируем tsql скрипт:

Получившийся скрипт 3-х view запустите в MS SQL SMS в контексте базы OneSBridge.

Я не привожу здесь скрипт, потому что даже если у вас полностью совпадает с моей версия конфигурации ИБД, имена полей и даже таблиц будут скорее всего другими. Так что, придется пользоваться обработкой для генерации view.

Создадим еще полезную скалярную функцию в OneSBridge, которой часто будем пользоваться:

create function [dbo].[FromOneSDate](@dt datetime) returns datetime
as
begin
    if Year(@dt)>3900 
        Set @dt=dateadd(year,-2000,@dt)
    else
        set @dt=null
    return @dt
end

5. Вот теперь в БД OneSBridge создадим процедуру, возвращающую строки согласно нашей постановке задачи.

CREATE proc [dbo].[ViewBills](@ДатаНачала datetime,@ДатаОкончания datetime,@Подстрока nvarchar(50)) with execute as owner
as
begin
	set nocount on -- не забывайьте
	select @ДатаНачала=dateadd(year,2000,@ДатаНачала),@ДатаОкончания=dateadd(year,2000,@ДатаОкончания)	select
		convert(nvarchar(34),S.Ссылка,1) Ссылка
		,[dbo].[FromOneSDate](S.Дата) Дата
		,S.Номер
		,S.СуммаДокумента
		,S.Комментарий
		,Z.Товары
	from [dbo].[БУ_Документ_СчетНаОплатуПокупателю] S
	outer apply (
		select
			left(R.N,len(R.N)-1) Товары
		from (
			select (
				select distinct top 10 
					F.[Наименование]+N',' as 'data()'
					from [dbo].[БУ_Документ_СчетНаОплатуПокупателю_Товары] R 
					inner join [dbo].[БУ_Справочник_Номенклатура] F on F.[Ссылка]=R.НоменклатураСсылка
					where
						R.Ссылка=S.Ссылка
						and N''+F.Наименование+N'' like N'%'+@Подстрока+N'%'
					for xml path('')) N) R) Z
	where
		s.Дата>=@ДатаНачала
		and S.Дата
		and Z.Товары is not null
	order by
		S.Дата
		,S.Номер
end

Как же хорошо работать на tsql: здесь тебе и apply и for xml!

6. Отображение в 1С:

Подключаем к ИБД wdb30 расширение конфигурации вкДоступMSSQL, обновляемся, и в «функциях для технического специалиста» находим справочник «MS SQL Строки соединения». Создаем там такую строку:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=OneSBridge;Data Source=ПолноеИмяВашегоСервераMSSQL

Наименование строки «Основная», по умолчанию будет браться строка с таким наименованием. 
Вот теперь запускаем обработку ТестMSSQL, вводим период и… бах!

Да, пользователю БД user1CV8 нужно выдать разрешение на запуск процедуры dbo.ViewBills:
В БД OneSBridge нужно выполнить скрипт:

GRANT EXECUTE ON OBJECT::[dbo].[ViewBills] TO [Usr1cV8]

Вот теперь все работает

Реализация в 1С

Давайте теперь подробно рассмотрим, как чтение реализовано в 1С. 
В расширении конфигурации вкДоступMSSQL присутствуют два небольших модуля и справочник строк соединения. Модуль вк_Скуль (клиенты, сервер) содержит функции преобразования значений в текстовый формат, пригодный для формирования команд t-sql. Передаваемые в параметры команд значения могут быть следующих типов: 
Число,Строка,Дата,Логический,УникальныйИдентификатор,Ссылка
Я утверждаю, что данный набор типов на 99% покрывает все потребности в передаче параметров. Конечно, типы параметров хранимых процедур могут быть более разнообразными, но текстовое представление этих параметров все равно будет приводиться вышеперечисленному. 

// Возвращает значение типа Дата в формате, пригодном для передачи параметра в команде t-sql
//
// Параметры:
// Дата - Дата
//
// Возвращаемое значение: 
// Строка - значение даты в формате всегда однозначно понятном MS SQL: 'ггггММдд ЧЧ:мм:сс'
Функция ДатаSQL(Дата) Экспорт
	ТипДата=Новый ОписаниеТипов("Дата");
	Значение=ТипДата.ПривестиЗначение(Дата);
	Если Значение=Неопределено Тогда
		Возврат "NULL";
	КонецЕсли;
	Если Значение<Дата(1900,1,1) Тогда
		Возврат "NULL";
	КонецЕсли;
	Возврат "'"+Формат(Значение,"ДФ='ггггММдд ЧЧ:мм:сс'")+"'";
КонецФункции
// Возвращает значение типа Строка в формате, пригодном для передачи параметра в команде t-sql
//
// Параметры:
// Строка - Строка
//
// Возвращаемое значение: 
// Строка - значение строки в формате nvarchar с экранированием символа '
Функция СтрокаSQL(Строка) Экспорт
	Если Строка=Неопределено Тогда
		Возврат "NULL";
	КонецЕсли;
	Возврат "N'"+СтрЗаменить(Строка(Строка),"'","''")+"'";
КонецФункции

//Возвращает значение типа Число в формате, пригодном для передачи параметра в команде t-sql
//
//Параметры:
// Число - Число
//
//Возвращаемое значение: 
// Строка - значение Число в формате ЧРД=.; ЧН=0; ЧГ=0
Функция ЧислоSQL(Число) Экспорт
	Если Число=Неопределено Тогда
		Возврат "NULL";
	КонецЕсли;
	Возврат Формат(Число(Число),"ЧРД=.; ЧН=0; ЧГ=0");
КонецФункции

// Возвращает значение типа УникальныйИдентификатор или Ссылка в формате шестнадцатиричного отображения binary(16)
//
// Параметры:
// Число - Число
//
// Возвращаемое значение: 
// Строка - шестнадцатиричное отображение Ссылка в binary(16) 
Функция Двоичное16(Ссылка) Экспорт
	Если Ссылка=Неопределено Тогда
		Возврат "NULL";
	КонецЕсли;
	Если ТипЗнч(Ссылка)=Тип("УникальныйИдентификатор") Тогда
		Строка=ВРег(Строка(Ссылка));
	Иначе
		Строка=ВРег(Строка(Ссылка.УникальныйИдентификатор()));
	КонецЕсли;   
	//=СЦЕПИТЬ(ПСТР(A2;20;4);ПСТР(A2;25;12);ПСТР(A2;15;4);ПСТР(A2;10;4);ПСТР(A2;1;8))
	Возврат  "0x"
		+Сред(Строка,20,4)
		+Сред(Строка,25,12) 
		+Сред(Строка,15,4) 
		+Сред(Строка,10,4)  
		+Лев(Строка, 8);	
КонецФункции 

// Возвращает значение типа Булево формате bit 0/1
//
// Параметры:
// Число - Число
//
// Возвращаемое значение: 
// Строка - 0/1 
Функция БулевоSQL(Флаг) Экспорт
	Если Флаг=Неопределено Тогда
		Возврат "NULL";
	КонецЕсли;
	Возврат ?(Флаг,"1","0");
КонецФункции

//Возвращает значение типа Число,Строка,Дата,Логический,УникальныйИдентификатор,Ссылка формате, пригодном для передачи параметра в команде t-sql
//
//Параметры:
// Значение - Число,Строка,Дата,Логический,УникальныйИдентификатор,Ссылка
//
//Возвращаемое значение: 
// Строка - Строка формате, пригодном для передачи параметра в команде t-sql 
Функция ЗначениеSQL(Значение) Экспорт 
	Если Значение=Неопределено Тогда
		Возврат "NULL";
	КонецЕсли;
	Тип=ТипЗнч(Значение);
	Если Тип=Тип("Число") Тогда 
		Возврат ЧислоSQL(Значение);
	ИначеЕсли Тип=Тип("Строка") Тогда 
		Возврат СтрокаSQL(Значение); 
	ИначеЕсли Тип=Тип("Дата") Тогда 
		Возврат ДатаSQL(Значение); 
	ИначеЕсли Тип=Тип("Булево") Тогда 
		Возврат БулевоSQL(Значение);
	Иначе
		Возврат Двоичное16(Значение);
	КонецЕсли;
КонецФункции 

// Возвращает текст команды SQL с зпаданными параметрами
//
// Параметры:
// Команда - строка - команда T-SQL 
// ПараметрыКоманды - Массив,
//   Стркутура - значения полей должны иметь типы Число,Строка,Дата,Логический,УникальныйИдентификатор,Ссылка
//
// Возвращаемое значение:
// Строка - текст команды со значением параметров, если тип ПараметрыКоманды является массивом, 
// тогда значения параметров перечисляются через запятую, иначе @ИмяПоля=ЗначениеПоля
Функция ПодготовитьКоманду(Команда,ПараметрыКоманды=Неопределено) Экспорт
	КомандаБД=Команда;        
	Если ПараметрыКоманды=Неопределено Тогда
		Возврат КомандаБД;
	КонецЕсли;
	Запятая="";
	Если ТипЗнч(ПараметрыКоманды)=Тип("Массив") Тогда   
		Для Каждого Элемент Из ПараметрыКоманды Цикл 
			КомандаБД=КомандаБД+Запятая+вк_Скуль.ЗначениеSQL(Элемент);
			Запятая=",";
		КонецЦикла;
	Иначе
		Для Каждого Элемент Из ПараметрыКоманды Цикл 
			КомандаБД=КомандаБД+Запятая+"@"+Элемент.Ключ+"="+вк_Скуль.ЗначениеSQL(Элемент.Значение);
			Запятая=",";
		КонецЦикла;
	КонецЕсли;
	Возврат КомандаБД;	
КонецФункции

// Возвращает УникальныйИдентификатор из шестнадцатиричного представления binary(16) 
//
// Параметры:
// Двоичное - Строка - 34-символьное шестнадцатиричное представления binary(16) convert(nvarchar(34),ЗначениеBinary16,1)
//
// Возвращаемое значение: 
// УникальныйИдентификатор
Функция УИДИзДвоичное16(Двоичное) Экспорт
	//39bb0086-68e9-11ea-b26f-001e674e84ce 
	Строка=
		Сред(Двоичное,27,8)
		+"-"
		+Сред(Двоичное,23,4)
		+"-"
		+Сред(Двоичное,19,4)
		+"-"
		+Сред(Двоичное,3,4)
		+"-"
		+Сред(Двоичное,7,12);
	Возврат Новый УникальныйИдентификатор(Строка);
КонецФункции

Процедура СкопироватьВТаблицу(Таблица,Строки,Очищать=Ложь,Свойства=Неопределено,Исключения=Неопределено) Экспорт
	Если Очищать Тогда
		Таблица.Очистить();
	КонецЕсли;
	Для Каждого Строка Из Строки Цикл
		ЗаполнитьЗначенияСвойств(Таблица.Добавить(),Строка,Свойства,Исключения);	
	КонецЦикла;
КонецПроцедуры

Пожалуй, стоило бы добавить еще тип ДвоичныеДанные, есть встроенная функция ПолучитьHexСтрокуИзДвоичныхДанных, прикрепить в начало ‘0x’ и готово. Но функция эта работает начиная с 8.3.10. Не реализовано из соображений совместимости. Если есть нужда, доработайте код самостоятельно, дел на 5 минут.

Модуль вк_СкульСервер содержит функции вызова хранимых процедур

// Возвращает значение Строку соедниения из справочника в формате, пригодном для передачи параметра в команде t-sql
//
// Параметры:
// ИмяСтрокиСоединения - Строка - Наименование элемента справочника строк соединения
//
// Возвращаемое значение: 
// Строка - Строка соединения
Функция СтрокаСоединенияПоИмени(ИмяСтрокиСоединения) Экспорт  
	Ссылка=Справочники.вк_СтрокиСоединения.НайтиПоНаименованию(ИмяСтрокиСоединения,Истина);
	Если Ссылка.Пустая() Тогда
		ВызватьИсключение "Строка соединения с именем "+ИмяСтрокиСоединения=" не найдена";
	КонецЕсли;
	Возврат Ссылка.СтрокаСоединения;
КонецФункции
                  
// Возвращает значение соединение c SQL сервером 
//
// Параметры:
// СтрокаСоединения - Строка - строка соединения с SQL сервером, если не задана, то соединение устанавливается со строкой соединения, 
// заданной в справочнике строк соединения с именем "Основная"
//  ТаймАут - число - количество секунд параметра CommandTimeout объекта ADODB.Connection
//
// Возвращаемое значение: 
// COMОбъект - ADODB.Connection
Функция ОткрытьСоединение(СтрокаСоединения=Неопределено,ТаймАут=30)  
	Если Не ЗначениеЗаполнено(СтрокаСоединения) Тогда
		СтрокаСоединения=СтрокаСоединенияПоИмени("Основная");
	КонецЕсли;
	Cnn=Новый COMОбъект("ADODB.Connection");
	Cnn.ConnectionString=СтрокаСоединения;
	Cnn.CommandTimeout=ТаймАут;
	Cnn.Open();
	Возврат Cnn;
КонецФункции   

// Возвращает первую строку первого выходного набора хранимой процедуры 
//
// Параметры:
// ИмяХранимойПроцедцуры - Строка - имя хранимой процедуры
// ПараметрыЗапроса - Массив - массив значений параметров
//   Структура - значения параметров, имена полей структуры соответствуют именам параметров хранимой процедуры без символа "@",
// значения полей структуры являются значениями параметров
// СтрокаСоединения - Строка - Строка - строка соединения с SQL сервером, если не задана, то соединение устанавливается со строкой соединения, 
// заданной в справочнике строк соединения с именем "Основная"
//  ТаймАут - число - количество секунд параметра CommandTimeout объекта ADODB.Connection
//
// Возвращаемое значение: 
// Структура - структура с полями, имена полей соответствуют именам полей выходного набора хранимой процедуры, значения полей соответствуют значениям полей выходного набора
// Если выходной набор пустой, возвращается Неопределено
Функция ПолучитьСтрокуИзБД(ИмяХранимойПроцедцуры,ПараметрыЗапроса=Неопределено,СтрокаСоединения=Неопределено,ТаймАут=30) Экспорт 
	ЗапросКБД=вк_Скуль.ПодготовитьКоманду(ИмяХранимойПроцедцуры,ПараметрыЗапроса);
	Cnn=ОткрытьСоединение(СтрокаСоединения,ТаймАут);	
	Rs=Cnn.Execute(ЗапросКБД);
	Строка=Неопределено;
	Если Rs.State()=0 Тогда
		Возврат Строка;
	КонецЕсли;
	Если Не Rs.EOF() Тогда
		Строка=Новый Структура();
		Для Каждого Field из Rs.Fields Цикл
			Строка.Вставить(Field.Name);
			Строка[Field.Name]=Field.Value;	
		КонецЦикла;
	КонецЕсли;
	Rs.Close();
	Возврат Строка;	
КонецФункции

// Выполнет хранимую процедуру 
//
// Параметры:
// ИмяХранимойПроцедцуры - Строка - имя хранимой процедуры
// ПараметрыЗапроса - Массив - массив значений параметров
//   Структура - значения параметров, имена полей структуры соответствуют именам параметров хранимой процедуры без символа "@",
// значения полей структуры являются значениями параметров
// СтрокаСоединения - Строка - Строка - строка соединения с SQL сервером, если не задана, то соединение устанавливается со строкой соединения, 
// заданной в справочнике строк соединения с именем "Основная"
//  ТаймАут - число - количество секунд параметра CommandTimeout объекта ADODB.Connection
Процедура ВыполнитьЗапросБД(ИмяХранимойПроцедуры,ПараметрыЗапроса=Неопределено,СтрокаСоединения=Неопределено,ТаймАут=30) Экспорт
	ЗапросКБД=вк_Скуль.ПодготовитьКоманду(ИмяХранимойПроцедуры,ПараметрыЗапроса);
	Cnn=ОткрытьСоединение(СтрокаСоединения,ТаймАут);	
	Cnn.Execute(ЗапросКБД);
КонецПроцедуры

// Возвращает строки первого выходного набора хранимой процедуры 
//
// Параметры:
// ИмяХранимойПроцедцуры - Строка - имя хранимой процедуры
// ПараметрыЗапроса - Массив - массив значений параметров
//   Структура - значения параметров, имена полей структуры соответствуют именам параметров хранимой процедуры без символа "@",
// значения полей структуры являются значениями параметров
// СтрокаСоединения - Строка - Строка - строка соединения с SQL сервером, если не задана, то соединение устанавливается со строкой соединения, 
// заданной в справочнике строк соединения с именем "Основная"
//  ТаймАут - число - количество секунд параметра CommandTimeout объекта ADODB.Connection
//
// Возвращаемое значение: 
// Массив - элемент массива структура с полями, имена полей соответствуют именам полей выходного набора хранимой процедуры, значения полей соответствуют значениям полей выходного набора
Функция ПолучитьСтрокиИзБД(ИмяХранимойПроцедуры,ПараметрыЗапроса=Неопределено,СтрокаСоединения=Неопределено,ТаймАут=30) Экспорт
	Строки=Новый Массив();
	ЗапросКБД=вк_Скуль.ПодготовитьКоманду(ИмяХранимойПроцедуры,ПараметрыЗапроса);
	Cnn=ОткрытьСоединение(СтрокаСоединения,ТаймАут);	
	Rs=Cnn.Execute(ЗапросКБД);
	Если Rs.State()=0 Тогда
		Возврат Строки;
	КонецЕсли;
	Пока Не Rs.EOF() Цикл
		Строка=Новый Структура();
		Для Каждого Field из Rs.Fields Цикл
			Строка.Вставить(Field.Name);
			Строка[Field.Name]=Field.Value;	
		КонецЦикла;
		Строки.Добавить(Строка);
		Rs.MoveNext();
	КонецЦикла;
	Rs.Close();
	Возврат Строки;	
КонецФункции

// Возвращает соответствие, содержащее первый выходной набор  хранимой процедуры, ключ соответствие задается значением указанного поля выходного набора 
//
// Параметры:     
//  ИмяКлюча - Строка, имя поля выходного набора, содержащее значение ключа
// ИмяХранимойПроцедцуры - Строка - имя хранимой процедуры
// ПараметрыЗапроса - Массив - массив значений параметров
//   Структура - значения параметров, имена полей структуры соответствуют именам параметров хранимой процедуры без символа "@",
// значения полей структуры являются значениями параметров
// СтрокаСоединения - Строка - Строка - строка соединения с SQL сервером, если не задана, то соединение устанавливается со строкой соединения, 
// заданной в справочнике строк соединения с именем "Основная"
//  ТаймАут - число - количество секунд параметра CommandTimeout объекта ADODB.Connection
//
// Возвращаемое значение: 
// Соответствие - ключ элемента соответствия - значение указанного поля выходного набора, значение элемента соответствия -структрура с полями, имена полей соответствуют именам полей выходного набора хранимой процедуры, значения полей соответствуют значениям полей выходного набора
Функция ПолучитьСтрокиИзБДВСоответствие(ИмяКлюча,ИмяХранимойПроцедуры,ПараметрыЗапроса=Неопределено,СтрокаСоединения=Неопределено,ТаймАут=30) Экспорт
	Строки=Новый Соответствие();
	ЗапросКБД=вк_Скуль.ПодготовитьКоманду(ИмяХранимойПроцедуры,ПараметрыЗапроса);
	Cnn=ОткрытьСоединение(СтрокаСоединения,ТаймАут);	
	Rs=Cnn.Execute(ЗапросКБД);
	Если Rs.State()=0 Тогда
		Возврат Строки;
	КонецЕсли;
	Пока Не Rs.EOF() Цикл
	Строка=Новый Структура();
		Для Каждого Field из Rs.Fields Цикл
			Строка.Вставить(Field.Name);
			Строка[Field.Name]=Field.Value;	
		КонецЦикла;
		Строки.Вставить(Строка[ИмяКлюча],Строка);
		Rs.MoveNext();
	КонецЦикла;
	Rs.Close();
	Возврат Строки;	
КонецФункции 

// Возвращает таблицу  заныений строк первого выходного набора хранимой процедуры 
//
// Параметры:
// ИмяХранимойПроцедцуры - Строка - имя хранимой процедуры
// ПараметрыЗапроса - Массив - массив значений параметров
//   Структура - значения параметров, имена полей структуры соответствуют именам параметров хранимой процедуры без символа "@",
// значения полей структуры являются значениями параметров
// СтрокаСоединения - Строка - Строка - строка соединения с SQL сервером, если не задана, то соединение устанавливается со строкой соединения, 
// заданной в справочнике строк соединения с именем "Основная"
//  ТаймАут - число - количество секунд параметра CommandTimeout объекта ADODB.Connection
//
// Возвращаемое значение: 
// ТаблицаЗначений - строка таблицы имеет колонки, имена которых соответствуют именам полей выходного набора хранимой процедуры, значения колонок соответствуют значениям полей выходного набора
Функция ПолучитьТаблицуИзБД(ИмяХранимойПроцедуры,ПараметрыЗапроса=Неопределено,СтрокаСоединения=Неопределено,ТаймАут=30) Экспорт     
	ЗапросКБД=вк_Скуль.ПодготовитьКоманду(ИмяХранимойПроцедуры,ПараметрыЗапроса);
	Cnn=ОткрытьСоединение(СтрокаСоединения,ТаймАут);	
	Rs=Cnn.Execute(ЗапросКБД);
	Строки=Новый ТаблицаЗначений();
	Для Каждого Field из Rs.Fields Цикл
		Строки.Колонки.Добавить(Field.Name);
	КонецЦикла;
	Пока Не Rs.EOF() Цикл
		Строка=Строки.Добавить();
		Для Каждого Field из Rs.Fields Цикл
			Строка[Field.Name]=Field.Value;	
		КонецЦикла;
		Rs.MoveNext();
	КонецЦикла;
	Rs.Close();
	Возврат Строки;	
КонецФункции  

// Возвращает массив выходных наборов хранимой процедуры 
//
// Параметры:
// ИмяХранимойПроцедцуры - Строка - имя хранимой процедуры
// ПараметрыЗапроса - Массив - массив значений параметров
//   Структура - значения параметров, имена полей структуры соответствуют именам параметров хранимой процедуры без символа "@",
// значения полей структуры являются значениями параметров
// СтрокаСоединения - Строка - Строка - строка соединения с SQL сервером, если не задана, то соединение устанавливается со строкой соединения, 
// заданной в справочнике строк соединения с именем "Основная"
//  ТаймАут - число - количество секунд параметра CommandTimeout объекта ADODB.Connection
//
// Возвращаемое значение: 
// Массив - элемент массива - массив выходного набора,содержащий структуры с полями, имена полей соответствуют именам полей выходного набора хранимой процедуры, значения полей соответствуют значениям полей выходного набора
Функция ПолучитьПакетСтрокИзБД(ИмяХранимойПроцедуры,ПараметрыЗапроса=Неопределено,СтрокаСоединения=Неопределено,ТаймАут=30) Экспорт
	ЗапросКБД=вк_Скуль.ПодготовитьКоманду(ИмяХранимойПроцедуры,ПараметрыЗапроса);
	Cnn=ОткрытьСоединение(СтрокаСоединения,ТаймАут);	
	Rs=Cnn.Execute(ЗапросКБД);
	МассивСтрок=Новый Массив();
	Флаг=Истина;
	Пока Флаг Цикл
		Если Rs.State<>0 Тогда
			Строки=Новый Массив();
			Пока Не Rs.EOF() Цикл
				Строка=Новый Структура();
				Для Каждого Field из Rs.Fields Цикл
					Строка.Вставить(Field.Name,Field.Value);
				КонецЦикла;
				Строки.Добавить(Строка);
				Rs.MoveNext();
			КонецЦикла;
			МассивСтрок.Добавить(Строки);
		КонецЕсли;
		Rs=Rs.NextRecordset();
		Если Rs=Неопределено Тогда
			Флаг=Ложь;
		КонецЕсли;
	КонецЦикла;
	Возврат МассивСтрок;		
КонецФункции

// Возвращает массив таблиц, содержащих выходные наборы хранимой процедуры 
//
// Параметры:
// ИмяХранимойПроцедцуры - Строка - имя хранимой процедуры
// ПараметрыЗапроса - Массив - массив значений параметров
//   Структура - значения параметров, имена полей структуры соответствуют именам параметров хранимой процедуры без символа "@",
// значения полей структуры являются значениями параметров
// СтрокаСоединения - Строка - Строка - строка соединения с SQL сервером, если не задана, то соединение устанавливается со строкой соединения, 
// заданной в справочнике строк соединения с именем "Основная"
//  ТаймАут - число - количество секунд параметра CommandTimeout объекта ADODB.Connection
//
// Возвращаемое значение: 
// Массив - элемент массива - ТаблицаЗначений, строки которой имеют имена колонок соответствующие именам полей выходного набора, значения колонок соответствуют значениям полей выходного набора
Функция ПолучитьПакетТаблицСтрокИзБД(ИмяХранимойПроцедуры,ПараметрыЗапроса=Неопределено,СтрокаСоединения=Неопределено,ТаймАут=30) Экспорт
	ЗапросКБД=вк_Скуль.ПодготовитьКоманду(ИмяХранимойПроцедуры,ПараметрыЗапроса);
	Cnn=ОткрытьСоединение(СтрокаСоединения,ТаймАут);	
	Rs=Cnn.Execute(ЗапросКБД);
	МассивСтрок=Новый Массив();
	Флаг=Истина;
	Пока Флаг Цикл
		Если Rs.State<>0 Тогда
			Строки=Новый ТаблицаЗначений();
			Для Каждого Field из Rs.Fields Цикл
				Строки.Колонки.Добавить(Field.Name);
			КонецЦикла;
			Пока Не Rs.EOF() Цикл
				Строка=Строки.Добавить();
				Для Каждого Field из Rs.Fields Цикл
					Строка[Field.Name]=Field.Value;	
				КонецЦикла;
				Rs.MoveNext();
			КонецЦикла;
			МассивСтрок.Добавить(Строки);
		КонецЕсли;
		Rs=Rs.NextRecordset();
		Если Rs=Неопределено Тогда
			Флаг=Ложь;
		КонецЕсли;
	КонецЦикла;
	Возврат МассивСтрок;		
КонецФункции

// Возвращает УникальныйИдентификатор, ДокументСсылка или СправочникСсылка из шестнадцатиричного представления binary(16) 
//
// Параметры:
// Двоичное - Строка - 34-символьное шестнадцатиричное представления binary(16) convert(nvarchar(34),ЗначениеBinary16,1)
// ИмяОбъекта - Строка - имя Документа или Справочника, если не задано, результатом выполнения будет УникальныйИдентификатор
// ЭтоДокумент - Булево - Если Истина, то ДокументСсылка, иначе СправочникСсылка, значение по умолчанию Истина
//
// Возвращаемое значение: 
// ДокументСсылка,
// СправочникСсылка,
// УникальныйИдентификатор
Функция СсылкаИзДвоичное16(Двоичное,ИмяОбъекта=Неопределено,ЭтоДокумент=Истина) Экспорт
	//39bb0086-68e9-11ea-b26f-001e674e84ce 
	Строка=
		Сред(Двоичное,27,8)
		+"-"
		+Сред(Двоичное,23,4)
		+"-"
		+Сред(Двоичное,19,4)
		+"-"
		+Сред(Двоичное,3,4)
		+"-"
		+Сред(Двоичное,7,12);
	ГУИД=Новый УникальныйИдентификатор(Строка);
	Если Не ЗначениеЗаполнено(Имяобъекта) Тогда
		Возврат ГУИД;
	КонецЕсли;
	Если ЭтоДокумент Тогда
		Менеджер=Документы[ИмяОбъекта];
	Иначе
		Менеджер=Справочники[ИмяОбъекта];
	КонецЕсли;
	Если Не ЗначениеЗаполнено(ГУИД) Тогда
		Возврат Менеджер.ПустаяСсылка();
	Иначе
	 	Возврат Менеджер.ПолучитьСсылку(ГУИД);
	КонецЕсли;
КонецФункции

Для примера рассмотрим код 1С из процедуры ОбновитьДанные модуля формы обработки ТестMSSQL.epf.

ПараметрыЗапроса=Новый Структура("ДатаНачала,ДатаОкончания,Подстрока",Период.ДатаНачала,Период.ДатаОкончания,Подстрока);  
Строки=вк_СкульСервер.ПолучитьСтрокиИзБД("dbo.ViewBills",ПараметрыЗапроса);

Процедура вк_СкульСервер.ПолучитьСтрокиИзБД("dbo.ViewBills",ПараметрыЗапроса) cо значениями реквизитов формы Период и Подстрока из скриншота выше выполнит на MS SQL буквально следующую команду:

dbo.ViewBills @ДатаНачала ='20240901 00:00:00',@ДатаОкончания='20240930 23:59:59',@Подстрока=N'Пал'

и вернет массив структур, содержащий выходной набор вызванной хранимой процедуры. То есть, передаете структуру с именами полей, соответствующих параметрам хранимой процедуры и получаете массив строк с данными, которые вернула процедура.

Особенности разработки хранимых процедур и преобразования типов

Для безошибочной работы процедур и функций модуля вк_СкульСервер необходимо передерживаться следующих правил:

Оформление оператора select, возвращающего данные 

1. Имена полей должны соответствовать правилам именования полей в типе данных Структура. 

2. Преобразования типов полей.

При чтении данных и переносе в переменные 1С из ADODB.Field.Value происходит автоматическое преобразование типов:

Видно, что datetime2 и binary вызывают проблемы

Чтобы datetime2 перевелась корректно в тип Дата используйте приведенную выше скалярную функцию

[dbo].[FromOneSDate] 

Функция уберет 2000 лет и переведёт в корректно понимаемый как Дата тип datetime, Ну, или напрямую: convert(datetime,Дата)

3. Преобразования полей типа binary. Значения этих полей приходят в ADODB.Field.Value значениями типа COMSafeArray. Дальнейшая обработка в 1С затруднена, кроме этого данный тип немутабелен, то есть, его значения не могут быть переданы клиенту с сервера 1С. Чаще всего встречаются binary(1), так хранятся значения Булево, и binary(16), где хранятся ссылки. Если хранится составной тип данных, то рядом будет идентификатор объекта метаданных типа binary(4).
binary(1) преобразуется bit, который воспринимается как Булево:

convert(bit,ПометкаУдаления)

Ссылки binary(16) преобразуются в hex-строку: 

convert(nvarchar(34), Ссылка,1)

Идентификаторы объектов метаданных binary(4)

convert(nvarchar (6),_СсылкаТип,1)

Вам могут потребоваться ДвоичныеДанные, могу посоветовать, например, сбросить ComSafeArray в файл и прочитать оттуда в ДвоичныеДанные:
 

Stream = Новый COMОбъект("ADODB.Stream");
Stream.Type = 1;
Stream.Open();
Stream.Write(НашComSafeArray);
Stream.SaveToFile(ИмяФайла,2);
Stream.Close();
ДвоичныеДанные=Новый ДвоичныеДанные(ИмяФайла);

Преобразование шестнадцатиричных строк в значения ссылочного типа:

В модуле вк_СкульСервер имеется функция:

// Возвращает УникальныйИдентификатор, ДокументСсылка или СправочникСсылка из шестнадцатиричного представления binary(16) 
//
// Параметры:
//    Двоичное    - Строка - 34-символьное шестнадцатиричное представления binary(16) convert(nvarchar(34),ЗначениеBinary16,1)
//    ИмяОбъекта    - Строка - имя Документа или Справочника, если не задано, результатом выполнения будет УникальныйИдентификатор
//    ЭтоДокумент - Булево - Если Истина, то ДокументСсылка, иначе СправочникСсылка, значение по умолчанию Истина
//
// Возвращаемое значение: 
//    ДокументСсылка,
//     СправочникСсылка,
//     УникальныйИдентификатор
Функция СсылкаИзДвоичное16(Двоичное,ИмяОбъекта=Неопределено,ЭтоДокумент=Истина) Экспорт

Вот пример ее использования в форме обработки ТестMSSQL:

&НаКлиенте
Процедура ИзменитьСтроку(Команда=Неопределено)    
    Если Элементы.Список.ТекущиеДанные=Неопределено Тогда
        Возврат;
    КонецЕсли;
    Ссылка=вк_СкульСервер.СсылкаИзДвоичное16(Элементы.Список.ТекущиеДанные.Ссылка,"СчетНаОплатуПокупателю");
    ПараметрыОткрытия=Новый Структура("Ключ",Ссылка);
    ОткрытьФорму("Документ.СчетНаОплатуПокупателю.Форма.ФормаДокумента",ПараметрыОткрытия,Элементы.Список,Ссылка,,,Новый ОписаниеОповещения("РесинхронизацияСтроки",ЭтаФорма,Ссылка));
КонецПроцедуры

Если вам нужен УникальныйИдентификатор, то на клиенте пользуйтесь функцией из модуля вк_Скуль, чтобы не дёргать сервер:

// Возвращает УникальныйИдентификатор из шестнадцатиричного представления binary(16) 
//
// Параметры:
//    Двоичное    - Строка - 34-символьное шестнадцатиричное представления binary(16) convert(nvarchar(34),ЗначениеBinary16,1)
//
// Возвращаемое значение: 
//     УникальныйИдентификатор
Функция УИДИзДвоичное16(Двоичное) Экспорт

В обработке «Структура Таблиц ИБД 1С» view объектов метаданных снабжены служебными полями, например:

create view [dbo].[БУ_Документ_СчетНаОплатуПокупателю]
as 
select
    N'Документы' as _Метаданные
    ,0x000000D7 as _СсылкаТип
    ,N'СчетНаОплатуПокупателю' as _Имя
    ,N'Счет покупателю' as _Представление

Поле _СсылкаТип и Ссылка удобно использовать в запросах в соединении с составными типами данных, Вот, например, реквизит ДокументОснование объекта Документ.СчетНаОплатуПокупателю:

create view [dbo].[БУ_Документ_СчетНаОплатуПокупателю]
as 
select
…
    ,_Fld54393_RTRef as ДокументОснование_СсылкаТип
    ,_Fld54393_RRRef as ДокументОснованиеСсылка

Можно изобразить что-нибудь такое:
Запрос MS SQL:

select
    ...
    ,convert(nvarchar(34),coalesce(D1.Ссылка,D2.Ссылка,D3.Ссылка),1) ДокументОснованиеСсылка
    ,coalesce(D1._Имя,D2._Имя,D3._Имя) ДокументОснование_Имя
    ...
From [dbo].[БУ_Документ_СчетНаОплатуПокупателю] S
left join БУ_Документ1 D1 on D1.[_СсылкаТип]=S.[ДокументОснование_СсылкаТип] and D1.Ссылка=S.[ДокументОснованиеСсылка]
left join БУ_Документ2 D2 on D2.[_СсылкаТип]=S.[ДокументОснование_СсылкаТип] and D2.Ссылка=S.[ДокументОснованиеСсылка]
left join БУ_Документ3 D3 on D3.[_СсылкаТип]=S.[ДокументОснование_СсылкаТип] and D3.Ссылка=S.[ДокументОснованиеСсылка]

В строке полученных данных, в 1С:

ДокументОснованиеСсылка =Неопределено;
Если ЗначениеЗаполнено(Строка.ДокументОснованиеСсылка) Тогда
    ДокументОснованиеСсылка =вк_СкульСервер.СсылкаИзДвоичное16(Строка.ДокументОснованиеСсылка,Строка.ДокументОснование_Имя,Истина);
КонецЕсли;

TSQLViewer

В моей практике чаще всего не было выделенных серверов для разработки. Разработка на SQL Server и на 1С велась на копиях баз данных, на тех же серверах. 
При переносе tsql кода в боевую БД важно не забыть перенести именно все изменения. При этом задача может быть усложнена тем, что некоторые, как правило, самые последние изменения переносить не надо. После переноса необходимо не забыть раздать права на запуск новых хранимых процедур. 
Предлагаю вашему вниманию TSQLViewer. Изначально этот инструмент был реализован на MS Access, потом на .Net WinForms и, в конце концов, на 1С как внешняя обработка. Пользуюсь и радуюсь.
Данная обработка выводит список пользовательских объектов указанной базы данных, причем список можно фильтровать по 
дате изменения;
по подстроке, содержащейся в tsql коде;

В этом списке можно:
Создать скрипт выбранных программных объектов для последующего переноса; 
Дать/забрать права на запуск выбранных хранимых процедур для указанного пользователя;

TSQLViewer запускается на тонком клиенте. Обработка полностью автономна, можете запускать из пустой конфигурации. Работа с SQL сервером требует административных прав и осуществляется со стороны клиента. Заданные строки соединения сохраняются в настройках формы. Если у вас строка соединения не с аутентификацией на основе учетных записей Windows, помните, что в настройках будет храниться пароль.

Синхронизация списков

1С хорошо работает с динамическими списками, изменения объектов быстро и чаще всего в автоматическом режиме приводят к обновлению строк в списках. Всегда легко можно попросить об обновлении списка, вызвав процедуру ОповеститьОбИзменении. Динамический список – чертовски сложный объект. Нет желания создавать что-либо подобное. Однако, если у вас есть список, полученный из хранимой процедуры, см. обработку ТестSQL, и задана возможность редактирования объекта, связанного со строкой списка, хорошим тоном будет обновить строку после изменения. В MS Access и в .Net WinFoms существовали механизмы синхронизации строк, в 1С придется делать самостоятельно, к счастью, благодаря удобству программирования в управляемых формах, это просто. Приведу в пример рассмотренную выше обработку ТестSQL. Здесь редактирование объекта строки списка (это Документ.СчетНаОплатуПокупателю) происходит так:

&НаКлиенте
Процедура ИзменитьСтроку(Команда=Неопределено)    
    Если Элементы.Список.ТекущиеДанные=Неопределено Тогда
        Возврат;
    КонецЕсли;
    Ссылка=вк_СкульСервер.СсылкаИзДвоичное16(Элементы.Список.ТекущиеДанные.Ссылка,"СчетНаОплатуПокупателю");
    ПараметрыОткрытия=Новый Структура("Ключ",Ссылка);
    ОткрытьФорму("Документ.СчетНаОплатуПокупателю.Форма.ФормаДокумента",ПараметрыОткрытия,Элементы.Список,Ссылка,,,Новый ОписаниеОповещения("РесинхронизацияСтроки",ЭтаФорма,Ссылка));
КонецПроцедуры

По-хорошему, надо было бы выдавать глобальное оповещение из события формы документа ПослеЗаписи и ловить его в форме обработки, но нельзя менять фирменный код БП30. Так что, реализуем оповещение закрытия формы:

&НаКлиенте   
Процедура РесинхронизацияСтроки(НенужныйПараметр,Ссылка) Экспорт
    Ключ=вк_Скуль.Двоичное16(Ссылка);
    Строка=СтрокаНайденная(Ключ);
    Если Строка=Неопределено Тогда
        Возврат;
    КонецЕсли;   
    ПараметрыЗапроса=Новый Структура("Ссылка,Подстрока",Ссылка,Подстрока);    
    Попытка
        Источник=вк_СкульСервер.ПолучитьСтрокуИзБД("dbo.ViewBillsSync",ПараметрыЗапроса);  
    Исключение
        Возврат;
    КонецПопытки;
    Если Источник=Неопределено Тогда
        Возврат;
    КонецЕсли;
    ЗаполнитьЗначенияСвойств(Строка,Источник);
КонецПроцедуры

Ну и процедура синхронизации строки:

CREATE proc [dbo].[ViewBillsSync](@ссылка binary(16),@Подстрока nvarchar(50)) with execute as owner
as
begin
    set nocount on -- не забывайте

    select
        convert(nvarchar(34),S.Ссылка,1) Ссылка
        ,[dbo].[FromOneSDate](S.Дата) Дата
        ,S.Номер
        ,S.СуммаДокумента
        ,S.Комментарий
        ,Z.Товары
    from [dbo].[БУ_Документ_СчетНаОплатуПокупателю] S
    outer apply (
        select
            left(R.N,len(R.N)-1) Товары
        from (
            select (
                select distinct top 10 
                    F.[Наименование]+N',' as 'data()'
                    from [dbo].[БУ_Документ_СчетНаОплатуПокупателю_Товары] R 
                    inner join [dbo].[БУ_Справочник_Номенклатура] F on F.[Ссылка]=R.НоменклатураСсылка
                    where
                        R.Ссылка=S.Ссылка
                        and N''+F.Наименование+N'' like N'%'+@Подстрока+N'%'
                    for xml path('')) N) R) Z
    where
        S.Ссылка=@ссылка
End

Все же иногда можно сочетать динамический список и прямее чтение. Смотрите, как эффектно бывает использование события ПриПолучениииДанныхНаСервере. Возьмем ту же самую задачу про счета. Но пусть будет динамический список. Обработка ТестMSSQL2.epf.
Сначала создайте в БД OneSBridge хранимую процедуру:

create proc [dbo].[ViewBillsXML](@ХМЛ xml,@Подстрока nvarchar(50)) with execute as owner
as
begin
    set nocount on 
    select
        S.Ссылка
        ,Z.Товары
    from (
        select distinct
            r.value('@v','nvarchar(34)') Ссылка
            ,convert(binary(16),r.value('@v','nvarchar(34)'),1) Ссылка16
        from @ХМЛ.nodes('/rs/r') col(r)) S    
    outer apply (
        select
            left(R.N,len(R.N)-1) Товары
        from (
            select (
                select distinct top 10 
                    F.[Наименование]+N',' as 'data()'
                    from [dbo].[БУ_Документ_СчетНаОплатуПокупателю_Товары] R 
                    inner join [dbo].[БУ_Справочник_Номенклатура] F on F.[Ссылка]=R.НоменклатураСсылка
                    where
                        R.Ссылка=S.Ссылка16
                        and N''+F.Наименование+N'' like @Подстрока
                    for xml path('')) N) R) Z
end

Выдайте права на запуск:

USE OneSBridge
GO
GRANT EXECUTE ON OBJECT::[dbo].[ViewBillsXML] TO [Usr1cV8]

Или поставьте галочку в TSQLViewer :)
Теперь запустите обработку и наслаждайтесь результатом. 

&НаСервереБезКонтекста
Процедура СписокПриПолученииДанныхНаСервере(ИмяЭлемента, Настройки, Строки)  
    ХМЛ=Новый ЗаписьXML();
    ХМЛ.УстановитьСтроку();
    ХМЛ.ЗаписатьНачалоЭлемента("rs");
    Для Каждого Строка Из Строки Цикл
        ХМЛ.ЗаписатьНачалоЭлемента("r");   
        ХМЛ.ЗаписатьАтрибут("v",вк_Скуль.Двоичное16(Строка.Ключ));
        ХМЛ.ЗаписатьКонецЭлемента();   
    КонецЦикла;
    ХМЛ.ЗаписатьКонецЭлемента();    
    Подстрока=Настройки.ПараметрыДанных.НайтиЗначениеПараметра(Новый ПараметрКомпоновкиДанных("Подстрока")); 
    ПараметрыЗапроса=Новый Структура("ХМЛ,Подстрока",ХМЛ.Закрыть(),Подстрока.Значение);
    Данные=вк_СкульСервер.ПолучитьСтрокиИзБДВСоответствие("Ссылка","dbo.ViewBillsXML",ПараметрыЗапроса);
    Для каждого Строка Из Строки Цикл
        Строка.Значение.Данные.Товары=Данные.Получить(вк_Скуль.Двоичное16(Строка.Ключ)).Товары;
    КонецЦикла;
КонецПроцедуры

Индексы

Сначала, хочу отметить одну важную особенность. При наличии в конфигурации общих реквизитов разделения данных, необходимо в запросах указывать значение полей этих реквизитов, даже если по факту разделение данных не используется. Связано это с тем, что поле разделителя является первой частью ключевого индекса. Без указания значения этого поля в условии, запрос, например, значений реквизитов конкретного документа приводит к сканированию всей таблицы, а не к поиску в кластерном индексе. Указание значения поля разделителя реализовано в генераторе view «Структура таблиц ИБД»:

Индексы для разработчика СУБД наше всё. Куцые возможности индексирования данных 1С компенсируются невозбранным изменением существующих индексов и введением своих собственных, с помощью, например, MS SQL SMS. Помните только, что изменение структуры данных таблицы 1C в конфигураторе приводит к сносу всех внешних добавлений и изменений. Снимайте скрипты заранее или переносите из бекапа.

Заключение

В заключении хотел поделиться опытом прямой записи в ИБД 1С, но, поразмыслив, пришел к выводу, что такую тему негоже освещать по остаточному принципу. Соберусь силами и временем, напишу отдельную статью. 
Спасибо всем за внимание. Если кто-то дочитал до этого места и при этом решил: «нет, пожалуй, это не для меня. Я плохо знаю/не знаю MS SQL», то я призываю: возьмитесь изучать MS SQL. Новые знания всегда полезны, а освоив чуть-чуть MS SQL, вы уже сможете эффективно решать задачи, которые раньше казались неподъёмными. Удовлетворение от красивого решения и красивого кода многое значит для программиста…