Solved

How can I import Oracle 10g data into a sql2008r2 db daily.  Linked server crashes. SSMA is unstable and tedious

Posted on 2015-02-12
6
94 Views
Last Modified: 2015-02-23
I have to import a 20gig 10g database into a sql server database on a weekly basis.  My linked server & sql script.  The SSMigration Assistant is tedious and usually fails.  I've been old SSIS is very complex
0
Comment
Question by:donsql
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 10

Accepted Solution

by:
HuaMinChen earned 500 total points
ID: 40607090
Hi,
You can use Stored proc to transfer data through linked server and call the scheduled job to call Stored proc periodically.
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40607455
You can use the SQL Server Replication and configure the Oracle database as the Publisher and the SQL Server database as the Subscriber and then schedule the Snapshot job to run in a weekly basis.
0
 

Assisted Solution

by:donsql
donsql earned 0 total points
ID: 40608117
Very true.  but I need to import the entire 770 + table 20 gigs of data.  Replication isn't feasible
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40608130
Why not? You can set the Snapshot to run on a non-working period.
0
 
LVL 49

Expert Comment

by:Vitor Montalvão
ID: 40618340
You can use the Database Wizard feature in SSMS. Personally I don't like it but maybe fits in what you are looking for.
0
 

Author Closing Comment

by:donsql
ID: 40625353
I've used replication extensively.  It's not a viable solution for this many tables.  Stored procs aren't stable enough.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to keep a record with the highest value 3 39
How can I find this data? 3 25
scheduler notification 9 38
SQL 2012 Instance Problem 3 55
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question