Trouble importing into SQL Server from Oracle

  • I'm trying to use the DTS Import/Export Wizard to import data from an Oracle 9iR2 database running on Linux into a SQL Server 2000 instance running on Windows 2003 Server.

    I have installed the Oracle client software on the windows server, and verified that the connection works by running SQL*Plus to run a few simple queries.

    As I go through the import wizard, everything works fine up to the point where I select the source tables that I want to import.  The list of tables is fine, and I select the tables I want to import, then press "Next", at which point I get the following error:

    Error Source: Microsoft OLE DB Provider for Oracle

    Error Description:  Unspecified error

    Oracle error occurred, but error message could not be retrieved from Oracle.

    Data type is not supported

    Context:  Error calling OpenRowset on the provider.

    I've actually had marginal success using the ODBC driver, but the errors there are worthy of a completely separate topic =)

    Any ideas what's going on here?

    Rick Root

  • Datatype incompatibilities are typically the problem here. I have had problems importing dates sometimes because Oracle Datetime datetype can handle dates outside the range that SQL Server DateTime datatype can handle. One way to test your feeds is to export your data to a flat file and then importing it into SQL Server.

    The way I handle out of range values from Oracle is to do a conversion into Materialized views in Oracle and then pulling the data using a DTS data pump.

    Since you are able to connect using your credentials, I don't think that permissions are an issue here.

    Cheers,

    Rushabh


    Rushabh Mehta

    SQL Server MVP
    Solid Quality Learning

  • Rick,

    Can you provide the table structure of the data that you're bringing over?

    As for dateTime values, I usually will get an overflow error if it's an invalid date along with the offending column.....but we've only gotten that from DB2.

    Also, if you're logging your package, you may find further info there.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply