none
Transação distribuida no SQL Server

    Question

  • Boas!!

    Sou iniciante em banco de dados e estou com uma duvida. Tenho uma integração para fazer com outra base de dados (pra ser específico é o OpenEdge Progress), e para fazer a integração entre SQL Server x Progress estou usando LInkedServer.

    Para fazer um select ou insert, faço a seguinte instrução:

    INSERT OPENQUERY("linked-banco", 'SELECT id FROM PUB."tabela" ') VALUES (4)

    Fazendo via comando eu consigo normalmente. Só que eu necessito fazer isso via TRIGGER. Se eu montar uma trigger com essa instrução ele retorna um erro dizendo:

    "The operation could not be performed because OLE DB provider "MSDASQL" for linked server "linked-banco" was unable to begin a distributed transaction."

    Alguem pode me ajudar se há alguma forma de eu "desativar" essa transação distribuida, ou conseguir passar isso?

    att

    Rafael

    Wednesday, July 10, 2013 5:18 PM

All replies

  • Hi RDalsenter,

    This is an English forum, you can ask your question on your local forum. I translated your question with Bing and get the following result:

    Distributed transaction in SQL Server
    Good!!
    I am new to the database and I'm with a doubt. Have to make integration with other database (to be specific is the Progress OpenEdge), and to make the integration between SQL Server x Progress I am using linked server.
    To make a select or insert, I make the following statement:
    INSERT OPENQUERY ("linked-Bank", ' SELECT id FROM PUB. ""table ') VALUES (4)
    Making via command I get normally. Just what I need to do this via TRIGGER. If I mount a trigger with this statement he returns an error saying:
    "The operation could not be performed because OLE DB provider" MSDASQL "for linked server" linked-Bank "was unable to begin a distributed transaction."
    Can someone help me if there is any way I "disable" this distributed transaction, or get past it?
    att
    Rafael

    First, I want to confirm what’s the SQL Server version on your server, if it is SQL Server 2005 or 2008, we can install the following hotfix. Additionally, we can set the linked server "Enable Promotion of Distributed Transactions" to false and check the result.

    FIX: Error message when you run a distributed query in SQL Server 2005 or SQL Server 2008: "OLE DB provider 'SQLNCLI' for linked server '<Linked Server>' returned message 'No transaction is active'"
    http://support.microsoft.com/kb/954950

    Linked server : The partner transaction manager has disabled its support for remote/network transactions
    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/24d74b4f-cf65-4b1b-8ba6-e87c1858b710/linked-server-the-partner-transaction-manager-has-disabled-its-support-for-remotenetwork

    Allen Li
    TechNet Community Support

    Wednesday, July 17, 2013 3:11 PM