Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Best way to import MS SQL Server table into Oracle

    I have a large table (35,000 rows) in SQL Server on WinNT, which I need to import into Oracle 9i on Solaris. Here is the table creation spec for the table i SQL Server:

    CREATE TABLE [tblMessage] (
    [msgID] [int] NOT NULL ,
    [msgSent] [smalldatetime] NOT NULL ,
    [msgFromType] [int] NOT NULL ,
    [msgFromID] [int] NOT NULL ,
    [msgSubject] [varchar] (255) NOT NULL ,
    [msgMessage] [text] NOT NULL ,
    [msgOriginal] [int] NULL ,
    [attID] [int] NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    ...and in Oracle:

    CREATE TABLE tblMessage (
    msgID int NULL ,
    msgSent date NULL ,
    msgFromType int NULL ,
    msgFromID int NULL ,
    msgSubject varchar2(255) NULL ,
    msgMessage clob NULL ,
    msgOriginal int NULL ,
    attID int NULL
    );


    What is the best way to migrate this table? I have tried exporting the table from SQL Server over ODBC, but only to get one of two errors: either an 03113 error (end-of-file on communcation channel) or an illegal parameter call on an OCI function call. I'm reasonably certain that Oracle is installed properly on the server; I've adjusted the memory properly in /etc/system, and the network connection is stead (I've migrated 45 other tables over the same network with no problems). I've tried replacing the CLOB field in Oracle with an NCLOB field (got a data mismatch error) and with varchar2(4000) (got a "value too large" error). I've replaced all of the non-ascii characters in the SQL Server table with ASCII characters. I've tried exporting to a csv file and loading the file with SQL*Loader, but it just hangs forever and won't complete the load.

    The Oracle Migration Workbench won't work; the previous DBA failed to assign proper ownership to the SQL Server tables, and the OMWB won't handle them.

    I'm stuck. Has anyone got any ideas?

  2. #2
    Join Date
    Apr 2003
    Location
    Los Alamos/Santa Fe, NM
    Posts
    69

    Access or other 3 party tools

    You know What I did. There are 3rd party tools out there to do this for you. Check out Google and search for migration tools.

    I needed to test moving from Oracle to Microsoft SQL..long story. Anyway I used Access. I imported the tables and data into Access, then exported them into Microsoft SQL. That is a dirty way to get the job done, but it works.
    Michellea Southern-David

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Exclamation

    Definitely use a tool. There are plenty of them out there.

    This is a tiny amount of data. Probably you don't need to do anything particularly clever to get it done.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Access or other 3 party tools

    Microsoft has a GUI tool for bulk loading data between data sources and targets. The Source can be numerous types of objects, spreadsheets, flat files, Access, Sql-Server, Oracle, and the Target can be numerous types of databases. Its called DTS. Data Tranformation Services I believe it stands for. You should be able to find it on the Sql_Server CD or with MS development CD.

    I have used it to successfully load data to and from numerous databases including Sql-Server and Oracle.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •