Take a look at DTS in SQL Server. It's fairly easy to get started with it using Enterprise Manager. You could create a new package and create two datasources, one being your oracle database and the other as SQL Server, then create a Data Transformation Task between them where you could either select tables of data to move or by query. Then you can massage the data into your SQL Server tables. Once you have your package done you can schedule it as a job using the SQL Server agent. Two things of note, first thing is you are using ODBC drivers instead of the native drivers to connect to Oracle which might cause problems (not totally sure, just might be slower) and I have heard if your tables are fairly large the jobs tend to take a long time. I have created some jobs that dump data from our mainframes to SQL Server using DTS and it has worked out nicely.
----------------------------------------------- Kevin Kazmierczak, CCNA Certified Advanced ColdFusion MX Developer Alfred University Alumni Hall 607-871-2334 [EMAIL PROTECTED] -----Original Message----- From: Ciliotta, Mario [mailto:[EMAIL PROTECTED] Sent: Thursday, March 20, 2003 6:44 PM To: CF-Talk Subject: Help with import of data from Oracle to SQL Server - SOT - I know Hi can anyone help me with a question. I need to export data from my Oracle 9 database and import it into my SQL Server database. The Oracle database is setup as a datawarehouse in theory and I need to provide a feed and the code to import the required data into SQLServer. I could run a CF scheduled job to query Oracle and then load the MS SQL tables every night but I really do not want to do this and put the extra load on my CF servers. I would prefer if there is a why to do it thru SQL Server - maybe DTS, BCP or a pass thru query - I am not sure. This would also need to schedule to run nightly so the new data is pick I am not sure exactly what DTC or BCP is, but I will be reading up on it, but if anyone has any quick info that I can read that would be great. Sorry for the non-CF question, but it is sort of CF since CF will be reading the data later. Thanks Mario This message is for the named person's use only. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. CREDIT SUISSE GROUP and each legal entity in the CREDIT SUISSE FIRST BOSTON or CREDIT SUISSE ASSET MANAGEMENT business units of CREDIT SUISSE FIRST BOSTON reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal written confirmation. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
