Ошибка BEGIN TRY/CATCH и MSDTC

1/Следующий фрагмент кода показывает мне ожидаемую ошибку: Оператор INSERT противоречил ограничению FOREIGN KEY FK _...

SET XACT_ABORT ON;

BEGIN TRANSACTION

    INSERT INTO linkedsrv1.db1.[dbo].tbl1 ([Col1], [Col2])  
    VALUES (1200, 0)                

COMMIT TRANSACTION

2/Но когда я помещаю это в BEGIN TRY/CATCH, сообщение об ошибке является неопределенным: Msg 1206, уровень 18, состояние 118, строка 18 Координатор распределенных транзакций Microsoft (MS DTC) отменил распределенную транзакцию.

SET XACT_ABORT ON;

BEGIN TRY  
    BEGIN TRANSACTION   

        -- Error is on this line
        INSERT INTO linkedsrv1.db1.[dbo].tbl1 ([IdWebsite], [IdProductType])  
        VALUES (1200, 0)   

    COMMIT TRANSACTION
END TRY  
BEGIN CATCH
    PRINT 'Error' -- Code not reached

    SELECT ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()

    IF XACT_STATE() != 0   
        ROLLBACK TRANSACTION
END CATCH

Любая идея, почему это происходит?

Редактировать позже:

Он работает, если я удалю ненужную явную транзакцию. Пока неясно, почему я получаю эту ошибку, когда я помещаю BEGIN/COMMIT TRAN.
Я получаю ту же ошибку, если у меня есть несколько вложений в нескольких таблицах, расположенных на связанном сервере.

Любые комментарии/замечания приветствуются.

Ответ 1

Из MSDN:

Проблема

Рассмотрим следующий сценарий. Для создания связанного сервера в SQL Server 2005 используется поставщик OLE DB собственного SQL-клиента (SQLNCLI). Вы создаете распределенную транзакцию. Распределенная транзакция содержит запрос, который использует связанный сервер для извлечения данных из таблицы. При фиксации распределенной транзакции может появиться следующее сообщение об ошибке:

Msg 1206, Level 18, State 167, Line 3 
The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled 
the distributed transaction.

Кроме того, при запуске запроса после возникновения такого поведения может появиться следующее сообщение об ошибке:

Msg 8525, Level 16, State 1, Line 1 
Distributed transaction completed. Either enlist this session in a new 
transaction or the NULL transaction.

Эта проблема возникает, если выполняются следующие условия:

You use the SQLNCLI provider to create a linked server between two 
instances of SQL Server 2005.

The XACT_ABORT option is set to ON.

In the distributed transaction, you try to release a rowset before 
all rows in the rowset are processed.

Примечание Эта проблема также может возникать, если вы вызываете метод ReleaseRows в распределенной транзакции для освобождения набора строк до того, как вы совершите распределенную транзакцию в приложении.

ПРИЧИНА

Эта проблема возникает из-за того, что поставщик SQLNCLI неправильно посылает сигнал привязки на связанный сервер для отката распределенной транзакции.

Временное решение

Чтобы поставщик SQLNCLI не отправлял сигнал внимания на сервер, используйте поставщик SQLNCLI для полного использования любых наборов строк, которые создает клиент OLE DB.

Update

вам нужно настроить 'remote proc trans' на "1" в параметрах сервера.

Пример:

exec sp_configure 'remote proc trans', '1' перенастроить с переопределением

Это позволит вам выполнить любые распределенные запросы.

Больше обновлений

Если вы используете .Net framework в интерфейсе, я думаю, вы можете использовать TransactionScope Класс. Удалите транзакцию из запроса и поместите транзакцию в уровень кода.

Ответ 2

Я пережил эту боль! Если вы выполняете любую операцию CRUD на одной таблице, транзакция не нужна.

В этом случае проблема заключается в том, что XACT_STATE() возвращает -1, поскольку в активной транзакции есть ошибка. Но ROLLBACK TRANSACTION терпит неудачу, так как НИКАКИХ транзакций не произошло. Вы выполнили только одну транзакцию INSERT, которая не удалась, поэтому нет других транзакций для отката.

Всегда лучше ретранслировать на @@TRANCOUNT, чем XACT_STATE() (по крайней мере в этом случае).

, чтобы заставить его работать, измените его (хотя я не поддерживаю TRAN для отдельной таблицы):

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

Ответ 3

BEGIN TRANSACTION запускает распределенную транзакцию между сервером, на котором запущены операторы и связанный сервер, поскольку потенциально вы можете запускать обновления для обоих серверов. Когда сбой INSERT, он должен отменить распределенную транзакцию, таким образом, вы получите ошибку. Таким образом, вы должны обрабатывать ошибки на двух уровнях (вставка и транзакция). В этом случае вам понадобятся два блока TRY/CATCH следующим образом:

SET XACT_ABORT ON;

BEGIN TRY  
    BEGIN TRANSACTION   

    BEGIN TRY
        -- Error is on this line
        INSERT INTO linkedsrv1.db1.[dbo].tbl1 ([IdWebsite], [IdProductType]) 
        VALUES (1200, 0)
    END TRY
    BEGIN CATCH
        SELECT 'Insert Error', ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()
        RAISERROR (15600,-1,-1, 'INSERT ERROR');
    END CATCH 

    COMMIT TRANSACTION
END TRY  
BEGIN CATCH
    SELECT 'Transaction Error', ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()

    IF XACT_STATE() != 0   
        ROLLBACK TRANSACTION
END CATCH