How to insert data into another database with Linked Server

Asked

Viewed 325 times

0

Good afternoon!

How do I create a trigger that uses a Linked Server to insert the INSERTED data in another table that is in another database, this in another server?

Trying to Exemplify:

CREATE TRIGGER [TESTE] ON [TABELA_XYZ]
   AFTER INSERT
AS 
BEGIN
  INSERT INTO LINKSERVER.BD.DBO.TABELA_ABC (CAMPO1, CAMPO2)
  SELECT CAMPOA, CAMPOB FROM INSERTED
END

With this same "Trigger" I was able to insert the data of this table in another table of the same server/ bank, but when I try to use Linked Server is giving this error:

OLE DB provider "SQLNCLI11" for linked server "DMPAP8" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 2
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "DMPAP8" was unable to begin a distributed transaction.

The two banks are SQL Server 2012, but are on different servers, Server and Server, and I need to copy from server B to A and table XYZ to ABC.

Any doubt I’m available.

sql-server trigger linked-server

  • to synchronize tables between different instances there are other less risky methods than trigger procedure that uses server binding (Linked server). Evaluate, for example, the use of some kind of replication or even a job sync from time to time.

  • Was any of the answer helpful? Don’t forget to choose one and mark it so it can be used if someone has a similar question!

2 answers

2

Boas, Since the 2 Servers are SQL Server 2012, why don’t you use SQL Server Native Client instead of OLE DB. Ole DB works with more widespread features of various Database engines. Quite different from Native Client, which uses native SQL Server features. Also, check the settings in the following image, and the inactive if you don’t need it: inserir a descrição da imagem aqui

  • SQLNCLI is the Native Client...

0

First ensure that the "Distributed Transaction Coordinator" is running on all servers:

  • Squeeze Windows KeyR;
  • Typo services.msc;
  • Squeeze Enter;
  • Start the "Distributed Transaction Coordinator" if it is not running.

If the client application is not on the same machine as the server perform the following steps on the server:

  • Squeeze Windows KeyR;
  • Typo comexp.msc;
  • Squeeze Enter;
  • In the tree on the left side navigate to Component Services| Computers | My Computer | Distributed Transaction Coordinator;
  • Right-click on "DTC Local" and "Properties";
  • Go to the tab "Security";
  • Ensure that the options "Network DTC Access", "Allow Remote Clients", "Allow Entry" and "Allow Output" are marked;
  • Click on "Apply";
  • Restart the service on services.msc;
  • You may need to restart the server;

In the client do the same procedure above and ensure the options "Network DTC Access", "Allow Entry" and "Allow Exit". Restart the service and server if necessary.

In "SQL Server Service Manager" the "Distributed Transaction Coordinator" service must be running.

References:

Browser other questions tagged sql-server trigger linked-server

You are not signed in. Login or sign up in order to post.