July 26, 2006 at 7:13 am
Hi Folks,
I have spent the last two days searching the internet (with no luck) for some direction on what I think could be a data replication task. I have never used replication before so it’s a bit of a gray area for me.
I have developed an inventory warehouse application that uses a SQL 2000/2005 database. A Microsoft gold partner has asked me to seamlessly integrate my db with a Microsoft ERP application that also uses a SQL database. What I thought would be rather easy issue has really got me bogged down, so may someone can point me in the right direction.
An example of events would be:
A sales order record is inserted into MS ERP database table, this record needs to be replicated into my database (Sales Order table). The Schema’s of the two tables are not the same, so I think this is going to be a problem for replication. If a record is delete/updated then these actions would also have to be reflected in my database.
This would only be a one way replication, meaning my database can not perform inserts/Updates/deletes on the MS ERP database table.
Options that I have thought of are.
1. Use triggers on the MS ERP database table to run a stored procedure to export the record out to a csv file. (this is messy)
2. Create a Transactional Component to distribute the record, this is my preferred option but because it is not my database creating the transaction I can see this working.
3. Use DTS, not really an option because the two databases need to be almost real time.
4. Use data replication..
Regards Ian Woods
July 26, 2006 at 2:30 pm
I used a replicated view once in a similiar situation.
basically set up a view on your database that has the same name as the table on the ms erp database, the view would point to a table with similiar structure but possibly not the same.
other way is to set up identical table on your database and then use triggers on your database to write the data in some other form.
July 27, 2006 at 10:40 am
Similiar to previous post.
The MS ERP database writes the data into a clone of the "SaleOrder" table. This can be done via a trigger. This "SalesOrder" table is then replicated.
July 27, 2006 at 5:20 pm
Thanks for your help on this one.
I know you are asking yourself why not do a lookup on the ERP database tables when needed, well one of the spec's required was that if the ERP database was taken offline my warehouse application (database) could still pick the sales order's.
Ok, so I write the triggers to clone the ERP Database table(s). Any pointers as to how I setup the replication on the cloned table(s)?
Thanks once more..
July 28, 2006 at 12:52 pm
since the tables have different structures I doubt if replication will work. You could dig into the sp_MSinsert/update/delete stored proc generated by the replication wizard and mess with them but replication itself is messy enough and again having to deal with this is prbly not worth it. I would recommend triggers if that is an option. Some companies have rules -NO Triggers (ours does).
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
August 2, 2006 at 4:42 am
Personally I would create an interim table, on your subscriber server, identical to the source table, you can replicate the source data into this new table, you can then use say a stored proc that fires either by a trigger on this table or as a timed event to populate the required table. In this way you can isolate any data errors prior to import. This gives you more control of the data into your table.
Regards
Carolyn
August 2, 2006 at 7:07 pm
Thanks for your post Carolyn,
I will give your solution a go, you are right it does give me more control over the data needed.
Regards Ian
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply