1 2 Previous Next 20 Replies Latest reply: Jun 3, 2014 4:40 PM by 1060867 RSS

    ODI ERROR

    1060867

      Hi All ,

       

      I am getting below error on ODI 11g  scenario execution

       

      ODI-1226: Step Update_Process_File fails after 1 attempt(s).

      ODI-1232: Procedure Update_Process_File execution fails.

      ODI-1228: Task Update_Process_File (Procedure) fails on the target MICROSOFT_SQL_SERVER connection SQL_HIFI_STG.

      Caused By: java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'Where'

       

      and update code is

       

       

      Update PROD_HIFISTAGE.HIFISTAGE.ESSBASE_LOAD_FILE SET PROCESS_FLAG = 'Y' , PROCESS_DTM = CAST(GETDATE() AS DATETIME),SESSION_NO=#MDM.CURRENT_SESSION, COMMENTS=#MDM.COMMENTS

      Where LD_File_Name

      LIKE  '#MDM.CURRENT_FILENAME' AND SESSION_NO=#MDM.CURRENT_SESSION

       

      Can anyone help me ASAP.

        • 1. Re: ODI ERROR
          Jayaprakash -Oracle

          Hi ,

           

          Thank you for participating in the Oracle Application Server community.

           

          This community is to discuss Oracle Application Server specific issues, you can post this query in Oracle Data Integrator community for better response:

           

          Data Integrator

           

          Best regards,

          Prakash.

          • 2. Re: ODI ERROR
            JeromeFr

            Hi,

             

            I suspect it's because of your column LD_File_Name. I'm pretty sure it would work if it was UPPER case or surrounded by double quotes.

            Do you have a way to change it to upper case?

             

            What is the IKM you use?

            Also could you execute it with log level 6 (ODI 11.1.1.6+ only) and check in the operator that all the variables are correctly set on that task?

             

             

            Regards,

            JeromeFr

             

             

            PS : love the tag

            • 3. Re: ODI ERROR
              Phanikanth

              Hi

               

              Please modify the update query manually and hardcode the updated values and run in SQL Server and check whether  it is working are not.

               

              Regards,

              Phanikanth

              • 4. Re: ODI ERROR
                1060867

                Hi Phanikanth,

                 

                The query is running successfully from the SQL Studio, I don't know what to do it with while executing it with ODI getting error

                • 5. Re: ODI ERROR
                  1060867

                  Hi Jerome,

                   

                  It did not work for me I have tired it by changing the log level also actually the problem is that we have upgraded ODI from 10g to 11g and we did not  have development work repository so we cannot modify variable procedure also we have scenario compiled code from that only we are executing any help from your side still getting same error

                  • 6. Re: ODI ERROR
                    Phanikanth

                    Hi

                     

                    Just do one trail

                     

                    just run below 2 variables alone and check the history on both the variables and see the output is coming in ODI variables are not

                     

                    #MDM.CURRENT_FILENAME

                    #MDM.CURRENT_SESSION

                     

                    See history after you ran above 2 ODI variables.

                     

                    Note: Make sure that both variables are refresh type

                     

                    Regards,

                    Phanikanth

                     

                    Message was edited by: Phanikanth

                    • 7. Re: ODI ERROR
                      JeromeFr

                      That's a problem if you only have the scenario. As it is a complete blackbox, you can't change variables and interfaces.

                       

                      When executing with log level 6, can you see the value of #MDM.COMMENTS ?

                       

                      Could you check on the SQL Server DB if the column LD_File_Name is actually mixing upper case and lower case? If this is the case, would rename it to LD_FILE_NAME (all upper case) at databse level a possible solution for you?

                       

                       

                      Regards,

                      JeromeFr

                      • 8. Re: ODI ERROR
                        1060867

                        Hi JeromeFr,

                         

                        I have tried that also i have update the column name as used in query it still showing me the same error actually we did not have any development repository and not any project we have just complied code of scenario that we are executing and verifying our new  environment from old environment that scenario is working or not

                        • 9. Re: ODI ERROR
                          Phanikanth

                          Hi

                           

                          Did you checked the values are populating on 2 ODI variables ?

                           

                          Regards,

                          Phanikanth

                          • 10. Re: ODI ERROR
                            1060867

                            Hi Phanikanth,

                             

                            I have checked the value on 2 ODI variable is on Current file name is GL Capex Account .csv and Current session is 576051

                             

                            Thanks

                            • 11. Re: ODI ERROR
                              JeromeFr

                              I'm more concerned about COMMENTS to be honest.

                              • 12. Re: ODI ERROR
                                1060867

                                HI ,

                                 

                                In comment there is no value

                                 

                                Thanks

                                • 13. Re: ODI ERROR
                                  JeromeFr

                                  And that's probably the problem , that's why I was asking about it in all my previous messages.

                                   

                                  You're trying tu execute that code which doesn't work :

                                   

                                  (...) COMMENTS= Where LD_File_Name LIKE (...)

                                   

                                   

                                  So you need a value for comments, or null if there is nothing.

                                   

                                  Regards,

                                  JeromeFr

                                  • 14. Re: ODI ERROR
                                    1060867

                                    HI JeromeFR,

                                     

                                    Can you tell me the whole update code so that i ca n try with that and let u know

                                     

                                    Update PROD_HIFISTAGE.HIFISTAGE.ESSBASE_LOAD_FILE SET PROCESS_FLAG = 'Y' ,

                                    PROCESS_DTM = CAST(GETDATE() AS DATETIME),SESSION_NO=#MDM.CURRENT_SESSION, COMMENTS=#MDM.COMMENTS

                                    Where LD_File_Name LIKE  '#MDM.CURRENT_FILENAME' AND SESSION_NO=#MDM.CURRENT_SESSION

                                    1 2 Previous Next