Oracle to SQL

Last Post 16 Feb 2007 08:34 AM by sql-tips. 14 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sql-tips
New Member
New Member

--
22 Jan 2007 10:58 AM
Hi,
I need to copy data from Oracle to SQL Server on daily basis. I have one table with million records, first run need to copy all 1 million records, but next run onwards only the changes occured in oracle should be migrated to SQL Server. What is the best way to do it? Thanks for any help.
sql-tips
New Member
New Member

--
24 Jan 2007 08:45 AM
thanks russellb for the response.

We have almost 200 tables which needs to copy from Oracle to SQL. Some of the tables have date field and most of them won't. So, do you think DTS will be best option? How about creating a linked server. Someone suggesting BizTalk, but I never done that. Any suggestion will be helpful.
sql-tips
New Member
New Member

--
24 Jan 2007 10:16 AM
Some tables needs to be updated once in day, and some weekly and some tables monthly.
lmf232s
New Member
New Member

--
24 Jan 2007 10:18 AM
Well, can I ask what the reason is for storing an exact copy of the data in a different DB?

Here we have or ERP data in Oracle yet all our intranets/applications use sql server. If we ever need to get data that exists in Oracle then we just query oracle which is set up as a linked server in SQL server. Once the linked server is set up you can query the data through a SP or dynamic SQL.

I believe your going to have to create a linked server in order for SQL server to be able to talk to oracle.

Here is an idea/theory.

Oracle 9i + has a merge statement which basically allows you to say for each record, if it exists then do an update, else do an insert. We do this to refresh our data warehouse every morning and its initiated with a windows scheduled task.

With this being said i see no reason why you could not create a linked server from SQL server to oracle and create a SP to do exactly the same thing.

For each table you would have 1 SP. In this SP you would open the Oracle table and do exactly the same thing. For each record, if it exists then do an update, else do an insert.

The only draw back is that you’re going to have to touch each record in the table.
sql-tips
New Member
New Member

--
24 Jan 2007 10:40 AM
The reason to copy the data is eventually they want to get rid off Oracle.

But even with linked servers I get many errors, almost for each table. Although same query is running fine in Oracle but get error from SQL Server.

for example: OLE DB provider "MSDAORA" for linked server "test" returned message "ORA-01854: julian date must be between 1 and 5373484

Another Example: Error converting data type DBTYPE_DBTIMESTAMP to datetime
lmf232s
New Member
New Member

--
24 Jan 2007 10:59 AM
can you post the query
sql-tips
New Member
New Member

--
24 Jan 2007 11:04 AM

select * from test.."Scott"."TABLE1"
lmf232s
New Member
New Member

--
24 Jan 2007 11:17 AM
Were are you running this query from? Query Analyzer
sql-tips
New Member
New Member

--
24 Jan 2007 11:28 AM
Yes. SQL Server 2005 Query Analyzer
lmf232s
New Member
New Member

--
24 Jan 2007 01:34 PM
It might have something to do with your provider.

Did you use Microsoft's OLE DB Provider for Oracle or did you fill out the Data Source?
(In SQL Sever Enterprise Manager go to your linked server "test" for oracle. Right click/Properties.)

This might be an issue with using Microsoft's Provider. We dont use the MS provider, we filled in the Data source name. Dont quote me but i think the data source is going to be the TNS name. We have in the past had issues using MS provider in asp pages and with SQL server.

I cant remember if we had to do anything on the server or not but i think we had to. Ill let you know in the morning what we did. I need to talk to my Oracle guru in the morning. He's already gone for the day.
sql-tips
New Member
New Member

--
25 Jan 2007 08:29 AM
This is I used to create linked server.

EXEC sp_addlinkedserver
@server = 'TEST',
@srvproduct = 'ORACLE',
@provider = 'MSDAORA',
@datasrc = 'test'

EXEC sp_addlinkedsrvlogin 'test', 'false', NULL, 'testuser', 'testuser'

test is in the tnsnames. I didn't find any other provider for Oracle other than MSDAORA on the providers list.
sql-tips
New Member
New Member

--
25 Jan 2007 09:29 AM
It is working fine with this query.

SELECT *
FROM OPENQUERY(test, 'Select * from table1')

lmf232s
New Member
New Member

--
25 Jan 2007 11:43 AM
No i guess i had a brain fart yesterday. I do use the MS Provider for oracle.

as far as your queries go, all of my queries are like your last example

SELECT *
FROM OPENQUERY(test, '
Select *
from table1
')

I never execute a query like this (not that i have really tried but i do believe once you start passing in values through a SP that this method has issues. For me the above example works better and have never had a problem with it.)
select * from test.."Scott"."TABLE1"


lmf232s
New Member
New Member

--
31 Jan 2007 01:41 PM
Sql-tips,

I totally forget about this thread but i wanted to find out if you have found the light?

While executing a query against a linked sever works (as far as pulling the data) it does not solve your original question. As well by executing against the link server does not help in getting you away from Oracle.

But I believe by executing against the linked server you can then do the following:
Load the initial data copy via DTS (Oracle > SQL) and then create a SP (in SQL) that executes against the linked server (Oracle) to import any new data or update it if its changed from Oracle.

Any way just wanted to see if you were able to get this up and running.

Thats it
sql-tips
New Member
New Member

--
16 Feb 2007 08:34 AM
Thanks lmf232s for followup. I was working on other issues so far.

You are right, my original problem still doesn't solve.
You said, after initial load, I can create a SP againist linked server. To do this Do I need to loop through all records and compare with SQL to load?. Can you give sample procedure?
You are not authorized to post a reply.

Acceptable Use Policy