Oracle Data to MS SQL Server 2005
-
Guys,
I need help in importing data from Oracle database to MS SQL Server 2005. Do any one know of the best way to go about this task? Is there any tool I can use to accomplish thsi task?
-
You need to install the Oracle client for whatever version of Oracle you're using and set up linked servers via this client. Once you install the client, you can use SQLPlus from the cmd line to verify connectivity, then set up the linked servers using the information you put in the tnsNames file (Oracle connection file) and use Openquery in SQL Server to pull the data into SQL. It's a complicated process, but I was lucky enough to have some Oracle DBA's on the other side of the wire helping with the initial connectivity.
-
- g8r65 provided the way to connect it.
- In case you want to migrate from oracle to sqlserver
MS provides a migration assistant (Microsoft SQL Server Migration Assistant (SSMA) for Oracle)Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I agree with newbie on the Enterprise Manager linked server to oracle. I have used OLEDB MSORA, but sometimes use Oracle OLE. Once you have the link connected, using Query Analyzer with
select *
into PickATableName
from openquery(LinkedOracleName, 'Select * From Account where Debt = ''Y'' ')
Just a heads, up make sure the text inside your single quotes has two ticks '' instead of the double quote" around text items.
-
Fellow SQL-ers,
I find the Syntax examples in BOL lacking a little. Here are some notes I have on connection to Oracle from one of our SQL Server instances:
Step 1: (SQL Server Side) Create a Linked Server.
Step 2: (SQL Server Side) Create a Linked Server Login. Uses an Oracle User and Password, communicating through TNSNAMES.
Step 3: (SQL Server Side) Use OpenQuery with Oracle SQL syntax.
-- Step 1:
-- Adding linked server (from SQL Server 2000 Books Online):
/* sp_addlinkedserver [@server =] 'server'
[, [@srvproduct =] 'product_name']
[, [@provider =] 'provider_name']
[, [@datasrc =] 'data_source']
[, [@location =] 'location']
[, [@provstr =] 'provider_string']
[, [@catalog =] 'catalog']
*/
-- Add a Linked Server 'Ora817Link', connecting to an Oracle 8.1.7.x.x database.
EXEC sp_addlinkedserver 'Ora817Link', 'Oracle', 'MSDAORA', 'oracle817'
Step 2:
-- Adding linked server login:
/* sp_addlinkedsrvlogin [@rmtsrvname =] 'rmtsrvname'
[,[@useself =] 'useself']
[,[@locallogin =] 'locallogin']
[,[@rmtuser =] 'rmtuser']
[,[@rmtpassword =] 'rmtpassword']
*/
-- Add Linked Server Login for 'Ora817Link', using the Scott Oracle User.
EXEC sp_addlinkedsrvlogin 'Ora817Link', 'FALSE',NULL, 'scott', 'tiger'
-- Step 3:
-- Test connectivity and basic rights/permissions.
SELECT * FROM OPENQUERY
(Ora817Link,'SELECT sysdate FROM DUAL')
GO
Results from Query:
SYSDATE
-------
2007-09-06 13:59:33.000
-- Help on the linked server:
-- System Stored Procedures to help troubleshoot Linked Servers.
EXEC sp_linkedservers
EXEC sp_helpserver
select * from sysservers
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply