RakeshBhandari
asked on
copying tables from Oracle 10g to MS SQL 2005
i wish to do the following:
copy 2 tables (and its data) from an oracle server on machine A to MS SQL server 2005 residing on machine B
this needs to be done using an application whenever the user wants... meaning, an application residing on an client's machine will trigger this copy
please guide
copy 2 tables (and its data) from an oracle server on machine A to MS SQL server 2005 residing on machine B
this needs to be done using an application whenever the user wants... meaning, an application residing on an client's machine will trigger this copy
please guide
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For simplicity and to setup script to rerun this automatically on demand. I would go with following steps.
Method 1.
------------
1. Oracle side = Using SQLPLUS query the tables and spool the data as comma delimited file.
2. SQL server side = Using bcp utility load the data into SQL server tables.
or
Method 2.
------------
1. Oracle Side = Using SQLPLUS spool the data with "insert into table ..." string along with data.
2. SQL server side = Run the above generated file from ISQL and insert data directly into the SQL server.
Method 1.
------------
1. Oracle side = Using SQLPLUS query the tables and spool the data as comma delimited file.
2. SQL server side = Using bcp utility load the data into SQL server tables.
or
Method 2.
------------
1. Oracle Side = Using SQLPLUS spool the data with "insert into table ..." string along with data.
2. SQL server side = Run the above generated file from ISQL and insert data directly into the SQL server.
ASKER
@AmmarR
thank you very much for the information
i, however, still have to find oracle client 11 on the oracle's site... can you please help me with a link?
thank you very much for the information
i, however, still have to find oracle client 11 on the oracle's site... can you please help me with a link?
Dear RakeshBhandari
i have it and its about 500 mb, if you have an FTP site, i will upload to you,
here is links if you need direct download
http://www.easysoft.com/support/kb/kb00612.html
http://www.mandsconsulting.com/oracle-client-download-11g-windows-client-32-bit-and-x64
you will see in the page they are pointing to Oracle website.
Regards
i have it and its about 500 mb, if you have an FTP site, i will upload to you,
here is links if you need direct download
http://www.easysoft.com/support/kb/kb00612.html
http://www.mandsconsulting.com/oracle-client-download-11g-windows-client-32-bit-and-x64
you will see in the page they are pointing to Oracle website.
Regards
ASKER
i managed to get this : 10201_client_win32.zip ... it is 453mb in size
its link was present in your doc
i think this should suffice?
its link was present in your doc
i think this should suffice?
ASKER
yes... i have the similar screen... but with oracle 10g [not 11g]
when you say "Use Oracle Net Manager to configure the connection to the oracle server" what does it mean?
i already have the default TNS listener configured
when you say "Use Oracle Net Manager to configure the connection to the oracle server" what does it mean?
i already have the default TNS listener configured
ASKER
also... what is this SSMA thing? is it any good/better than what you are teaching me?
10g should work fine.
oracle net manager actually configures you tnsnames.ora file.
so if you can manually configure the file, then why bother with net manager,
just make sure the entries in the tnsnames.ora file are formatted properly like below
and it should work fine
-------------------------- ----------
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\NETWORK\AD MIN\tnsnam es.ora
# Generated by Oracle configuration tools.
ServerName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.0.7)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ServiceName)
)
)
oracle net manager actually configures you tnsnames.ora file.
so if you can manually configure the file, then why bother with net manager,
just make sure the entries in the tnsnames.ora file are formatted properly like below
and it should work fine
--------------------------
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\NETWORK\AD
# Generated by Oracle configuration tools.
ServerName =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.0.7)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ServiceName)
)
)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As mentioned by sridharv9, i would prefer the simple and normal method of SSIS,
i tried SSMA before with sybase, and its wasn't as easy and as smooth as they claimed it would be, there are few challenges and pre-configurations that you need to setup before using it.
but if you want to read more about SSMA and try it your self here are useful links
SSMA for Oracle
http://www.microsoft.com/downloads/details.aspx?familyid=3E8722AE-77F3-4288-906D-F421E24D322F&displaylang=en
White papers
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c7933d3e-b7b9-43a6-ade3-f8e37c8cb046#tm
Blog
http://ssmablog.blogspot.com/2009/09/sql-server-2008-migration-whitepapers.html
Good luck
i tried SSMA before with sybase, and its wasn't as easy and as smooth as they claimed it would be, there are few challenges and pre-configurations that you need to setup before using it.
but if you want to read more about SSMA and try it your self here are useful links
SSMA for Oracle
http://www.microsoft.com/downloads/details.aspx?familyid=3E8722AE-77F3-4288-906D-F421E24D322F&displaylang=en
White papers
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=c7933d3e-b7b9-43a6-ade3-f8e37c8cb046#tm
Blog
http://ssmablog.blogspot.com/2009/09/sql-server-2008-migration-whitepapers.html
Good luck
ASKER
absolutely wonderful support!
thank you very much to all the experts, esp. AmmarR!
thank you very much to all the experts, esp. AmmarR!
ASKER
thank you!
your most welcome
http://www.databasejournal.com/features/mssql/article.php/3864246/SSIS-Packages---the-Simplest-Way-to-Create-Them.htm