Results 1 to 6 of 6
  1. #1
    Join Date
    May 2007
    Posts
    3

    Unanswered: SQL --> to Oracle Load error

    hi,

    getting ORA-01722: invalid number error when running SQL 2000 DTS to Oracle 10g. This package works fine currently in production which is SQL 2000 and Oracle 9i.

    We are going to 10g and in dev the DTS package giving us error:

    Unknown provider error.
    [OLE/DB provider returned message: [Microsoft][ODBC driver for Oracle][Oracle]ORA-01722: invalid number
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80040e03: Unknown provider error.].


    please help

    thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by stoney12
    hi,

    getting ORA-01722: invalid number error when running SQL 2000 DTS to Oracle 10g. This package works fine currently in production which is SQL 2000 and Oracle 9i.

    We are going to 10g and in dev the DTS package giving us error:

    Unknown provider error.
    [OLE/DB provider returned message: [Microsoft][ODBC driver for Oracle][Oracle]ORA-01722: invalid number
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80040e03: Unknown provider error.].


    please help

    thanks
    We need a lot more info here to be able to help...

    What's the ddl for the source table? What's the ddl for the destination table?

    Have you upped the logging for the DTS package (row level logging and export failed rows to file)?

    Help us help you...

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    May 2007
    Posts
    3
    actually it is a scheduled sql job which executes a sproc. All it does is gather data in sql server and does a straight insert into oracle. not sure if that is the kind of response you were looking for.

  4. #4
    Join Date
    May 2007
    Posts
    3
    This might be a better explanation:
    1. At the start of the process in sqlserver, the process
    will do an insert to this table with an action of 'TRUNCATE TABLE' this
    trigger will fire to delete all existing data from the temp tables.

    2. SqlServer will load new orders into a temporary order header table
    and a temporary order detail table.

    3. the process will then insert into this table with an action of
    'PROCESS ORDERS' causing this trigger to fire and load the temp table into the so interfaces tables.

    Unknown provider error.
    [OLE/DB provider returned message: [Microsoft][ODBC driver for Oracle][Oracle]ORA-01722: invalid number

    error during execution of trigger
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::InsertRow returned 0x80040e03: Unknown provider error.].

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by stoney12
    actually it is a scheduled sql job which executes a sproc. All it does is gather data in sql server and does a straight insert into oracle. not sure if that is the kind of response you were looking for.
    Unless I'm missing something here, your statements are contradictory:

    From your original post:
    Quote Originally Posted by stoney12
    We are going to 10g and in dev the DTS package giving us error:
    Now you're saying it's a job that calls a sproc.

    Which is it?

    More importantly, the internal mechanics of the sproc probably reference one (or more) tables. It is probably attempting to insert one (or more) parameters into the columns of the table(s). What are the data types of the columns of the destination table? What are the data types of the source parameters?

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Maybe I am being too picky here, but your statements confuse me...

    Quote Originally Posted by stoney12
    This might be a better explanation:
    1. At the start of the process in sqlserver, the process
    will do an insert to this table with an action of 'TRUNCATE TABLE'
    Which is it? Are you inserting data into a table? Or are you truncating the table. These are two fundamentally different operations. I am assuming (for now) that you meant to say, "The process issues a TRUNCATE TABLE statement against the Sales Order Header and Sales Order Detail temporary tables".

    Quote Originally Posted by stoney12
    this trigger will fire to delete all existing data from the temp tables.
    The basis of my previous assumption. It is incorrect for you to call this a "trigger".

    Quote Originally Posted by stoney12
    2. SqlServer will load new orders into a temporary order header table
    and a temporary order detail table.
    Fair enough. What's the ddl (DESCRIBE in Oracle) for the header and detail tables?

    Quote Originally Posted by stoney12
    3. the process will then insert into this table with an action of
    'PROCESS ORDERS'...
    What is 'PROCESS ORDERS'? Is it a stored procedure? A DTS package? A sql job? An external program?

    Quote Originally Posted by stoney12
    ...causing this trigger to fire and load the temp table
    Do you understand what a trigger is? Is that really what is in effect here (ie, you insert records into the temp tables where there is a trigger to insert the incoming data into the permanent tables)?

    Quote Originally Posted by stoney12
    into the so interfaces tables.
    what are the "so interface tables"? Sales Order interface tables?

    Bottom line: I still need to see the DDL of the source and destination tables in order to be able to provide any assistance.


    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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