Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1688
  • Last Modified:

Getting data from Oracle database into SQL Server database tables

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
YZlat
Asked:
YZlat
4 Solutions
 
David ToddSenior DBACommented:
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
 
magarityCommented:
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
 
YZlatAuthor Commented:
David Todd, any tips on how to create linked server between SQL Server database and Oracle db on UNIX?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
David ToddSenior DBACommented:
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
 
YZlatAuthor Commented:
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
 
David ToddSenior DBACommented:
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
 
YZlatAuthor Commented:
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
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
This is an oracle drivers it can be downloaded from oracle.

http://www.oracle.com/technetwork/database/windows/utilsoft-088126.html
0
 
David ToddSenior DBACommented:
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
 
YZlatAuthor Commented:
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
 
YZlatAuthor Commented:
Praveen, the link you posted seems to only contain older Oracle versions, up to 10g, and I am dealing with 11g.
0
 
YZlatAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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