Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 сессии и nesting level exceeded (limit 32)  [new]
RKam
Member

Откуда:
Сообщений: 18
Есть SP которая копирует запись в другую базу. Разные записи из разных таблиц.

Есть программа, которая запускает эту SP, иногда тысячи раз

Проблема 1 - при запуске из программы, плодятся сессии и после 500-той отваливается по ошибке


Сделал запуск SP из самой SP (с проверками)

Проблема 2 - получаю ошибку nesting level exceeded (limit 32)
, триггеры действительно есть, кривые в т.ч. но трогать их нельзя(

Сделать курсор внутри SP будет довольно трудоемко, придется все переписывать.

джоб- не выход...

А есть ли выход? Без курсора, без джоба?
5 окт 13, 21:50    [14928664]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
RKam
Member

Откуда:
Сообщений: 18
Даже sp_executesql используемая в моей SP так же вызывает ошибку nesting level exceeded хотя в свойствах Recursive Triggers Enable set to false
5 окт 13, 21:59    [14928687]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
RKam
Проблема 1 - при запуске из программы, плодятся сессии и после 500-той отваливается по ошибке
Поправить ошибку в программе - там не делается закрытие коннекта.

RKam
Проблема 2 - получаю ошибку nesting level exceeded (limit 32), триггеры действительно есть, кривые в т.ч. но трогать их нельзя(
Запретить для базы вложенный вызов триггеров.
6 окт 13, 01:13    [14929433]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
RKam
Member

Откуда:
Сообщений: 18
автор
Запретить для базы вложенный вызов триггеров.

Вроде как запрещен -Recursive Triggers Enable false, да и ругается не только на триггеры но и системную sp_executesql

А если вызвать такуюже SP с другим именем (четные nesting level выполняет одна нечетные другая) это решит проблему?
6 окт 13, 12:54    [14929960]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
RKam
Вроде как запрещен -Recursive Triggers Enable false, да и ругается не только на триггеры но и системную sp_executesql
Что то неопределённое у вас :-)

Напишите, что вызывается и какое сообщение об ошибке.
Может, это не к триггеру относится.
6 окт 13, 13:32    [14930010]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
Уленшпигель
Member

Откуда:
Сообщений: 115
RKam
Сделал запуск SP из самой SP (с проверками)

Проблема 2 - получаю ошибку nesting level exceeded (limit 32)


похоже, ваша процедура вызывает себя или другие хп больше 32 раз. Непонятно, как это может произойти при простом копировании записей. Посмотрите стпрвку по [url="http://technet.microsoft.com/en-us/library/ms187371(v=sql.90).aspx"]@@nestlevel[/url]
6 окт 13, 14:01    [14930048]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
RKam
Member

Откуда:
Сообщений: 18
Если в таблице, куда пишу есть триггер, то ругается так:
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
если триггеров нет, то на системную sp
Msg 217, Level 16, State 1, Procedure sp_executesql Maximum stored procedure nesting level exceeded (limit 32).

В моей СП есть вызов sp_executesql и в конце процедуры, есть запуск самой себя, но с другими параметрами (будет копироваться уже другая запись) никаких дублей здесь нет, разумеется sp_executesql и моя СП запустится 32 раза, но она будет отрабатывать каждый раз новые данные!

Где криминал? не кашерно запускать сп на рекурсив?
7 окт 13, 00:15    [14931593]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
RKam
Member

Откуда:
Сообщений: 18
вот скрипт, повырезал, оставил самое важное

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[rectest]
	@s  NVARCHAR(100)='',
	@b  NVARCHAR(100)='',
	@c  NVARCHAR(100)='',	
	@t  NVARCHAR(100)='',	
	@r1 NVARCHAR(100)='',
	@r2 NVARCHAR(100)=''	
AS
BEGIN
    SET NOCOUNT ON;
     
	DECLARE @SQL NVARCHAR(MAX)
....................

SET @SQL =(N'
..............................
IF LEN(@a) > 1
	SET @a=(SELECT LEFT(@a,LEN(@a)-1))
')

EXEC sp_executesql @SQL,N'@f VARCHAR(100)',@f OUTPUT
SET @SQL = (N'INSERT INTO '+@sbt+' ('+@f+') SELECT TOP 1 '+@ff+' FROM '+@sbf+' f WHERE '+@z+' AND NOT EXISTS(SELECT TOP 1 * FROM '+@sbt+' WHERE '+@x+')') -- формирую запрос

EXEC sp_executesql @SQL -- выполнение 
--loop 
SET @SQL =(N'
перед вызовом ставится метка в таблице, что запись отработана, выбирается новая запись и определяются параметры @s,@b,@c,@t,@r1,@r2
')
EXEC sp_executesql @SQL,N'@s VARCHAR(100)..........',@s OUTPUT........... --формирую параметры

exec [dbo].[rectest] @s,@b,@c,@t,@r1,@r2 --запуск самой себя с новыми параметрами

END 
7 окт 13, 00:29    [14931615]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
Гость333
Member

Откуда:
Сообщений: 3683
RKam
автор
Запретить для базы вложенный вызов триггеров.

Вроде как запрещен -Recursive Triggers Enable false

Вложенный вызов триггеров (nested triggers) и рекурсивный вызов триггеров (recursive triggers) -- это две разные вещи, две разные настройки. Но это так, к слову, и к ситуации не имеет никакого отношения.

RKam
А если вызвать такуюже SP с другим именем (четные nesting level выполняет одна нечетные другая) это решит проблему?

Нет, не решит. Будет точно такая же ошибка "nesting level exceeded (limit 32)". Без разницы, будет ли цепочка вызовов "a -> a -> a -> ... -> a", или "a -> b -> a -> ... -> b", уровень вложенности всё равно будет одинаков:
BOL -> @@NESTLEVEL
Каждый раз, когда хранимая процедура вызывает другую хранимую процедуру или выполняет управляемый код путем обращения к подпрограмме, типу или статистическому выражению среды CLR, уровень вложенности возрастает. При достижении максимального уровня 32 транзакция прекращается.


RKam
моя СП запустится 32 раза, но она будет отрабатывать каждый раз новые данные!

Какая разница, новые данные или старые? Уровень вложенности растёт независимо от этого.

RKam
Где криминал? не кашерно запускать сп на рекурсив?

Забудьте про рекурсию при работе с T-SQL. Nesting level же.
Нет никакой настройки для увеличения максимального уровня вложенности, 32 -- это значение, зашитое в коде SQL Server.

RKam
вот скрипт, повырезал, оставил самое важное

Из представленного кода не понятно следующее:
-- Для чего тут рекурсия, а не (к примеру) цикл?
-- Для чего тут динамический SQL?
-- Для чего тут вообще универсальная процедура?
7 окт 13, 03:06    [14931760]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
RKam
Member

Откуда:
Сообщений: 18
Спасибо за ответ! теперь все понятно, нужно переделать СП под цикл.

Рекурсией хотел больше алгоритма передать на сервер, чтобы упростить код в программе и ускорить выполнение.
7 окт 13, 07:25    [14931847]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
Гость333
Member

Откуда:
Сообщений: 3683
RKam
Спасибо за ответ! теперь все понятно, нужно переделать СП под цикл.

Эээ... я вовсе не хотел сказать, что нужно переделать под цикл. Хотя и предполагал, что вы именно так это интерпретируете. SQL заточен под множественную обработку данных, т.е., условно говоря, чем больше данных удаётся обработать за одну операцию — тем лучше. Если вместо одного цикла с тысячей insert'ов по одной записи можно сделать один insert с тысячей записей — следует выбрать второй вариант. Но вводных от вас недостаточно, чтобы точно сказать, что такой вариант возможен.
7 окт 13, 09:16    [14932058]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
Roman_V2
Member

Откуда:
Сообщений: 3
[quot Гость333]
RKam
Но вводных от вас недостаточно, чтобы точно сказать, что такой вариант возможен.


Вот! К самому интересному подошли, сейчас запрос на Insert одной записи бывает длиной около 8000 знаков (много полей в таблице)
Вставку делаю через Select.
Я могу в строку сделать наборный фильтр, но пока не понимаю как он должен выглядеть?
Если делаю так [Entry No_]=13212 OR [Entry No_]=42324 выбирается одна строка

Т.е. вопрос в том, как должен выглядеть фильтр в WHERE когда нужно отфильтровать по одному полю много значений
И второй вопрос насколько длинный запрос можно сгенерить?
7 окт 13, 11:15    [14932676]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
Glory
Member

Откуда:
Сообщений: 104751
Roman_V2
Т.е. вопрос в том, как должен выглядеть фильтр в WHERE когда нужно отфильтровать по одному полю много значений

Как соединение с таблицей, в которой хранятся эти значения
7 окт 13, 11:17    [14932688]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
Roman_V2
Если делаю так [Entry No_]=13212 OR [Entry No_]=42324 выбирается одна строка
Не может быть.

Roman_V2
Т.е. вопрос в том, как должен выглядеть фильтр в WHERE когда нужно отфильтровать по одному полю много значений
Вместо OR можно написать более коротко:
WHERE [Entry No_] IN (13212, 42324, ...)

Но и с OR тоже правильно.

Ещё лучше не записи передавать, а какие то другие критерии переноса строк.
Roman_V2
RKam
Но вводных от вас недостаточно, чтобы точно сказать, что такой вариант возможен.


Вот! К самому интересному подошли
Что "Вот"? Расскажите, какая задача, может, её получится решить оптимальнее.

Откуда вы получаете эти ИД записей, переписываете из приказа директора?
7 окт 13, 11:21    [14932712]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
Roman_V2
Member

Откуда:
Сообщений: 3
WHERE [Entry No_] IN (13212, 42324, ...)
Да, работает, TOP 1 было...

Записи формирует программа и записывает в таблицу, по этой таблице делаю цикл и каждую запись обрабатываю...записей бывает 5тыс
Теперь сделаю IN (13212, 42324, ...) в одном запросе, но опять же вопрос насколько длинный может быть запрос?
7 окт 13, 11:34    [14932794]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 32167
Roman_V2
но опять же вопрос насколько длинный может быть запрос?
В принципе 256 мегабайт, но на практике сервер на таких объёмах затыкается.

Если записей может быть очень много, то лучше вставляйте из вашей "программы" ИД во временную таблицу, а потом делайте JOIN в запросе.
7 окт 13, 11:36    [14932807]     Ответить | Цитировать Сообщить модератору
 Re: сессии и nesting level exceeded (limit 32)  [new]
Roman_V2
Member

Откуда:
Сообщений: 3
alexeyvg
Roman_V2
но опять же вопрос насколько длинный может быть запрос?
В принципе 256 мегабайт, но на практике сервер на таких объёмах затыкается.

Если записей может быть очень много, то лучше вставляйте из вашей "программы" ИД во временную таблицу, а потом делайте JOIN в запросе.


Спасибо большое!
7 окт 13, 11:47    [14932867]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить