Linked Server DTS like function

[surzycki]

Hello,

I am not sure if this is possible but in short. I have two SQL servers, one is a 'protected' server and the other is accessable via the internet. What I need is to be able to modify data in the protected server and send it to the internet visible server without explicity connecting to the protected server via ADO's, thus linked server and triggers.

So, data is modified in the proteceted server and sent to the internet visible server via a trigger and linked server setup.

THE PROBLEM: Some data needs to go the other way as well, without and explicit ADO connection. But, setting up a trigger and linked server in the other direction would cause an infinite loop of triggered updates.

MY SOLUTION: Create a buffer table in the internet visible server to recieve the data and trigger to update the protected server (via a link), once the protected server has been updated, it will via its own trigger update the proper table in the internet visible server. Right??? WRONG. The error I recieve is

Server: Msg 7391, Level 16, State 1, Procedure fnChangeData, Line 12
Operation cant be completed because OLE DB 'SQLOLEDB' doesn't handle distributed transactions
Server: Msg 3910, Level 16, State 1, Procedure fnChangeData, Line 12
Transaction context in use by another session.

YET, the triggers work fine once I remove the buffer trigger.

Is this architecture not possible???

Thanksd for any insight

Stefan

 

[NoCoolHandle]

What about replication?? This can be done through a firewall. It sounds like it might be the option as you can write your triggers as not for replication..

 

[surzycki]

I thought about replication, but (as far as I know) the replication is on entire DB or tables and can only be schedualed. For me this is a great waste of resources to be replicating entire DB or tables every X minutes when the data that is changing between DB's is minimal and occurs infrequently, however it must be immediatly replicated.... Perhaps I can pass through another server, b/c I have learned that one cannot do what I would like to do.

 

[ukwiz]

Sounds like you need transactional replication.

From BooksOnline:
Transactional Replication
With transactional replication, an initial snapshot of data is propagated to Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers.

SQL Server 2000 monitors INSERT, UPDATE, and DELETE statements, and changes to stored procedure executions and indexed views. SQL Server 2000 stores the transactions affecting replicated objects and then it propagates those changes to Subscribers continuously or at scheduled intervals. Transaction boundaries are preserved. If, for example, 100 rows are updated in a transaction, either the entire transaction with all 100 data modifications are accepted and propagated to Subscribers or none of them are. When all changes are propagated, all Subscribers will have the same values as the Publisher.

Options available with transactional replication allow you to filter published data, allow users at the Subscriber to make modifications to replicated data and propagate those changes to the Publisher and to other Subscribers, and allow you to transform data as it is published.
Transactional replication is typically used when:

You want data modifications to be propagated to Subscribers, often within seconds of when they occur.


You need transactions to be atomic (either all or none applied at the Subscriber).


Subscribers are mostly connected to the Publisher.


Your application will not tolerate high latency for Subscribers receiving changes.