Linked server

Transact-SQL
1

I need to insert data by trigger a SQL SERVER 2008-server to server SQL SERVER 2012.

I made the nécesssaire to link the server 2,

I have a message:

Source error : .NET SQLClient data Provider
Fournisseur OLD DB "SQLNCCLI11" the linked server was unable to start a distributed transaction

knowing that the same trigger works well between two of the same type server (2008-2008 and 2012-2012)

(the message is in French I try to translate)

2

I want to clarify that the operation
INSERT INTO [SERVER]
SELECT ... FROM [SERVER1]
works well from the query analysis
but does not work from the trigger

3

In your linked server properties try setting:
Data Access - True
RPC - True
PRC Out - True
Enable Promotion of Distributed Transactions - True

I think this approach is a bad idea as the remote insert will be synchronous and so stall your application if the link goes down. I would be inclined to look at Transactional Replication. If you are prepared to do a bit of coding, you could also look at using the Service Broker.

4

I agree, we never create Triggers which update data in another server. We would create a "Queue" table (in the local server) and then have something "process" that Queue table in order to "distribute" that change to the remote server. If the connection goes down then that change would be distributed "as soon as the connect is re-established". Service Broker would do that well, but it is also easy to roll-your-own.

But it is possible that it is critical to the O/P that this process IS synchronous?