Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 The current transaction cannot be committed and cannot support operations that write to...  [new]
Gobzo Kobler
Member

Откуда: у людей такая фантазия?
Сообщений: 387
Эта проблема уже довела меня до ручки.

Есть хранимая процедура (не я писал), которая расчитывает некий набор данных в табличной переменной и возвращает его. Она должна вызываться в цикле из другой хранимой процедуры для каждого завода компании.

При вызове с определенным набором параметров она бросает исключение с такими реквизитами:

ErrorNumber ErrorSeverity ErrorState  ErrorLine   ErrorMessage
----------- ------------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3930        16            1           355         The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.


Но если вызвать эту процедуру отдельно то ошибка не происходит!
Явных транзакций нигде нет насколько я вижу.
Временных таблиц тоже не используют - только табличную переменную заполняют при помощи курсоров.
Перепроверил все параметры по много раз: отдельно работает, из хранимой процедуры - нет.

Я сам курсоры никогда не использую, предпочитаю временную таблицу и цикл по ПК.
С вложенными курсорами это может быть связано?
15 мар 14, 02:21    [15728103]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
NickAlex66
Member

Откуда:
Сообщений: 319
Gobzo Kobler,

Профайлер вам в руки. Дает ответы на многие вопросы.
15 мар 14, 03:02    [15728133]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Crimean
Member

Откуда:
Сообщений: 13148
это в catch rollback не сделали
15 мар 14, 12:50    [15728837]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7869
Проверяйте состояние транзакции перед commit. Если она not commitable, то делайте rollback.
Суда по всему, при выполнении операций появляется ошибка, которая переводит транзакцию в нефиксируемое состояние, а процедура пытается зафиксировать безусловно.
Короче говоря, добавьте обработчик ошибки TRY в процедуру.
17 мар 14, 11:53    [15736927]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Gobzo Kobler
Member

Откуда: у людей такая фантазия?
Сообщений: 387
Владислав Колосов,

Нигде в коде нет begin tran.
17 мар 14, 17:10    [15740052]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7869
И commit нигде нет?

Что в строке 355?
17 мар 14, 18:18    [15740519]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Gobzo Kobler
Member

Откуда: у людей такая фантазия?
Сообщений: 387
Владислав Колосов,

commit тоже нет. выше было указано что явные транзакции не используются.
в строке 355 - вызов этой самой процедуры.
17 мар 14, 19:30    [15740964]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Gobzo Kobler
Member

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

BEGIN TRY
	CLOSE cur_1
	DEALLOCATE cur_1
END TRY
BEGIN CATCH
	PRINT 'Error closing/deallocating cur_1 - ignore'
END CATCH


Каждый раз, когда выдается вышеуказанное сообщение, предварительно выстреливает CATCH в этом блоке.
17 мар 14, 23:52    [15742257]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31446
Gobzo Kobler
Заметил вот что: в существующей процедуре есть такой блок

BEGIN TRY
	CLOSE cur_1
	DEALLOCATE cur_1
END TRY
BEGIN CATCH
	PRINT 'Error closing/deallocating cur_1 - ignore'
END CATCH

Каждый раз, когда выдается вышеуказанное сообщение, предварительно выстреливает CATCH в этом блоке.
А что же ошибка не передаётся дальше? Вы даже не знаете, что падает (CLOSE или DEALLOCATE), и по каким причинам.

Ещё можете для отладки поставить вывод текущего счётчика транзакций - возможно, всё таки где нибуть транзакция открывается, возможно, во вложенной процедуре.
18 мар 14, 00:14    [15742336]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Gobzo Kobler
Member

Откуда: у людей такая фантазия?
Сообщений: 387
alexeyvg,

Да, все точно. Каждый раз когда код попадает в любой (!) блок TRY CATCH, результат хранимой процедуры не может быть вставлен во временную таблицу командой

INSERT INTO #t
EXEC sp_abc

хотя сама по себе процедура sp_abc выполняется без ошибок и возвращает правильный набор данных.
Похоже на очередной баг 2008R2, если конечно MS не объявила это feature.

К счастью, мне откровенно по барабану из-за чего колется их код и где - я отвечаю только за свою вызывающую процедуру. Вот такие высокие отношения :(
18 мар 14, 00:19    [15742353]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Gobzo Kobler
Member

Откуда: у людей такая фантазия?
Сообщений: 387
Gobzo Kobler,

пардон, имелся в виду блок BEGIN CATCH.
18 мар 14, 00:20    [15742359]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31446
Gobzo Kobler
пардон, имелся в виду блок BEGIN CATCH.
В смысле, внутри блока BEGIN CATCH нельзя выполнить вставку в таблицу? Нет, такого бага нет.

Вы для начала ошибку прочитайте, может, там всё написано? :-)
Например, несоответствие #t и результата sp_abc, может, отладочный вывод вставили в sp_abc, да мало ли где программист может ошибиться.
18 мар 14, 00:31    [15742392]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Gobzo Kobler
Member

Откуда: у людей такая фантазия?
Сообщений: 387
alexeyvg,

Видно следующее: когда результат хранимой процедуры вставляется во временную таблицу, то внутри процедуры @@TRANCOUNT = 1 без единого BEGIN TRAN в коде. И вот когда в этой процедуре код попадает в BEGIN CATCH, то происходит поломка этой транзакции. Не знаю всегда ли, пока не было времени проверить эту теорию, но в данном конкретном случае это так.

В ней было 2 таких блока. Один для закрытия курсора, другой для отловки ошибок возможных при исполнении динамического выражения. Первый я переписал и убрал, достаточно оказалось просто найти правильное место закрытия курсора. От второго я избавиться пока не могу (без длительных обсуждений и одобрений свыше).

В оставшемся блоке просто происходит присвоение значения скалярной переменной и вывод PRINT сообщения. Этого достаточно чтобы результат, который процедура прекрасно собрала во временной переменной и выдала SELECTом в конце, не вставился во временную таблицу с которой процедура была вызвана.

Если это выглядит как баг и пахнет как баг, то это все таки похоже баг.
18 мар 14, 00:41    [15742423]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Gobzo Kobler
Member

Откуда: у людей такая фантазия?
Сообщений: 387
Воспроизвести ошибку можно вот таким кодом:

CREATE PROCEDURE #p
AS
BEGIN
	DECLARE @result INT

	BEGIN TRY
		EXEC sp_executesql 'Select 1/0'
	END TRY
	BEGIN CATCH
		SELECT @result = 1
	END CATCH

	SELECT @result AS result
END
GO

EXEC #p
GO

CREATE TABLE #t (result int)
GO

INSERT INTO #t
EXEC #p

GO

SELECT *
FROM #t

GO

DROP PROCEDURE #p
GO

DROP TABLE #t
GO
18 мар 14, 01:05    [15742473]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
Gobzo Kobler
когда результат хранимой процедуры вставляется во временную таблицу, то внутри процедуры @@TRANCOUNT = 1 без единого BEGIN TRAN в коде
Любая инструкция модификации данных выполняется в транзакции, вне зависимости от вашего желания. Т.к. insert exec и есть такая инструкция, то ваша процедура будет выполняться в транзакции.
Gobzo Kobler
И вот когда в этой процедуре код попадает в BEGIN CATCH, то происходит поломка этой транзакции.
Какой, оказывается, вредный этот блок CATCH - так и норовит транзакцию поломать. На самом деле все проще - в вашей процедуре возникает ошибка, переводящая транзакцию в uncommittable state. Ошибку же эту вы благополучно игнорируете в своем абсолютно безграмотно написанном блоке CATCH.
Gobzo Kobler
В оставшемся блоке просто происходит присвоение значения скалярной переменной и вывод PRINT сообщения. Этого достаточно чтобы результат, который процедура прекрасно собрала во временной переменной и выдала SELECTом в конце, не вставился во временную таблицу с которой процедура была вызвана.
Результат не вставился потому, что в транзакции, находящейся в uncommittable state недопустимы никакие модификации данных. О чем вам сервер доходчиво написал в сообщении об ошибке.
18 мар 14, 01:34    [15742515]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Gobzo Kobler
Если это выглядит как баг и пахнет как баг, то это все таки похоже баг.
Или элементарное незнание матчасти.
18 мар 14, 01:50    [15742536]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Crimean
Member

Откуда:
Сообщений: 13148
invm,

а что, тут как-то можно catch сделать "более грамотно"? у меня не вышло
сама природа insert / exec "достаточно странная", скажем так
я на нее также "попадал" при работе с "внешними данными" и достаточно с ней "натерпелся"
18 мар 14, 12:53    [15744679]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
Crimean
а что, тут как-то можно catch сделать "более грамотно"?
Я не о том как победить insert exec. Я о том, что у ТС невозможно выяснить причину, приведшую к uncommittable state.
18 мар 14, 13:14    [15744919]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Crimean
Member

Откуда:
Сообщений: 13148
invm,

ну так это как бы репро, как по мне - достаточно показательное. я вот быстро репро для этого случая так и не смог быстро сделать, хотя пара подходов была
понятно, что в catch можно добавить (условно) throw и будет ясна причина "почему"
но тут проблема insert/exec, а не построения конкретного кода, хотя оно (построение) тут, похоже, активно мешает найти причины
18 мар 14, 13:50    [15745273]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31446
Gobzo Kobler
Владислав Колосов,

Нигде в коде нет begin tran.
так у вас транзакция открывается, а вы говорите нету ;-)
18 мар 14, 14:07    [15745498]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
Crimean
ну так это как бы репро, как по мне - достаточно показательное.
Имхо, вот такое репро более показательно, т.к. демонстрирует еще и влияние устаноки xact_abort:
+
use tempdb;
go

create procedure dbo.Proc1
as
begin
 set nocount on;

 begin try
  select 1 / 0;
 end try
 begin catch;
  print error_message()
 end catch;
end;
go

set xact_abort on;

declare @t table (id int);
insert into @t
 exec dbo.Proc1;

select * from @t;
go

set xact_abort off;

declare @t table (id int);
insert into @t
 exec dbo.Proc1;

select * from @t;
go

drop procedure dbo.Proc1;
go

Crimean
но тут проблема insert/exec, а не построения конкретного кода, хотя оно (построение) тут, похоже, активно мешает найти причины
Не соглашусь. Это проблема конкретного кода. Он будет приводить к такой же ошибке и вне insert/exec.
18 мар 14, 14:25    [15745695]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Crimean
Member

Откуда:
Сообщений: 13148
invm,

так в этом репро ошибка не деление на ноль :) а "Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.", как минимум у меня. то есть банальный N не дописан при вызове sp_executesql. и с _таким_ (возможно и неочевидным) репро ваши оба примера дают одинаковый с "исходным" примером результат. но с xact_abort первый, разумеется, сваливается, а второй - доходит до select, возвращая пустой резалтсет
ну и, собственно, добавка "print error_message()" в "исходное" репро полностью выравнивает поведение обоих примеров если мы все еще эмулируем ошибку от передачи varchar вместо N-varchar для sp_executesql. хотя если даже поставить тупое деление на ноль - я тоже не особо вижу разницы в поведении обоих примеров
или ваш "мессадж" был только про "print error_message()"? он был совершенно справедливый и он был "прочитан" и "воспринят" но он никак не облегчает работу ни с insert / exec в частности ни с "current transaction cannot be committed" вообще
все имхо, разумеется
18 мар 14, 14:37    [15745838]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7869
Gobzo Kobler
Заметил вот что: в существующей процедуре есть такой блок

BEGIN TRY
	CLOSE cur_1
	DEALLOCATE cur_1
END TRY
BEGIN CATCH
	PRINT 'Error closing/deallocating cur_1 - ignore'
END CATCH


Каждый раз, когда выдается вышеуказанное сообщение, предварительно выстреливает CATCH в этом блоке.


Честно сказать, этот код выглядит странно, поскольку вполне достаточно курсор объявлять локальным.
Код подавляет ошибку, т.к. автор кода не нашел причины самопроизвольного исчезновения курсора или ошибки двойного закрытия или еще чего. Но при этом авто не учел, что ошибка может появляться не только по вине курсора, что и происходит.
Поставьте ;THROW вместо невнятного PRINT и проанализируйте, наконец, ошибку.

Этот код напоминает картину, которая на обоях дырку закрывает.
18 мар 14, 14:39    [15745861]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Crimean
Member

Откуда:
Сообщений: 13148
Владислав Колосов,

автор, скорее всего, слегка не в курсе про cursor_status, раз прячет совершенно безобидный код в try
18 мар 14, 14:50    [15745966]     Ответить | Цитировать Сообщить модератору
 Re: The current transaction cannot be committed and cannot support operations that write to...  [new]
Gobzo Kobler
Member

Откуда: у людей такая фантазия?
Сообщений: 387
invm,

Как всегда на этом форуме все свелось к бросанию калом. Вот поэтому я и бываю здесь так редко - из-за безнаказанного хамства хамства участников. К слову, процедуру писал не я и это было указано в самой первой строке первого сообщения. Но истина таких как вы исправить не в силах.
18 мар 14, 17:10    [15747498]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить