Question Linked Server MS Sql to Progress Replication Trigger

[Diaboliciq]

Hi,
I have MS sql Server and OpenEdge 10.2B DB. I add OpenEdge db on linked server with ODBC driver.
I need near real time replication Sql to Progress.I try to ddl triggers on linked server but it didnt fire Openedge tables for insert,update,delete. I try every possibility but i get an error like this :


OLE DB provider "MSDASQL" for linked server "XX DB" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Optional feature not implemented.".

Msg 7391, Level 16, State 2, Procedure Deneme, Line 8

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


Sql Options RPC,RPC out,Enable Promotion of Distributed Transactions for RPC True and Distributed transaction coordinator service is active.

How can i fix this.Is it possible MS SQL to Openedge db Replication please help me.

Thanks.

 

[TheMadDBA]

OpenEdge provides basic SQL support but not the same level of support as Oracle or MS SQL.

Why are you trying to add triggers to OE if you are trying to replicate from MS SQL to OE?

If you are trying to replicate from OE to MS SQL.... buy this from Bravepoint/Progress http://www.bravepoint.com/products-pro2-replication-suite.shtml

 

[Cringer]

Completely agree with the recommendation for Pro2. It's not as expensive as you might expect either.

 

[Diaboliciq]

Yes I know that program but our business plans require two directed replication. We need sql to oe and oe to sql. We think use pro2 for oe to sql. But sql to oe how can we solve this problem. Please Give me advise. And you test with pro2 ? How is it works

 

[Cringer]

I haven't used Pro2 yet in a production system but I've had the chance to play with it in an Arcade instance against sports2000. It works very well, is robust and is near real time.

 

[TheMadDBA]

Two way replication is going to be more of an issue... especially if you are talking about replicating the same data elements back and forth.

If you want replication from SQL to OE you are going to have to do a lot more work on the SQL side... meaning you are going to have to track the new and changed records on the SQL side and generate the appropriate DML to update OE.

OE is not SQL based, it just provides some basic SQL access. You aren't going to have distributed transactions or peer to peer replication like you can do with MS SQL and Oracle.

 

[Diaboliciq]

Yes we knew that and think about it,But How? We try DDL triggers,clr triggers,smo events , service broker with sqldependency on c#.We didnt capture new and changed datas.Can you lead which way we try?.We think to need send changed and new records send to c# for insert,update,delete or we fix this on linked server.We didnt know how can we do.We are trying.
Thank you so much

 

[TheMadDBA]

This is how the Pro2 replication works (also the model I have used for several other replication projects):

1) Make a new table or tables to track which records have been updated (you can use a pointer to the actual record or copy the entire record... pointers usually work better). At minimum you need the pointer back to the base record, a replication status, a record change event (delete,insert,update) and a timestamp

2) Use the DB triggers to populate this table

3) Write programs that read through the new tracking tables and generate the appropriate DDL to update the target database


You aren't going to fix the linked server to make the OE database do things it doesn't support.