Import From Oracle Rdb Into SQL 2005

Dec 7, 2007



Hi All,

I'm trying to import from Oracle Rdb database into SQL server 2005 via ODBC without much success.

I've installed the correct Oracle Rdb driver and verified it all works ok by importing in to Access.

Using the import wizard within SQL mgt studio and the following connection strings I get a password error

Dsn=XRS;Driver={Oracle RDB Driver};sid=system

TITLE: SQL Server Import and Export Wizard
------------------------------
The operation could not be completed.
------------------------------
ADDITIONAL INFORMATION:
ERROR [HY000] [Oracle][ODBC][Rdb]%SQLSRV-F-GETACCINF, Oracle SQL/Services authorization failed
ERROR [HY000] [Oracle][ODBC][Rdb]%SQLSRV-F-GETACCINF, Oracle SQL/Services authorization failed
------------------------------
BUTTONS:
OK
------------------------------

I know the username/password combination to be correct.


Is SSIS the way to go. I've had a quick look at this and setup the ODBC connection but the column
mappings box reports the following...

error at data flow task [datareader source[1]; cannot aquire a managed connection
from the run-time connection manager.

Anyone point me towards any examples of reading in data via ODBC and storing in a SQL server database.

Should point out that the source is not Oracle RDBMS (7,8 or 9) but Oracle Rdb which is a
completely seperate product.

Thanks in advance

Dave







View 10 Replies


ADVERTISEMENT

Importing Data From Oracle 8i/9i To SQL Server 2005 Using SQL Server Import And Export Wizard (AKA DTS Wizard)

Oct 20, 2006

Hi All,

I have become frustrated and I am not finding the answers I expect.

Here's the gist, we support both Oracle and SQL for our product and we would like to migrate our Clients who are willing/requesting to go from Oracle to SQL. Seems easy enough.

So, I create a Database in SQL 2005, right click and select "Import Data", Source is Microsoft OLE DB Provider for Oracle and I setup my connection. so far so good.

I create my Destination for SQL Native Client to the Database that I plan on importing into. Still good

Next, I select "Copy data from one or more tables or views". I move on to the next screen and select all of the Objects from a Schema. These are Tables that only relate to our application or in other words, nothing Oracle System wise.

When I get to the end it progresses to about 20% and then throws this error about 300 or so times:

Could not connect source component.
Warning 0x80202066: Source - AM_ALERTS [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.

So, I'm thinking "Alright, we can search on this error and I'm sure there's an easy fix." I do some checking and indeed find out that there is a property setting called "AlwaysUseDefaultCodePage" in the OLEDB Data Source Properties. Great! I go back and look at the connection in the Import and .... there's nothing with that property!

Back to the drawing board. I Create a new SSIS package and figure out quickly that the AlwaysUseDefaultCodePage is in there. I can transfter information from the Oracle Source Table to the SQL Server 2005 Destination Table, but it appears to be a one to one thing. Programming this, if I get it to work at all, will take me about 150 hours or so.

This make perfect sense if all you are doing is copying a few columns or maybe one or two objects, but I am talking about 600 + objects with upwards of 2 million rows of data in each!!

This generates 2 questions:
1. If the Import Data Wizard cannot handle this operation on the fly, then why can't the AlwaysUseDefaultCodePage property be shown as part of the connection
2. How do I create and SSIS Package that will copy all of the data from Oracle to SQL Server? The source tables have been created and have the same Schema and Object Names as the Source. I don't want to create a Data Flow Task 600 times.

Help!!!

View 8 Replies View Related

Import Oracle Tables Using Dts Or Other...

Mar 29, 2006

i am using sql server express edition.
i need to import (or make copies) on a weekly/daily basis, of several oracle tables into a new adp database.

what is the fastest option? some of these tables hold over 1m records.
so ideally i would like to schedule a refresh or update job outside busy hours.

I have heard of "linked servers" and "dts", but would like some experts advice before starting looking into this.

many thanks
luzippu

View 3 Replies View Related

Import Data From Oracle

May 2, 2007

I am using Import and Export wizard and seeing a very weird behaviour.



I am able to choose 'Microsoft OLE DB Provider for Oralce' and provide server name, username/ password. The test connection results in a success. However when I try to move to next window/page by clicking next I get invalid oracle error ora-01017 - invalid username/ password.



what is going on here?

View 1 Replies View Related

Oracle Import Inconsistency

Apr 4, 2008

Hi all,

I recently encountered what will appear to me as a very strange problem with my packages involving data import from an Oracle 10g database. The packages are simple data imports which select and insert data from Oracle to SQLServer 2005 tables.

Till recently, the packages ran without any problems but a day ago, these packages starts to fail when nothing was changed in them. They will run for about 8 minutes, at which approximately 400,000 records will be selected and processed. After that these packages will fail. The failure rate is inconsistent in the sense that some of these packages will fail while some will pass, depending on the 'mood' of SSIS.

These packages are using Oracle's supplied OLE DB drivers and have been running well since day 1. Should I be concerned with Oracle terminating connection after 'x' minutes something like that?

I'm absolutely puzzled with these behavior and will appreciate it if someone can point me to the right direction. This server is a 64 bit server and SQL Server is installed as 64 bit as well.

Thanks in advance!
Nestor

View 6 Replies View Related

Cannot Import Oracle Data With OLE DB

Mar 6, 2006

I have a column in an Oracle source system with data type NUMBER(38,2). The value "-0.01" is causing problems when trying to import into a SSIS data-flow.

The only way I can import this into my data-flow is by using a Datareader connection manager using the ODBC Data Provider. My DSN is using the Oracle ODBC driver.

If I try and use the "Native OLE DBMicrosoft OLE DB Provider for Oracle" I get an error: "The data value cannot be converted for reasons other than sign mismatch or data overflow"

Judging by this post: http://microsoftdw.blogspot.com/2005/11/final-storyhow-to-get-data-out-of.html there aren't really any other combinations to try that will bring my data in as I want it.

I don't want to use ODBC though as:

Its an old technology
Its slow
I have to deploy an additional DSN.

Can anyone tell me why the other options don't work? Why does Microsoft OLE DB Provider for Oracle have a problem with "-0.01"?

-Jamie

 

View 2 Replies View Related

Data Import From Oracle To SQL Server

Mar 3, 2000

Does 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

View 2 Replies View Related

Need To Compare Data In SQL To Oracle / Import Changes

Mar 10, 2005

We will need to routinely import only changed data from an Oracle data base into a SQL database.
So we need an agent that will
1) Compare data in both databases (From disparate tables)
2) Import only that which is changes or new.

I am new to SQL server administration and am looking for a best practice method that we can be run on a weekly basis.
I am open to using third party software solutions, but would prefer a native MS SQL 2000 solution.
Can someone point me in the right direction?
Thanks.

View 1 Replies View Related

Sql Import/push To Oracle Faster

Jan 10, 2006

Looking for a faster method of moving data from SQL to Oracle.

I'm attempting to push a sql table into an oracle table (sql server 2000 oracle 7, 8, and 9). I have no problem doing this with either 'Oracle Provider for OLE DB' or the 'Microsoft OLE DB Provider for Oracle'. None of my data is being transformed so its a straight import. With the hardware I'm using it takes nearly 3 seconds to import 1000 rows. While this isn't too bad, I need to import upwards of 4 million rows and this results in unacceptable time results.

I do have an oracle script that imports the csv files of the tables, but I'm looking for an all inclusive sql solution.

Does anyone know of another method in SQL that I can use to push the data faster?

View 7 Replies View Related

Connecting To Oracle Datasource For Import

Nov 29, 2006

Now this is the first time i have tried this with SQL 05 I am trying to Import data from an Oracle DB. I have the connection info:

Xtx20xxx = (DESCRIPTION = (ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =urlpart1.part2.part3.com)(PORT = 1521)))(CONNECT_DATA =(SID = Xtx20xxx ))
)


I used the Microsoft OLEDB provider for oracle from the dropdown,

The username and password I have also. I tried to enter in the Xtx20xxx as the server name, clicked "Properties"

put in the username and password I can given and clicked "Test Connection". No matter what I get the infamous ORA-12154: TNS:could not resolve service name error message. I am sure I am missing something obvious here but I have absolutely no clue and all the googling in the world has not given me any help. I do have the Oracle 10g client tools installed on this workstation (running Windows XP Prof SP2 and SQL 2005 STD).

View 5 Replies View Related

How To Import The Data From Oracle To SQL Server?

Feb 11, 2007

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?

View 2 Replies View Related

Integration Services :: Import Oracle Data

May 25, 2015

I'm using - Destination - Oracle driver - oraOLEDB.Oracle.1 (native ole dboracle provider for ole db)

Source - SQL driver - microsoft ole db prover for sql server. I want to import data from sql server to oracle. Challenge is, I have 1 million records on oracle. I have 100 records on sql server (these 100 records count will change daily). So, I thought of using 'lookup' task looking taking record from ms sql and fetch corresponding record from oracle. But when I use lookup, all records from oracle are loading into cache, which is taking approx 3 hrs.

View 4 Replies View Related

SSIS: Oracle Import Works, But Not When Scheduled

Dec 26, 2006

Hi,

I am running SQL Server 2005 and have built a simple SSIS package to import data from an oracle database to my SQL Server 2005 database. When I run it in SSIS, it works and it imports just fine. When I schedule it, it gives me problems. Help!

It might be a problem with Oracle Provider client I installed. Is there a client version I can download and install? the one I downloaded from oracle doesn't work. I bet i did something wrong though.

Here is my version:

Microsoft SQL Server Management Studio 9.00.2047.00
Microsoft Analysis Services Client Tools 2005.090.2047.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790

Microsoft Visual Studio 2005
Version 8.0.50727.42 (RTM.050727-4200)
Microsoft .NET Framework
Version 2.0.50727

Installed Edition: IDE Standard

SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.2047.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.2047.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.2047.00



Thanks,

Ryan

View 5 Replies View Related

SSIS Import Data From Oracle To SQL Server

Apr 19, 2007

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

View 1 Replies View Related

SSIS Oracle --&&> SQL Server Import Fails

Sep 6, 2006

Im getting the following error when I try to export from Oracle 9i and import into SQL Server 2005 using Oracle and SQL OLE DB Provider's respectively.

Cannot retrieve the column code page infor from the OLE DB Provider. If the component supports the "DefaultCodePage" property, the code page from that property value 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.

I know there is a way to change the property settings for the OLE DB Provider in the Data flow section of the development studio BUT ...

1) Is there a way to change this outside of the development studio?

2) I can't create the package to setup in the studio because even when I uncheck execute immediately and check save package, it still fails and never creates anything.


Any help is much appreciated

Thanks

Scott

View 2 Replies View Related

How To Import An Oracle 8.1.7 Dump File Into SQL Server 2000 ?

Mar 2, 2005

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

View 2 Replies View Related

Question About By Import Oracle Data And Field Is Null

Jan 23, 2007

Hi, I've a question about importing Oracle data and some fields are null. I get an error 'Conversion failed because the data value overflowed the specified type'. When i look in preview query result, via OLE db Source editor > Preview, this field contains '<value too big to display>'.

What do i do wrong? Can somebody help me?

Thanks in advance

Olaf

View 6 Replies View Related

VB Code To Export And Import SQL Server Table To Oracle

Jan 29, 2008

Hi,

Please do anyone know a VB 2005 code to export and import SQL server table to Oracle. Thanks.

View 3 Replies View Related

Native Oracle SQL -&&> SQL Server 2005 CE .sdf File -&&> Using Visual Studio 2005?

May 23, 2007

I've got a table adapter that connects using an oracle data connector. In the adapter, I'm using native oracle SQL such as:

select TO_DATE(SUBSTR(TO_CHAR(weird_oracle_field),0,12),'YYYYMMDDHH24MI') as dt_added from oracle_data_table

There's also a CASE statement in there with some other data transformations.

Anyway, I want to take the results of that Oracle query and put the dataset into a SQL Server Compact Edition database - within an application that I'm creating in Visual Studio 2005.

For whatever reason, I can't seem to do anything like that in 'bulk' and there aren't any data migration tools that work with anything other than "full" SQL Server versions. My client doesn't support SQL Server, but I can deploy my app with SQL CE. I need a 'local' copy of the database (for several reasons) and just can't seem to figure out how to make this work.

I'm really going nuts. I feel like I'm soooo close when I see the data I want in the table adapter - but I can't seem to actually *move* the data over!!

Can anyone help?

thanks,

Jon

View 6 Replies View Related

Reporting Services 2005 (VS 2005) Using Oracle 10g Stored Procedure

Apr 17, 2008

Hi,
I want to get data from Oracle 10g Stored procedure to Reporting Services 2005. I could pass a SQL text and get a record set, but I want to execute a store proc and get the record set.


1. Add New Data Source
2. Choose Type : Oracle and connection tested OK
3. { call Test_Package.Test_Procedure(?) } is it wrong... how to write???
There is an error in the query. ORA-00911: invalid character


Also make sure that you use the text-based generic query designer (2 panes !) instead of the visual query designer (4 panes) - you can switch between them through an icon on the toolbar in the data view of report designer.
Question : I tried many methods but unable to solve it...

create or replace
PACKAGE Test_Package
AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE;
END Test_Package;


create or replace PROCEDURE Test_Procedure (
Test_Cursor IN OUT Test_Package.Test_Type,
Test_Parameter IN Test_Table.ID%TYPE
)
AS
BEGIN
OPEN Test_Cursor FOR
SELECT *
FROM Test_Table WHERE Test_Table.ID >= Test_Parameter;
END Test_Procedure;

The below site gave some example but i could not solve it... any suggestions greatly appreciated...

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=357121&SiteID=17


http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_frm/thread/bbc613c4529ed3cd/696624ec4ba70937?q=oracle+stored+procedures

View 1 Replies View Related

Import Access Tables (set Up As Pass-through Table Types To Oracle )--OLE DB Connection To Access Cannot See Them

Mar 17, 2008

Access Connection

create a new Connection Manager by right-clicking in the Connection Managers section of the design area of the screen. Select New OLE DB Connection to bring up the Configure OLE DB Connection Manager dialog box. Click New to open the Connection Manager. In the Provider drop-down list, choose the Microsoft Jet 4.0 OLE DB Provider and click OK.
Browse to the Access database file and connection set up---all good!!!

Dataflow task
Add an OLE DB Source component
Double-click the icon to open the OLE DB Source Editor. Set the OLE DB Connection Manager property to the Connection Manager that I created . Select Table from the Data Access Mode drop-down list.
I cannot see the tables set up as set up as pass-through table types to a Oracle 9i db

Any ideas please help

thanks in advance
Dave

View 2 Replies View Related

Visual Studio 2005 - Reporting Services 2005 Access Import Error

Oct 22, 2007

I am trying to import Access reports using Visual Studio 2005 and Access 2007. SQL Server 2005 with Reporting Services is also installed. I select Reports in the Solution Explorer and then Import Reports... Microsoft Access and browse to my Access database. After I select the database I see it open briefly as if it is trying to import the reports but then Access quickly closes and a error pops up: "You already have the database open." I have checked and double checked and the database is not open. I have even tried several different databases and the same errror occurs. Any help would be greatly appreciated.

View 1 Replies View Related

Oracle And SQL Server 2005

Oct 20, 2007

I have more than 8 GB Data in oracle and Everyday we have to check some data in oracle but it takes times due to lot of data. So what i was looking that data that we need can import in SQL Server database and do checking purpose. But I was looking some command that connect to oracle brings its data and import in SQL Server. For that we will fire SQL statement to oracle through asp.net and  insert into SQL Server I will fire differencial data insert covery only. is there any way or idea to do that please advice me.

View 3 Replies View Related

SQL Server 2005 Vs Oracle

Apr 6, 2006

Anyone know where I can find some good resources to help us choose betweenSQL and Oracle ( Progress Openedge as well ) . Any comments on what youwould choose ?? We are creating a new Warehouse Management System which wilmanage our very large inventory.Anyway comments suggestions welcomeThanksPaul

View 42 Replies View Related

Where DTS In SQL 2005; Oracle To SQL2k5??

Nov 2, 2006

I need tyo move tables from Oracle 9.2 or 10.2 version to SQL server 2005.Can any one give me some good hints, scripts etc.I try to look into the Business Intellegent Studio, but I only see Report,Analaysis or Integration services - NO DTS.Can someone tell me where to find it. It used to be so easy in 2000.Thanks,Nasir

View 4 Replies View Related

Oracle .dmp To SQL Server 2005

Aug 28, 2007

I am trying to import an Oracle .dmp database into SQL Server 2005, what would you suggest as the fastest and easiest way to do this? DTS/SSIS doesn't have an instant solution and the script I tried to use gave me an error...


Msg 3241, Level 16, State 0, Line 1

The media family on device '<DATABASE>' is incorrectly formed. SQL Server cannot process this media family.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.


I would like a GUI based way to do this if at all possible. Thanks
-Kyle

View 8 Replies View Related

DTS From Oracle To SQL Server 2005

Mar 13, 2008

Hello guys,

I'm relatively new to DTS and I was trying to import data from the oracle database to SQL server database based on some parameter from the destination table.
i.e select * from SourceTable where SourceTable.Column1 > (select max(Column1) from DestinationTable)
This tries to search DestinationTable from the source database(Oracle)
How shoud I go to solve this problem?
Any Hint is greatly appreciated....

View 1 Replies View Related

SQL Server 2005 64-bit &&amp; Oracle

Feb 21, 2008



OK new Windows 2003 Enterprise 64-bit with MS SQL Server 2005 64-bit.. Now I am migrating the DTS packages from our olld Windows 2000 Standard with MS SQL 2000 (all 32-bit). I am not moving the packages to SSIS yet just want to move the legacy DTS jobs over... However whenever I try and connect to the Oracle DB I got ORA-12154: TNS:could not resolve the connect identifier specified.

I have the Oracle 10g 32-bit & Oracle 10g 64-bit clients installed and I can run SQL+Plus and do everything just fine. It is just in MS SQL 2005 I can not. Even in SSIS I get the ORA-12154 error. I can create an ODBC and test it and it works just fine... Has anyone here ever fixed this? Some say it is b/c of the ('s around x86 in program files and I have done their suggestion in making a ProgramFilesx86 directory and copy the visual stupid & sql directories there... still nothing. I have one of my DBA's searching Oracle for any patches but thought I would ask and see.

Thanks,

Billy S.

View 3 Replies View Related

SQL Server 2005 Versus Oracle

Jun 18, 2007

If there is someone who is intimate with both sqlserver and oracle tell me the main differences between the two?

what features are not available in either one? is one better for certain situations over others?

View 5 Replies View Related

Tutorial For DTS From Oracle To SQL Server 2005

Jan 9, 2008

Hi,
I want the tutorial for developing Integration Service for copying the database from Oracle 9i to SQL Server 2005.

If anybody knows then pls give me the steps..

Thanx in advance.

View 1 Replies View Related

How To Transfer Oracle Table To SQL 2005

Feb 6, 2008

Hi all

How to transfer oracle table to SQL 2005.

Thanks

View 1 Replies View Related

Tuning On Oracle 10g And SQL Server 2005

May 4, 2006

Does anyone have some more detailed information about how Oracle and MSimplement / allow Tuning on Oracle 10g and SQL Server 2005 and thedifferences between them?Which of them, In a deep comparison about it, allow better tuning andwhy.Regards,Marcio Evangelista

View 3 Replies View Related

Need Help With SSRS 2005 Report Using Oracle 9i Db

Jan 18, 2008



We have been, successfully, using SSRS 2005 with SQL databases. Now are trying to do create SSRS 2005 reports using an Oracle database. What components do I need to install on our SSRS 2005 server to successfully render a report, in a web browser, using an Oracle 9i database. Any help would be appreciated.

View 15 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved