Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 INSERT на linked-сервере  [new]
Ejhi
Member

Откуда:
Сообщений: 103
Добрый день.

На сервере1 есть связанный сервер2 и джоба, в которой запускается процедура. В процедуре происходит следующее:

SET NOCOUNT ON;
SET XACT_ABORT ON;

-- Сбор всяких данных на локальном сервере в переменную таблицу
DECLARE @T TABLE(col1, col2)

INSERT @T 
SELECT col1, col2
FROM LocalServerDBTables

-- Вставка собранных данных в таблицу на локальном сервере и на связанном сервере
BEGIN TRY

	BEGIN TRAN

	INSERT LocalServerDBTable(col1)
	SELECT col1
	FROM @T T

	INSERT LinkedServerDBTable(col1, col2)
	SELECT col1, col2
	FROM @T T

END TRY

BEGIN CATCH
	/* 
	Если возникла какая-то ошибка, откатываем транзакцию и инициируем ошибку уровня 16, 
	чтобы шаг джобы однозначно завершился неуспешно. 
	*/
	IF @@TRANCOUNT > 0 ROLLBACK;
	DECLARE @ERROR_MESSAGE VARCHAR(2048) = ERROR_MESSAGE()
	RAISERROR(@ERROR_MESSAGE, 16, 1)
		
END CATCH

IF @@TRANCOUNT > 0 COMMIT;


Важно, что в транзакции данные вставляются и на локальном сервере и на удаленном. И если хотя бы один INSERT завершился с ошибкой, надо откатить оба.

Иногда при вставке данных в таблицу на связанном сервере возникает ошибка
OLE DB provider "SQLNCLI10" for linked server "server2" returned message "No transaction is active.".
Сообщение 0, уровень 11, состояние 0, строка 0
При выполнении текущей команды возникла серьезная ошибка.. При наличии результатов они должны быть аннулированы.


По идее ошибка уровня 11 должна обрабатываться в блоке CATCH, но при возникновении такой ошибки шаг джобы заканчивается успешно. И соответственно мы не сразу понимаем, что у нас проблемы со вставкой данных.
Повторюсь, что ошибка при вставке возникает не всегда, и поэтому планирую просто добавить для шага джобы несколько попыток запуска. Но для этого надо чтобы процедура завершилась с ошибкой.

Подскажите, пожалуйста, что можно сделать.

@@VERSION server1:
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

@@VERSION server2:
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4286.0 (X64)   May 29 2013 15:04:02   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 
21 фев 14, 10:42    [15603512]     Ответить | Цитировать Сообщить модератору
 Re: INSERT на linked-сервере  [new]
Ejhi
Member

Откуда:
Сообщений: 103
UP
24 фев 14, 09:47    [15617284]     Ответить | Цитировать Сообщить модератору
 Re: INSERT на linked-сервере  [new]
it17
Member

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

погуглить?
достаточно много тем
посмотрите здесь
http://36chambers.wordpress.com/2011/04/08/msdtc-no-transaction-is-active/
24 фев 14, 18:12    [15621536]     Ответить | Цитировать Сообщить модератору
 Re: INSERT на linked-сервере  [new]
Ejhi
Member

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

Добрый день.

Я пытаюсь решить задачу обработки возможных ошибок, а не устранения одной конкретной.
За ссылку спасибо, посмотрю что там с MSDTC. Но все же мне хотелось понять, что я делаю неправильно в TRY CATCH.
Сегодня проблемы могут быть из-за MSDTC, а завтра из-за чего-нибудь другого. Поэтому нужно обеспечить оперативное информирование о возникающих ошибках.
25 фев 14, 10:05    [15623838]     Ответить | Цитировать Сообщить модератору
 Re: INSERT на linked-сервере  [new]
Glory
Member

Откуда:
Сообщений: 104751
Ejhi
Я пытаюсь решить задачу обработки возможных ошибок, а не устранения одной конкретной.

Мне кажется, что ошибку с номером 0 нельзя обработать.
Потому, что ее просто не существует в списке ошибок.
25 фев 14, 11:31    [15624431]     Ответить | Цитировать Сообщить модератору
 Re: INSERT на linked-сервере  [new]
Ejhi
Member

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

Спасибо за наводку.

Попытался сэмулировать ситуацию, но RAISERROR(0, 11, 0, '') сам по себе возвращает ошибку 2732.
Ок, буду гуглить дальше.
25 фев 14, 14:23    [15625901]     Ответить | Цитировать Сообщить модератору
 Re: INSERT на linked-сервере  [new]
o-o
Guest
Ejhi
Попытался сэмулировать ситуацию, но RAISERROR(0, 11, 0, '') сам по себе возвращает ошибку 2732.


...в смысле, хотите написать код, к-ый вызовет это:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded
?
(могу поделиться)
25 фев 14, 14:35    [15626028]     Ответить | Цитировать Сообщить модератору
 Re: INSERT на linked-сервере  [new]
Ejhi
Member

Откуда:
Сообщений: 103
o-o
...в смысле, хотите написать код, к-ый вызовет это:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded
?
(могу поделиться)


Да, именно это хочу. Поделитесь, пожалуйста.
25 фев 14, 15:50    [15626740]     Ответить | Цитировать Сообщить модератору
 Re: INSERT на linked-сервере  [new]
o-o
Guest
валится с этой ошибкой, если вставлять через insert exec в таблицу,
где полей больше, чем в возвращаемом резалтсете,
и чтоб в процедуре было try..catch.
есть где-то на форуме, но ник у меня был не о-о тогда, шиш найдешь теперь

create proc dbo.msg0
as
   begin try
      select 1;
   end try
   
   begin catch
      select 2;
   end catch;
      	
declare @t table (col1 int, col2 int);
insert @t exec dbo.msg0
25 фев 14, 16:04    [15626865]     Ответить | Цитировать Сообщить модератору
 Re: INSERT на linked-сервере  [new]
Ejhi
Member

Откуда:
Сообщений: 103
o-o,

Спасибо. Ваш скрипт действительно возвращает ошибку 0.

Воткнул его в свой тест:
CREATE TABLE #TMP(ID INT)
INSERT #TMP VALUES(1)

BEGIN TRY

	BEGIN TRAN

	INSERT #TMP VALUES(2)
	
	declare @t table (col1 int, col2 int);
	insert @t exec dbo.TEST_msg0	--	Возвращает "Сообщение 0, уровень 11, состояние 0, строка 0"

END TRY

BEGIN CATCH

	IF @@TRANCOUNT > 0 ROLLBACK;
	DECLARE @ERROR_MESSAGE VARCHAR(2048) = ERROR_MESSAGE()
	RAISERROR(@ERROR_MESSAGE, 16, 1)

END CATCH

IF @@TRANCOUNT > 0 COMMIT;


SELECT * FROM #TMP
--DROP TABLE #TMP


Возвращает
(строк обработано: 1)

(строк обработано: 1)
Сообщение 3998, уровень 16, состояние 1, строка 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

В таблице #TMP всего 1 запись.

Вроде все ок, транзакция откатилась, но при этом не произошла передача @ERROR_MESSAGE в блоке CATCH. Пробовал подствалять просто строку вместо ERROR_MESSGAE(), все равно возвращается 3998.
Похоже, что хак с нулевой ошибкой чреват странными эффектами. При возникновении проблем с MSDTC транзакция просто откатывалась, но шаг джобы успешно завершался.

Я теперь склоняюсь к тому, что надо решить проблемы с MSDTC, а ошибки с MsgID > 0 будут успешно отлавливаться.
25 фев 14, 16:39    [15627272]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить