• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 758
  • Last Modified:

How can I export data from Microsoft Sql Server 2008 R2 directly into an Oracle 10g Database

How can I  export data from Microsoft Sql Server 2008 R2 directly into an Oracle 10g Database
0
dpdmembers
Asked:
dpdmembers
2 Solutions
 
dj_alikCommented:
0
 
OP_ZaharinCommented:
- i usually use "Export Data" utility on SQL Server management studio which is easy to use. this steps is in 2005 (we have yet to upgrade to 2008) but it is almost similar :

: in the Object Explorer treeview, select the source schema/database
: right click > choose "Task" then Export data
: select your schema as the data sourceĀ 
: select "Oracle OLE provider" as the destination
: click on "Properties" button and fill-up the necessary details on data source, username and password. click on remember password. (if the database is on a 2 different machine, you need to have Oracle client installed and setup on the SQL Server machine)
: then choose either to write a specific query or to copy selective table. you need to click on Mapping button to change certain datatype to match Oracle datatype.
: then choose to execute immediately

- Export tool in SQL Server 2008:
http://msdn.microsoft.com/en-us/library/ms140052.aspx
0
 
Alpesh PatelAssistant ConsultantCommented:
Using the Import Export Wizard you can do it directly. But make sure Oracle clinet is installed at SQL SErver System.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
dpdmembersAuthor Commented:
I have tried all the Import/Export solution but the error comes about with the Oracle OLE provider as the destination.  The Oracle client is installed and setup on the SQL Server machine.
0
 
OP_ZaharinCommented:
- Oracle client is installed and have you verified that the connection to the Oracle database is working?
- do you have tnsnames.ora file in the oracle client folder? (copy this file from the Oracle server location commonly found in C:\oracle\product\10.2.0\db_1\network\ADMIN\). create a folder call TNS_ADMIN in the the same folder of the Oracle client. and copy the tnsnames.ora file into that folder. the content of the file would look like below. put 'ORADB' as the data source name in the "Oracle OLE provider" dialogbox.

ORADB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = OraServer)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


- next you need to add the following to the environment variables (in My Computer>Properties>Advanced System Properties):
PATH=c:\Oracle_InstantClient;%PATH%
TNS_ADMIN=c:\Oracle_InstantClient\TNS_ADMIN
SQLPATH=c:\Oracle_InstantClient
LD_LIBRARY_PATH=c:\Oracle_InstantClient
0
 
dpdmembersAuthor Commented:
Did that and now getting the error below:

OCIEnvCreate failed with return code -1 but error message text was not available
0
 
OP_ZaharinCommented:
- this might be related to permission rights now.
- this is from memory: go to Oracle client folder, right click select > Properties, in security tab, add SQL Server user (i can't remember the exact user group) and make sure that the permission to Read, Execute is checked and apply to this folder, subfolders and files.
0
 
dpdmembersAuthor Commented:
I did the permission solution already and it did not work
0
 
OP_ZaharinCommented:
- what is your version of Oracle database server? did you download the right version and OS version (32/64bit)? see if you are following the steps in the below documentation. yes you are not using odbc for the export utility, but this is one way to make sure that the server can connect to Oracle server:

http://www.oracle.com/technetwork/database/features/oci/odbc-ic-releasenotes-094306.html 
0
 
dpdmembersAuthor Commented:
I install instantclient and i got it to work. Thanks
0
 
OP_ZaharinCommented:
i believe the link i gave you is the instruction to install instant client which solve the issue.
0
 
OP_ZaharinCommented:
- i'm recommending to accept answer on http:#35730496 as it pointed the asker to install instant client which solved this question as he mention in http:#35732399
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now