Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Getting data from Oracle database into SQL Server database tables

Posted on 2014-01-24
12
Medium Priority
?
1,594 Views
Last Modified: 2014-02-05
What is the best way to get the some data out of an oracle database on UNIX server into the SQL Server database table on Windows server?

Would exporting data into an Excel file via SQL Developer and them importing it into a SQL Serve rdatabase be the best way?
0
Comment
Question by:YZlat
[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
12 Comments
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1400 total points
ID: 39807802
Hi,

If this is a regular occurrence, or maybe on demand, install the appropriate oracle client on your SQL Server. Note the differences between 32-bit clients and 64-bit clients.

On SQL create a linked server to oracle.

Now across that linked server, using openquery, retrieve the data.

Store results in a temp table etc as needed for further processing/linking to SQL.

HTH
  David

PS I've built a solution around this that is in phase 1 for one of my clients accounts. We are developing a proposal for phase 2 which is for all their accounts.
0
 
LVL 13

Expert Comment

by:magarity
ID: 39807944
SQL Server's SSIS can move data from Oracle to SS also. Bigger companies often have security guidelines that make linked servers a hassle and so SSIS is an easy way around that kind of thing.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39822084
David Todd, any tips on how to create linked server between SQL Server database and Oracle db on UNIX?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 35

Accepted Solution

by:
David Todd earned 1400 total points
ID: 39822131
Hi,

On the SQL Server, install the appropriate oracle client. If SQL is 64bit then install a 64bit client.

You'll need to configure Oracle and the TNSnames file. Check that you can connect to Oracle!

From SQL then create the linked server. Mine looks like this:
EXEC master.dbo.sp_addlinkedserver @server = N'EXSTOCK', @srvproduct=N'OraOLEDB', @provider=N'OraOLEDB.Oracle', @datasrc=N'PROD'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'EXSTOCK',@useself=N'False',@locallogin=NULL,@rmtuser=N'****',@rmtpassword='########'

The if you'll pardon the dynamic SQL, the code looks like this:
                  declare @sqlTemplate2 varchar( max )
                  set @sqlTemplate2 = 'insert #r( SnapshotDate ) select MaxReportDate
                  from openquery( EXSTOCK, ''select max( ReportDate ) as MaxReportDate from BAW_BILL_SKUXLOC_DAILY where ReportDate <= ''''FROMDATE'''' '' )'
                  
                  declare @sql varchar( max )
                  set @sql = replace( replace( @sqlTemplate2, 'FROMDATE', convert( varchar( 10 ), @d, 120 )), 'TODATE', convert( varchar( 10 ), dateadd( day, 1, @d ), 120 ))
                  
                  --insert #r
                  execute( @sql )

HTH
  David
0
 
LVL 35

Author Comment

by:YZlat
ID: 39825452
David, in the line where you are creating linked server:

EXEC master.dbo.sp_addlinkedserver @server = N'EXSTOCK', @srvproduct=N'OraOLEDB', @provider=N'OraOLEDB.Oracle', @datasrc=N'PROD'


@datasrc parameter is the name of the database or is it the tns entry name from tnsnames.ora file?

and also is @srvproduct value OraOLEDB an actual value or a sample value?
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1400 total points
ID: 39825585
Hi

@server = N'EXSTOCK'
EXSTOCK is what I refer to in the query. This is the name of the linked server

@srvproduct=N'OraOLEDB'
This is the odbc name of the oracle client that we used.

@datasrc=N'PROD'
This is the entry in tnsnames.ora.

HTH
  David
0
 
LVL 35

Author Comment

by:YZlat
ID: 39829964
David, if I decide to create an SSIS package to transfer the data on regular basis, do I still need to create a linked server separately, or will it be created in the process of SSIS creation?

I also noticed that OraOLEDB.Oracle provider is not installed on the SQL Sever. What do I need to install there in order to have that provider? Oracle client 64-bit is already installed
0
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
ID: 39830070
This is an oracle drivers it can be downloaded from oracle.

http://www.oracle.com/technetwork/database/windows/utilsoft-088126.html
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1400 total points
ID: 39830232
Hi,

For an SSIS package, that connects directly to the appropriate data source, and generally doesn't need the linked server setup mentioned above.

Kind Regards
  David
0
 
LVL 35

Author Comment

by:YZlat
ID: 39830474
Thanks David.

Would you be so kind to also help me on this question?

http://www.experts-exchange.com/Database/Oracle/Q_28355135.html

There I am trying to figure out how to go about conecting to Oracle db on AIX operating system.
0
 
LVL 35

Author Comment

by:YZlat
ID: 39830476
Praveen, the link you posted seems to only contain older Oracle versions, up to 10g, and I am dealing with 11g.
0
 
LVL 35

Author Closing Comment

by:YZlat
ID: 39830542
Thank you!
0

Featured Post

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.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

721 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