I am doing a test on migrating an Oracle 8i database to
SQL 2000. I've never done this before and I would like
to find out if there are any complications or side effects
if I try doing the transfer of data using DTS?
If not DTS then what would you suggest I transfer data
with?
Thank you
Hi,
I have been asked to look at optimising a Data Migration Strategy for one of our clients. I am quite new to Data Migration (I'm actually a system designer!).
The migration is from a legacy Oracle 7.16 Db to a SQL Server 2000 environment.
The legacy Db has some large tables - 30 Million+ rows
Table/column Mapping is 1:1
At present we are using DTS to import into the SQL environment.
My question is: Is there any way to leverage the Oracle 7.16 export to speed up the migration process?
Any advice would be appreciated.
Many thanks
Mike
Hello there
I have a customer that wants to start using SQL Server 7.0 instead of Oracle, but they have a lot of Oracle applications (made in Developer 2000 and Forms). Due to the big effort needed to convert those applications (to Visual Basic), they want to be able somehow to migrate only data from Oracle to SQL Server, and then use some tool that will allow them to use the same applications with the SQL Server 7.0 engine.
I found on the "SQL Server Developer's Kit" a document describing the process of migrating data and applications from Oracle to SQL Server. There is a mention to an Oracle tool called "Oracle Gateway to SQL Server" that will translate calls received by the Oracle engine to the SQL Server engine, but this approach will still require the existence of Oracle servers (which is the thing the customer no longer wants).
The second option mentioned on that document, and the one that seems more attractive, is the use of Oracle "Open Client Adapter" (OCA) that will allow Developer 2000 applications to communicate directly to SQL Server via ODBC. However, I have been unable to find (on the Oracle site) any documentation regarding this tool.
Has anynoe heard or used such tool (OCA) ? Any comments, tips, drawbacks, experience you could share ? Any other idea on how a thing like that could be accomplished ? Perhaps a third-party application that can act as a gateway between the Forms app and ODBC/SQL Server, or that will be able to generate VB code from a Developer 2000/Forms app...
Any help will be appreciated.
Please respond directly to me, as I am not a member of the distribution list.
Luis Francisco Silva
we recently got a scenario that we need to get the data from oracle tables which is installed on third party servers. we have sqlserver installed on ourservers. so they have created a DBLINK in oracle server to our sqlserver and published the DBLINK name.
what are the next steps that i need to follow on my sqlserver in order to access the oracle tables ?
I need to send data from a MS SQL 2000 DB in one location down to a Oracle DB (Solaris) in another location. I was wondering how(if at all possible) how to do this.
View 1 Replies View RelatedHi,
I used DTS Export Wizard to copy tables from SQL server 2000 to Oracle 8.1.7. After transfering completed successfully, I loged into oracle and tried to select data from the tables. I got 'table does not exist' error. But when I select table_name from oracle data dictionary, I saw tables exist in oracle. Then I opened SQL Query Analyzer, I can select data from the tables just transfered through linked oracle server.
Does anyone have same experience? Could someone please give me suggestion?
Thanks in advance!
Hi guys,
I was given a task to get an Oracle .dmp file into SQL Server.
Is it even possible to do? Or do I have to script the Oracle DB and import the DB that way?
But then how would I get the data?
I need to get the Oracle structure and the Data over to SQL Server.
PLease help.
Is it possible to replicate from Oracle to SQL Server.
Thanks,
Bianca
Hi
I am trying to migrate my database in Oracle 8i to Sql Server 2000 but am unsure of how to carry out the migration. How do I do it and what do I have to consider. My OS is Windows 2003. Thanks in advance
Dear All,
I am working on a project to migrate a .net desktop application to web based application using ASP.NET 2.0.
The present app has lots of tables with data in sql server 2000. The new app is to have Oracle 10.2 G as data store.
Although the data store is being changed most of the tables in the present database will remain same along with the data they hold.
My problem is:
1) how to transfer the tables to the new datastore 10.2 G in an automated way?
After all it's not possible to create each table afresh in Oracle and then insert records into each table one by one. If that is done it will take atleast few months if not years.
2) can I export the tables to a xml file and then import it from Oracle.How to do that?
It has become a nightmare for me.
Pls suggest the easiest way and which takes the minimum time.
A solution to the problem will be gratefully accepted.
Thanks.
We are running SQL Server 2000 SP3. We have linked servers in use thatwe use to access Oracle tables.Recently the claim has been made that you can access Oracle tables fromwithin SQL Server without using a Linked Server. I searched BooksOnline using keywords: linked, remote, and Oracle and did not findanything.A search of the newsgroup archives found only entries related to usingLinked Servers.Is there any such method as claimed? I do not think there is, but Ineed to find some support for my position or else learn something new.Thank you-- Mark D Powell --
View 4 Replies View RelatedHi Gurus,I have table (CallByCall) with following Data.Input :CallID Event DNIS UserID Time SeqID MobNo-----------------------------------------------------------------------------1 | 43 | 100 | |09:00:05 | 1 |98332107701 | 40 | | |09:01:05 | 2|1 | 41 | | |09:02:05 | 3|1 | 42 | | 25 |09:03:05 | 4|2 | 43 | 200| |09:10:05 | 1 |98339755362 | 40 | | |09:11:05 | 2|2 | 41 | | |09:12:05 | 3|2 | 42 | | 26 |09:13:05 | 4|3 | 43 | 300| |09:15:05 | 1 |98139785363 | 40 | | |09:15:55 | 2|3 | 41 | | |09:16:05 | 3|3 | 42 | | 28 |09:18:05 | 4|4 | 43 | 100| |09:45:05 | 1 |92239788964 | 40 | | |09:46:05 | 2|4 | 41 | | |09:47:05 | 3|4 | 42 | | 26 |09:48:05 | 4|Output:CallID DNIS UserID MinTime MaxTime MobNo--------------------------------------------------------------------------1 | 100 | 25 |09:00:05 |09:03:05 |98332107702 | 200 | 26 |09:10:05 |09:13:05 |98339755363 | 300 | 28 |09:15:05 |09:18:05 |98139785364 | 100 | 26 |09:45:05 |09:48:05 |9223978896In short I am getting DNIS and MobNo for Event 43 andUserId for Event 42.How can I achieve the above result?Is it possible with scalar query?Could any one help me for the same?Thanking in AdvanceSanjeev
View 1 Replies View RelatedHi guys,
I figure this should not be a complex one. I know how to manually pull in data from Oracle 9i into SQL Server 2000 using DTS. However this is my issue.....
I simply want to automate the pulling in of data from 1 table in my ORACLE 9i database into another table in my Sql Server 200. I was hoping I could simple write a stored procedure that would sort of utilize a dblink like in ORACLE and then schedule that procedure. Is this feasible in Sql Server, and how would one go about setting this automated import up????
Thanks in Advance all............
'Wale
Hi,
I do not know if it can be done:
how to import an Oracle dump file into SQL server ?
That dump file contains the content of a table, excported from Oracle.
Is there a way or another to import it to SQLServer 2000 ?
Thanks
How can we do pair wise comaprission using Sub query as generally can be done in Oracle?
Thanx
Greetings,
I want to connet to Oracle databse from SQL server 2000 since I am more comfortable with SQL 2000 client package. The oracle server is 8i or above.
I am running a SQL Server 2000 on a PC with Windows XP OS.
Please help what I need to do and configuration information.
Thank you all in advance.
BPG
Hello,
I deaply need to know how to execute procedures from package in oracle, from sqlserver 2000 using linkserver.
Thank you very much,
Victor
DBA
We have a linked server, which worked fine, but we have just changedthe collation sequence, in SQL-Server, fromSQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS.Now trying to use the Linked server, we get the following message:"Error 7399: OLE DB Provider 'MSDAORA' reported an error. The providerdid not give any information about the error. OLE DB error trace [OLEDB provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005:The provider did not give any information about the error]."Ha anyone seen this before, because I am not sure why changing thecollation sequence, would affect the Linked Server
View 3 Replies View RelatedDoes anyone know how to import data from an Oracle view (on Unix machine) to the tables in NT/SQL server 7.0? At least point me to the right docoment resource, if available. Thanks a lot.
wendy
Hi,
Which is the best way to transfer data from SQL server into Oracle?.
Thanks in Advance,
Ravi
I have an ORACLE server say "A". I have a SQL Server named "B". In SQL Server I have a table named "test" who contains only one field named "aa" which is "BIT" type field. I want to query this table of "B" from "A" interface through Oracle's DB Link and ODBC dsn for Sql Server. But it gives error. If I changed the data type of field "aa" to "int", then it reads easily. Please suggest.
View 7 Replies View RelatedHi,
I created SQL Server package and schedule the job. SQL Server allows us to connected different database for e.g. for oracle using "Oracle Provide for OLD DB" to retrieve our database. A link is create between this database which move the data to SQL Server. I'm sure there is no issues in the link, as I'm using it to retrieve several oracle database which contains both Arabic and English data.
But after Importation it is showing Junk values. Please advise me what step I should take next.
Regards
Azeem
Hello friends,
I am working for a project. At my college I used to work with oracle. Now as requirement changes I have to change my database to SQL Server.
Is there any simple way one can suggest me?
We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "".
Msg 7346, Level 16, State 2, Line 1
Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".
Hi,
I am using Windows 2003 server and Sqlserver 2005 by the use of Linked server , I made a connection to Oracle 10g after that I am importing records from Oracle to sqlserver 2005. When I made tnsnames.ora in sql machine , it worked fine but when i am using tnsnames file from oracle server then i fiired importing procedure it returns below maintain error :
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Unspecified error".
OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS" returned message "Oracle error occurred, but error message could not be retrieved from Oracle.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDAORA" for linked server "BI_ORACLE_LS". The provider supports the interface, but returns a failure code when it is used.
Please let me know.
Thanks
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?
When I tried to import system.data.oracleclient, it was not available. Do I need to install anything for being able to use it ?
Thanks a lot.
I have a coworker who needs to extract data from Oracle into SQL Server 6.5 and Sybase as part of an interface that runs daily. How is the best way to do this?
Tony
Hi All,
We are extracting the data from ORACLE databse into SQL Server 2005 database. Both are hosted on different servers.
For this we have created linked server on SQL Server and we using the OPENQUERY to extract the data. Its taking too much time to extract the data.
Is there any other alternate thing for OPENQUERY clause.
Please help me on this.
Thanks in advance.
Thanks,
Ramesh.
Hi to every one,
i have the export data from oracle to sql server.
how can i do it.
i tried through the wizard i am able to improt from oracle to sql server. but constratins are defined in oracle table are not comming in slq server.
i want to transfer data from oracle to sql(like tables,other database objects from oracle to sql).
how can i do it. is there any procedure .
could any one send the process.
regards,
kumar
pavan
Associate Oracle DBA
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?Thanks a lot.
View 3 Replies View RelatedHI,
I need some help regarding the data access.
I needs to access some data from tables which are in oracle and load it into sql server tables.
Please let me know the process for this.
Thanks in advance.
Ok, we have built a data mart using SSIS etc...for transformations and loading.
Our biggest single problem we have currently is loading data from an Oracle server to our SQL server. Some tables from oracle run fine when retrieving the data but there is one particular table that just doesn't load fast enough (9 million records take over 12 hours). It seems that we are idling alot and its not always running.
Can anyone help with this problem?
I am getting this error when connecting to Oracle db. I tried using Microsoft OLEDB provider for Oracle it give me error and tells me the error could not be retrieved from Oracle. When I try the Native OLDDB provider for Oracle I get
Warning at {0F67F2FA-E3F8-4F44-93EC-47D513A34FD4} [Orcale Database WPHP2 [1]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
Error at Copy DSS_SJV_Volume_History [DTS.Pipeline]: The "output column "COMP_UNIQUE_ID" (2007)" has a precision that is not valid. The precision must be between 1 and 38.
What do I do !!!!!!!!!!
Thanks
Shabnam