April 28, 2008 at 11:01 pm
Has anybody transfered data from Oracle to SQL before?
How do we do it? Can it be scripted?
I have tried OPENROWSET, where I couldn't find the exact syntax, and also OPENQUERY, wherein I couldn't setup a linked server.
Your help will be appreciated.....
Please don't provide bookish knowledge.....only those reply who have really worked on it.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 29, 2008 at 7:54 am
It's been a while - about five years - but I used DTS to copy data from Oracle to SQL Server. If you're using SQL 2005, you'll want to use SSIS. You'll need to have the Oracle driver (Oranet?) on the machine you're running SSIS on when you set up the linked server.
Microsoft has a free downloadable migration assistant: http://www.microsoft.com/sql/solutions/migration/oracle/default.mspx
Greg
April 29, 2008 at 8:35 am
SELECT *
FROM OPENQUERY (LS_ORACLE,'select * from oracletable')
Here LS_ORACLE is a linked server which has provider as "Oracle Provider for OLE DB" and datasource is system datasoruce which points to oracle server.
Hope this helps.
April 29, 2008 at 9:10 am
OPENQUERY or SSIS, but move the data.
Don't leave it in that old, out-dated Oracle system 😉
Is this regularly or one-time? Does the data move ad-hoc? SSIS is quick and works well, but it's more time to setup. If you do it regularly and can define what moves, I'd use that. If this is one-time or ad-hoc, then a linked server or the OPENQUERY/ROWSET works well.
April 30, 2008 at 9:57 am
Don't forget about the SQL Server Migration Assistant for Oracle (SSMA for Oracle)
http://www.microsoft.com/sql/solutions/migration/oracle/default.mspx
It works well to transfer data and stored procedures ...
May 1, 2008 at 7:29 am
I am not sure which version of SQL Server you are using, bu in SQL Server 2000 I created a DTS package and transferred the data over. If I need to perform data manipulation I created a staging table in SQL Server and did the manipulation. You could do it on the ORACLE side if your expertise is in Oracle.
In SQL SERVER 2005 SSIS packages have worked great and the data manipulation can be done in the package.
If you like .net...
I have it to pull data out and manipulate it and then insert into SQLSERVER. Of course this will work on both versions of SQL Server. I think this was the best way when heavy manipulations were needed. In SQL Server 2005 we can use CLR to incorporate the .net into the RDBMS. In order to do this the CLR Integration has to be enabled.
Q
Please take a number. Now serving emergency 1,203,894
November 24, 2008 at 7:53 am
Is there a tool that will data sync / migrate data from Oracle 10g to SQL Server 2008 Express??
February 18, 2009 at 2:04 am
hello all..
i am just doing a study about data migration .i am interested to know about
migrating from oracle to sql.
what are pitfalls to look out for?
as in what are the challenges?
The methodology?
etc
Can anyone please lead me as where i will find information regarding the same?
Thank you
February 18, 2009 at 2:23 am
archan.arch (2/18/2009)
hello all..i am just doing a study about data migration .i am interested to know about
migrating from oracle to sql.
what are pitfalls to look out for?
as in what are the challenges?
The methodology?
etc
Can anyone please lead me as where i will find information regarding the same?
Thank you
You may want to start by taking a look at SQL Server Migration Assistant for Oracle (SSMA for Oracle) here http://www.microsoft.com/sqlserver/2005/en/us/migration-oracle.aspx then search this forum -tons of information about migration.
Just a heads-up, migrating data is not going to be your problem, your problem would be the code which in practical terms in unmigratable -you will end up rewritting most of it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 18, 2009 at 8:35 am
I'm thinking that SQL Server 2008 Express will have some limitations. The documentation for SS2005 is great if you're using that tool. I need to migrate data from Oracle 10g to laptops running SS2008EX. This will be a weekly migration.
Can this be done with the version of SS or do we need to go Oracle Lite? Looking for a client db with a small footprint. Perferably opensource.
Thank you.
Chip
February 18, 2009 at 11:25 am
hello paul,
Thank you for the lead.I did went through the forum and the link.
Also i would like to know if there is something similar for SSIS?
And what are the other options?
Thank you Again
February 19, 2009 at 8:11 am
archan.arch (2/18/2009)
hello all..i am just doing a study about data migration .i am interested to know about
migrating from oracle to sql.
what are pitfalls to look out for?
as in what are the challenges?
The methodology?
etc
Can anyone please lead me as where i will find information regarding the same?
Thank you
The biggest pain for data is the mapping of types. Particularly numbers. Oracle has a generic number type, which can look like (and be used as) all the number types MSSQL uses. The key is which types the driver returns. I've been out of the driver biz for a few years (worked for an ODBC/JDBC/ADO.NET driver vendor), but many of the drivers had idiosyncrasies in what types they reported to the client app. I remember an Oracle ODBC driver (The MS one I think, don't quote me) that guessed the type of numbers from a sample brought back, so if it got some with a decimal place, it reported float, only small int, tinyint, etc.
Since I haven't been keeping up, can't recommend a method/driver.
April 6, 2009 at 2:39 pm
Was wondering if SYS & SYSTEM too need to be migrated since both these schemas hold Oracle's internal tables/ data dictionary definitions etc. Is it imperative to migrate these users as well in SQL server ?
Thanks
Sanji
April 7, 2009 at 7:14 am
matlo_talli (4/6/2009)
Was wondering if SYS & SYSTEM too need to be migrated since both these schemas hold Oracle's internal tables/ data dictionary definitions etc. Is it imperative to migrate these users as well in SQL server ?Thanks
Sanji
I wouldn't even attempt to "migrate" them. You'll need to extract the meaning and context of the users and rebuild them on SQL Server. I believe you could get a list of users, but that's about it. Remember that Oracles users are used differently than in SQL Server. For shorthand: Oracle to SQL server
Database = Instance
Owner = Database
Oracle uses a 3 part name for data references Server.Schema.Table, SQL Server uses Server.Catalog.Schema.Table.
Oracle collapses Catalog and Schema together, which will likely cause some ambiguity you'll need to deal with.
April 7, 2009 at 7:55 am
I just used SSMA to migrate an Oracle 9.2.0.3 schema to SQL 2005 a few weeks ago. It worked fantastic.
You are correct about the users. I just recreated on the SS side. Now our developers are happy as clams rewriting the old PowerBuilder app front end as a web app in .NET.
Mike
“I know that you believe you understand what you think I said, but I'm not sure you realize that what you heard is not what I meant.”...Robert McCloskey
___________________________________________________________________
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply