6 Replies Latest reply on Jan 18, 2010 12:15 PM by Varun Kaushal

    ODI error: Executing Interface

    Varun Kaushal
      I am trying to load data from a flat file to a Excel file. Reverse engineering is working perfectly fine.

      At the time of execution of interface i am getting a warning message at
      export-test1-drop table

      -1305 : S0002 : java.sql.SQLException: MicrosoftODBC Excel Driver Table 'C$_testexcel' does not exist.
      java.sql.SQLException: MicrosoftODBC Excel Driver Table 'C$_testexcel' does not exist.

      at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

      at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

      at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)

      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)

      at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeUpdate(Unknown Source)

      at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)

      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)

      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)

      at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)

      at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)

      at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)

      at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)

      at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)

      at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)

      at com.sunopsis.dwg.cmd.e.i(e.java)

      at com.sunopsis.dwg.cmd.g.y(g.java)

      at com.sunopsis.dwg.cmd.e.run(e.java)

      at java.lang.Thread.run(Unknown Source)

      Thankx in advance
      Nick
        • 1. Re: ODI error: Executing Interface
          Varun Kaushal
          And also i am getting an error at execution of interface at the time of create work table

          -3551 : 37000 : java.sql.SQLException: [Microsoft][ODBC Excel Driver] Syntax error in CREATE TABLE statement.
          java.sql.SQLException: [Microsoft][ODBC Excel Driver] Syntax error in CREATE TABLE statement.

               at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)

               at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)

               at sun.jdbc.odbc.JdbcOdbc.SQLPrepare(Unknown Source)

               at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(Unknown Source)

               at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(Unknown Source)

               at com.sunopsis.sql.SnpsQuery.a(SnpsQuery.java)

               at com.sunopsis.sql.SnpsQuery.a(SnpsQuery.java)

               at com.sunopsis.sql.SnpsQuery.updateExecStatement(SnpsQuery.java)

               at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)

               at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)

               at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)

               at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)

               at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)

               at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)

               at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)

               at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)

               at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)

               at com.sunopsis.dwg.cmd.e.i(e.java)

               at com.sunopsis.dwg.cmd.g.y(g.java)

               at com.sunopsis.dwg.cmd.e.run(e.java)

               at java.lang.Thread.run(Unknown Source)

          Thankx in advance
          Nick
          • 2. Re: ODI error: Executing Interface
            566902
            Nick
            In order for this to work you will need to select a staging area different from target, as you are taking data from, and moving it to technologies which do not support staging. The simplest thing is to select the SUNOPSIS MEMORY ENGINE as the staging area. This will owrk fine, as long as you are not moving huge volumes of data. You should then select the LKM File to SQL, and the IKM SQL to SQL Append as your KMs. (this all assumes that uou do need to stage)
            If you don't need to stage the data, check to see if there is an IKM File to SQL available, and the set the staging area different to target, and use that KM, which should be able to get the data out of the file, and load it into Excel.

            C
            • 3. Re: ODI error: Executing Interface
              Varun Kaushal
              Thankc CTS,

              I after including sql to sql append module that error got resolved. But now during the execution of the interface i got the following error.


              0 : S1000 : sun.jdbc.odbc.JdbcOdbcBatchUpdateException: [Microsoft][ODBC Excel Driver] Operation must use an updateable query.
              sun.jdbc.odbc.JdbcOdbcBatchUpdateException: [Microsoft][ODBC Excel Driver] Operation must use an updateable query.

                   at sun.jdbc.odbc.JdbcOdbcPreparedStatement.emulateExecuteBatch(Unknown Source)

                   at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeBatchUpdate(Unknown Source)

                   at sun.jdbc.odbc.JdbcOdbcStatement.executeBatch(Unknown Source)

                   at com.sunopsis.sql.SnpsQuery.executeBatch(SnpsQuery.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execCollOrders(SnpSessTaskSql.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSqlI.treatTaskTrt(SnpSessTaskSqlI.java)

                   at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)

                   at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)

                   at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)

                   at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)

                   at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)

                   at com.sunopsis.dwg.cmd.e.i(e.java)

                   at com.sunopsis.dwg.cmd.g.y(g.java)

                   at com.sunopsis.dwg.cmd.e.run(e.java)

                   at java.lang.Thread.run(Unknown Source)

              Thankx in advance
              Nick
              • 4. Re: ODI error: Executing Interface
                566902
                What is the statement giving this error: copy the text from the command on source in so we can see it.
                Thx
                • 5. Re: ODI error: Executing Interface
                  Varun Kaushal
                  Code present in Loading:Query/Execution Plan
                  select      
                       C1_ENTITY     as ENTITY,
                       C2_ACCOUNT     as ACCOUNT,
                       C3_AMOUNT     as AMOUNT
                  from      "C$_0testexcel"
                  where     
                       (1=1)


                  Code present in Default:Query/Execution Plan

                  insert into "testexcel"
                  (
                       "Entity",
                       "Account",
                       "Amount"

                  )
                  values
                  (
                       :ENTITY,
                       :ACCOUNT,
                       :AMOUNT

                  )

                  Is this what you are looking for???

                  Thankx in advance
                  Nick

                  Edited by: user8989360 on Jan 18, 2010 4:16 AM
                  • 6. Re: ODI error: Executing Interface
                    Varun Kaushal
                    Code present in Loading:Query/Execution Plan
                    select      
                         C1_ENTITY     as ENTITY,
                         C2_ACCOUNT     as ACCOUNT,
                         C3_AMOUNT     as AMOUNT
                    from      "C$_0testexcel"
                    where     
                         (1=1)


                    Code present in Default:Query/Execution Plan

                    insert into "testexcel"
                    (
                         "Entity",
                         "Account",
                         "Amount"

                    )
                    values
                    (
                         :ENTITY,
                         :ACCOUNT,
                         :AMOUNT

                    )

                    Is this what you are looking for???

                    Thankx in advance
                    Nick