Запрос к базе данных MS SQL из 1С

Форум Программирование Общие вопросы программирования (Dev)
Платформа 1С v8.3
1. donyab 9 20.10.16 15:17 Сейчас в теме
Всем доброго дня
(платформа 1С:Предприятие 8.3 (8.3.8.2054))
(Microsoft SQL Server Standard Edition (64-bit), v. 10.0.5890.0)

Есть 2 SQL запроса, оба прекрасно отрабатывают query консоли MS SQL сервера

Но, 1й запрос так же выполняется при обращении через 1с к SQL серверу, а второй вылетает с ошибкой: Произошла исключительная ситуация (ADODB.Recordset): Operation is not allowed when the object is closed.

Работает везде: 
ПолучениеСоединенияСАнкетами(Connection);
	Текст = 
	"SELECT     INV.T876c, ISNULL(INV.T990t, '') AS T990t, INV.T876p, INV.SOURCE, INV.DOC_ID, DOC.ITEM, DOC.BIBLEVEL, DOC.RECTYPE, SIGLAS.SHORTNAME, SIGLAS.ID, INV.INV_ID
	|FROM         SIGLAS RIGHT OUTER JOIN
	|                      INV ON SIGLAS.ID = INV.SIGLA_ID RIGHT OUTER JOIN
	|					   DOC ON INV.DOC_ID = DOC.DOC_ID
	|WHERE     (NOT (INV.T876p IS NULL))
	|ORDER BY INV.DOC_ID";

	RecordSet=Connection.Execute(Текст);
Показать


Работает только в консоли запросов MS SQL Server Management Studio^ 

ПолучениеСоединенияСАнкетами(Connection);
Текст = 
		
	"SELECT     INV.T876c, ISNULL(INV.T990t, '') AS T990t, INV.T876p, INV.SOURCE, INV.DOC_ID,  SUBSTRING (REPLACE (convert(varchar(1000),DOC.ITEM),CHAR(031),'!!!'),60, LEN (REPLACE (convert(varchar(1000),DOC.ITEM),'  vВ наличии : инв. 2015z_',''))) as item, DOC.BIBLEVEL, DOC.RECTYPE, SIGLAS.SHORTNAME, SIGLAS.ID, INV.INV_ID
	|INTO #temp1
	|FROM         SIGLAS RIGHT OUTER JOIN
	|                      INV ON SIGLAS.ID = INV.SIGLA_ID RIGHT OUTER JOIN
	|                      DOC ON INV.DOC_ID = DOC.DOC_ID
	|WHERE      (NOT (INV.T876p IS NULL)) AND (((SIGLAS.SHORTNAME  <> 'сбо') AND (SIGLAS.SHORTNAME <> 'прокат') ) AND NOT INV.T876p like('%P%'));
	|
	|SELECT     T876p, REPLACE (ITEM, CHAR(030),'###') as ITEM, INV_ID, T990t
	|INTO #temp2
	|FROM         #temp1;
	|
	|SELECT replace (ITEM,'###100__!!!a','###100') AS ITEM, T876p, INV_ID, T990t
	|FROM #temp2
	|
	|ORDER BY ITEM
	|
	|DR OP   TABLE  #temp1
	|DR OP   TABLE  #temp2";

RecordSet=Connection.Execute(Текст);
Показать


Вот сама строка соединения: 

&НаСервереБезКонтекста
Функция ПолучениеСоединенияСАнкетами(Connection) Экспорт
	Connection = Новый COMОбъект("ADODB.Connection");
	Connection.Open ("DRIVER={SQL Server};SERVER=XXX;UID=Login;PWD=PASSWORD;DATABASE=baseName");
КонецФункции


При всем при этом "Execute" выполняет запрос, т.к. выполнение не прерывается, пока не поптаешься перебрать выборку "RecordSet.EOF"
Прикрепленные файлы:
+ – Ответить
По теме из базы знаний
Ответы
Подписаться на ответы Инфостарт бот Сортировка: Древо развёрнутое
Свернуть все
2. kuzev 48 20.10.16 15:21 Сейчас в теме
(1) donyab, в 1-м случае один запрос, во 2-м - пакет запросов.
+ – Ответить
3. donyab 9 20.10.16 15:24 Сейчас в теме
(2) kuzev,
Нет, не пакет, а запрос со временными таблицами.

Суть то, что надо поправить, что бы работало?
+ – Ответить
5. sssss_aaaaa_2011 20.10.16 15:26 Сейчас в теме
(3) donyab, таки пакет. С 3(тремя) запросами. Что делать уже написано.
+ – Ответить
6. donyab 9 20.10.16 15:37 Сейчас в теме
(5) sssss_aaaaa_2011,
А чем пакет то неугодил? Непонятно.
+ – Ответить
7. sssss_aaaaa_2011 20.10.16 15:39 Сейчас в теме
(3) donyab, у меня ваших таблиц нет, потому просто редактирование текста запроса.
SEL ECT replace (ITEM,'###100__!!!a','###100') AS ITEM, T876p, INV_ID, T990t
FR OM --#temp2
(
SEL ECT T876p, REPLACE (ITEM, CHAR(030),'###') as ITEM, INV_ID, T990t
FR OM -- #temp1;
(
SELECT INV.T876c, ISNULL(INV.T990t, '') AS T990t, INV.T876p, INV.SOURCE, INV.DOC_ID, SUBSTRING (REPLACE (convert(varchar(1000),DOC.ITEM),CHAR(031),'!!!'),60, LEN (REPLACE (convert(varchar(1000),DOC.ITEM),' vВ наличии : инв. 2015z_',''))) as item, DOC.BIBLEVEL, DOC.RECTYPE, SIGLAS.SHORTNAME, SIGLAS.ID, INV.INV_ID
FR OM SIGLAS RIGHT OUTER JOIN
INV ON SIGLAS.ID = INV.SIGLA_ID RIGHT OUTER JOIN
DOC ON INV.DOC_ID = DOC.DOC_ID
WH ERE (NOT (INV.T876p IS NULL)) AND (((SIGLAS.SHORTNAME <> 'сбо') AND (SIGLAS.SHORTNAME <> 'прокат') ) AND NOT INV.T876p like('%P%'))
) t1
) t2
ORDER BY ITEM
donyab; +1 – Ответить
8. donyab 9 20.10.16 15:52 Сейчас в теме
(7) sssss_aaaaa_2011,
Оно, спасибо.
Только где бы почитать про то, почему нельзя выполнить SQL батч из 1с?
+ – Ответить
9. sssss_aaaaa_2011 20.10.16 16:01 Сейчас в теме
(8) donyab,
почему нельзя выполнить SQL батч из 1с?
Ну что за чушь? Дело в неправильном использовании ADO, а не в 1с или ms sql.
+ – Ответить
10. donyab 9 20.10.16 16:19 Сейчас в теме
(9) sssss_aaaaa_2011,
с этого и надо было начинать)

Для будущих поколений:
Каждый пакет посылаем отдельно! 

ПолучениеСоединенияСАнкетами(Connection);
	Текст = 

	"SELECT     INV.T876c, ISNULL(INV.T990t, '') AS T990t, INV.T876p, INV.SOURCE, INV.DOC_ID,  SUBSTRING (REPLACE (convert(varchar(1000),DOC.ITEM),CHAR(031),'!!!'),60, LEN (REPLACE (convert(varchar(1000),DOC.ITEM),'  vВ наличии : инв. 2015z_',''))) as item, DOC.BIBLEVEL, DOC.RECTYPE, SIGLAS.SHORTNAME, SIGLAS.ID, INV.INV_ID
	|INTO #temp1
	|FROM         SIGLAS RIGHT OUTER JOIN
	|                      INV ON SIGLAS.ID = INV.SIGLA_ID RIGHT OUTER JOIN
	|                      DOC ON INV.DOC_ID = DOC.DOC_ID
	|WHERE      (NOT (INV.T876p IS NULL)) AND (((SIGLAS.SHORTNAME  <> 'сбо') AND (SIGLAS.SHORTNAME <> 'прокат') ) AND NOT INV.T876p like('%P%'))";
	Connection.Execute(Текст);
	Текст =
	
	"SELECT     T876p, REPLACE (ITEM, CHAR(030),'###') as ITEM, INV_ID, T990t
	|INTO #temp2
	|FROM         #temp1";
	Connection.Execute(Текст);
	Текст =
	
	"SELECT replace (ITEM,'###100__!!!a','###100') AS ITEM, T876p, INV_ID, T990t
	|FROM #temp2
	|
	|ORDER BY ITEM
	|
	|DR OP   TABLE  #temp1
	|DR OP   TABLE  #temp2";


	RecordSet=Connection.Execute(Текст);
Показать
+ – Ответить
11. sssss_aaaaa_2011 20.10.16 16:32 Сейчас в теме
(10) donyab, ну, можно и так... Хотя лучше просто убрать удаление временных таблиц. Они с убиением коннекта сами умрут. И ваш первоначальный вариант не работал именно из-за этого дропания.
+ – Ответить
12. donyab 9 20.10.16 16:34 Сейчас в теме
(11) sssss_aaaaa_2011,
Нет, проверял первым делом.
Не дропал, ошибка была таже самая.

И с убиением не умирают. Проверял через SQL, они хранятся в tempd и при 2 запуске запроса SQL ругается, что такая временная таблица уже создана.
Я полагаю, что после перезагрузки скуля удалятся.
+ – Ответить
13. sssss_aaaaa_2011 20.10.16 16:41 Сейчас в теме
(12) donyab,
Я полагаю, что после перезагрузки скуля удалятся.
Ну, после перезагрузки уж всяко удалятся ибо при этом сама tempdb пересоздастся. А вот с вашими выводами и проверками не соглашусь, что-то где-то не так вы делали.
+ – Ответить
4. sssss_aaaaa_2011 20.10.16 15:25 Сейчас в теме
(1) donyab, как уже указали, во втором случае совсем не запрос, а пакет. Но его моно превратить в запрос.
+ – Ответить
14. herfis 515 20.10.16 17:02 Сейчас в теме
1. Временные таблицы живут до закрытия соединения, если их не дропать. Они им изолированы. В этом их смысл. Самое смешное, что одинэсный объект с пафосным названием МенеджерВременныхТаблиц на самом деле тупая ссылка на соединение (чтобы разные запросы выполнялись в одном соединении с одними и теми же временными таблицами). Ну не совсем тупая. Она как бы управляет временем жизни соединения на самом деле.
2. Не пробовал, но ADODB вроде как поддерживает обработку множественных результатов пакетного запроса.
https://msdn.microsoft.com/en-us/library/ms677539(v=vs.85).aspx
+ – Ответить
15. Felix-pele 01.08.18 09:03 Сейчас в теме
Столкнулся с такой же проблемой и ошибкой, когда хотел использовать процедуры которая на MS SQL Server Management Studio отрабатывает корректно а в 1С выдавала ошибку: Operation is not allowed when the object is closed
А сама проблема была в процедуре. По умолчанию параметр SET NOCOUNT имеет статус OFF, соответственно решение надо дописать в процедуру SET NOCOUNT ON и все тогда работает.
решение нашел здесь:

https://stackoverflow.com/questions/25803116/operation-not-allowed-when-the-object-is-closed-when-running-more-advanced-query#

надеюсь кому-то поможет
iogri; ya.Avoronov; oyeah; Aleksandr_Kotelnikov_mmc; acanta; +5 – Ответить
16. lepth 26.07.19 10:10 Сейчас в теме
(15) Спасибо, добрый человек :)
+ – Ответить
17. pavlo 21.08.20 09:25 Сейчас в теме
что то не понятно как то, может кто подскажет, та же беда :( не ясно что закрывает объект.
В студии все работает на ура 

Соединение = Новый COMОбъект("ADODB.Connection");
	Соединение.ConnectionString = СтрокаСоединения;
	Соединение.Open();
	
	Команда = Новый COMОбъект("ADODB.Command");
	Команда.ActiveConnection = Соединение;
	Команда.CommandText = ТекстЗапроса;

	Выборка = Новый COMОбъект("ADODB.RecordSet");
	Выборка = Команда.Execute();

	Если Выборка.EOF() Тогда //тут падает Operation is not allowed when the object is closed.
Показать


Сам запрос вот такой: 

	ТекстЗапроса = "
	|SET NOCOUNT ON
	|-- Список проблемных заданий
	|USE MSDB
	|;WITH CTE_MostRecentJobRun AS
	|(
	|-- For each job get the most recent run (this will be the one where Rnk=1)
	|SELECT job_id,run_status,run_date,run_time
	|,RANK() OVER (PARTITION BY job_id ORDER BY run_date DESC,run_time DESC) AS Rnk
	|FROM sysjobhistory
	|WHERE step_id=0
	|)
	|SELECT
	|name  AS [JobName],
	|CONVERT(VARCHAR,DATEADD(S,(run_time/10000)*60*60 /* hours */
	|	+((run_time - (run_time/10000) * 10000)/100) * 60 /* mins */
	|	+ (run_time - (run_time/100) * 100)  /* secs */,
	|	CONVERT(DATETIME,RTRIM(run_date),113)),100) AS [TimeRun],
	|CASE WHEN enabled=1 THEN 'Enabled'
	|	ELSE 'Disabled'
	|	END AS [JobStatus]
	|FROM CTE_MostRecentJobRun MRJR
	|	JOIN sysjobs SJ ON MRJR.job_id=sj.job_id
	|WHERE Rnk = 1
	|	AND run_status = 0 -- i.e. failed
	|ORDER BY name
	|";
Показать
+ – Ответить
18. sertak 326 11.11.23 13:21 Сейчас в теме
Для тех, кто столкнется с этой проблемой: я решил проблему следующим образом: переделал временные таблицы на CTE.
Пример: 
SEL ECT
	PriceBuffer._PIN AS PIN,
	PriceBuffer._Brand AS Brand,
	PriceBuffer._PartnerID AS PartnerID,
	PriceBuffer._PriceCurrency AS PriceCurrency
INTO #tblPriceBuffer
FROM trade_add_t.dbo.PriceBuffer
;
SEL ECT
	PriceBuffer.PIN AS PIN,
	PriceBuffer.Brand AS Brand,
	PriceBuffer.PartnerID AS PartnerID,
	PriceBuffer.PriceCurrency AS PriceCurrency
FROM #tblPriceBuffer AS PriceBuffer
;
DR OP   TABLE #tblPriceBuffer
Показать


WITH CTE_PriceBuffer AS 
(
SELECT
	PriceBuffer._PIN AS PIN,
	PriceBuffer._Brand AS Brand,
	PriceBuffer._PartnerID AS PartnerID,
	PriceBuffer._PriceCurrency AS PriceCurrency
FR OM trade_add_t.dbo.PriceBuffer
)
SELECT
	PriceBuffer.PIN AS PIN,
	PriceBuffer.Brand AS Brand,
	PriceBuffer.PartnerID AS PartnerID,
	PriceBuffer.PriceCurrency AS PriceCurrency
FR OM CTE_PriceBuffer  AS PriceBuffer
Показать
+ – Ответить
Оставьте свое сообщение

Для получения уведомлений об ответах подключите телеграм бот:
Инфостарт бот