August 1, 2006 at 1:49 pm
Hi all,
How can i set up replication from SQL to Oracle!!!
also if possible to schedule DTS that will transfer data from SQL 2000 to Oracle!!
Thanks
August 2, 2006 at 7:23 am
SQLRep,
It should be possible for you to create a push subscription from your SQL Server 2000 server to an Oracle (ODBC) subscriber, this is referred to as Publishing to heterogeneous data sources, however my experience of this is just theoretical, I have never done it before. Check out BOL searching for 'Replication and Heterogeneous Data Sources'.
It is definitely possible to schedule a DTS package to transfer data from SQL 2000 to Oracle, your best bet is to create your DTS package using an 'Microsoft OLE DB Provider for SQL Server' connection for the SQL Server and a 'Microsoft OLE DB Provider for Oracle' connection for Oracle. All you need to do then is setup any transformations, ActiveX scripts, T-SQL batches etc. Then simply schedule the DTS package from Enterprise Manager or if you want more flexibility with the control of the DTS package, i.e. passing in Variables, Multi-step jobs etc add a DTSRUN command to a new SQL Server Agent Job that executes the DTS package.
Hope this helps,
ll
August 2, 2006 at 12:11 pm
SQLRep,
I have setup replication from SQL Server to Oracle. First install the oracle client SQLNet software on the SQL Server machine. Then configure the oracle database as a subscriber. Create the publication and push it to Oracle. As a side note, In my case I later changed the publication to use Transform Data Task using a DTS package, because without that the transactions were sent without using bind variables and it caused excessive shared memory usage on the oracle database. To change it I created a linked server for the oracle database on SQL Server then created a new publication with advanced options and checked Transform Data Task. After the publication is created right click on it and select Define Transformation of Published Data which will guide you through creating the DTS package to transform the data using ActiveX scripts. HTH,
Michelle
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply