Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Getting error from "SQL" job step

  1. #1

    Default Getting error from "SQL" job step

    Hello,

    I am getting strange error in SQL job step. I am trying to execute the following SQL script, but receive an error: "The executeQuery method must return a result set". The same script can be executed directly on server without any problems, so it is Kettle error. Can anyone give some explanation?

    Thank you,

    Romans


    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : An error occurred executing this job entry :
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : An error occurred executing SQL:
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : SELECT TOP (100) PERCENT dbo.LU_FIN10_small.FIN_ID, dbo.LU_FIN9.FIN9_ID, dbo.LU_FIN8.FIN8_ID, dbo.LU_FIN7.FIN7_ID, dbo.LU_FIN6.FIN6_ID,
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN5.FIN5_ID, dbo.LU_FIN4.FIN4_ID, dbo.LU_FIN3.FIN3_ID, dbo.LU_FIN2.FIN2_ID, dbo.LU_FIN1.FIN1_ID, dbo.LU_FIN10_small.FIN10_CODE,
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN10_small.FIN10_DESC, dbo.LU_FIN10_small.FIN10_IBAN, dbo.LU_FIN10_small.FIN5_ID_BUDZ
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : into lu_fin10
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : FROM dbo.LU_FIN1 INNER JOIN
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN2 ON dbo.LU_FIN1.FIN1_ID = dbo.LU_FIN2.FIN1_ID INNER JOIN
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN3 ON dbo.LU_FIN2.FIN2_ID = dbo.LU_FIN3.FIN2_ID INNER JOIN
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN4 ON dbo.LU_FIN3.FIN3_ID = dbo.LU_FIN4.FIN3_ID INNER JOIN
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN5 ON dbo.LU_FIN4.FIN4_ID = dbo.LU_FIN5.FIN4_ID INNER JOIN
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN6 ON dbo.LU_FIN5.FIN5_ID = dbo.LU_FIN6.FIN5_ID INNER JOIN
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN7 ON dbo.LU_FIN6.FIN6_ID = dbo.LU_FIN7.FIN6_ID INNER JOIN
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN8 ON dbo.LU_FIN7.FIN7_ID = dbo.LU_FIN8.FIN7_ID INNER JOIN
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN9 ON dbo.LU_FIN8.FIN8_ID = dbo.LU_FIN9.FIN8_ID INNER JOIN
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : dbo.LU_FIN10_small ON dbo.LU_FIN9.FIN9_ID = dbo.LU_FIN10_small.FIN9_ID
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : ORDER BY dbo.LU_FIN10_small.FIN_ID
    2007/09/05 16:42:24 - Dimensiju izveide - ERROR (version 2.4.0, build 725 from 2007/01/25 09:53:29) : The executeQuery method must return a result set.

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Problem is in the "into lu_fin10"... that's not supported. It's not a kettle error

    And please use 2.5.1

    Regards,
    Sven
    Last edited by sboden; 09-05-2007 at 01:48 PM.

  3. #3

    Default

    I've just tested another "select into" statement with version 2.5.1. - the same error. Does it means that "select into" statement is not supported? It is quite common statement I guess...

    Another strange thing is that actually query is executed and table is created...

    2007/09/05 17:45:29 - Chef - Starting job...
    2007/09/05 17:45:29 - Thread[tst (tst (Thread-3)),5,main] - Sleeping: 0 minutes
    2007/09/05 17:45:29 - tst - Starting entry [SQL 1]
    2007/09/05 17:45:50 - SQL 1 - ERROR (version 2.5.1, build 1 from 2007/09/05 17:44:16) : An error occurred executing this job entry :
    2007/09/05 17:45:50 - SQL 1 - ERROR (version 2.5.1, build 1 from 2007/09/05 17:44:16) : An error occurred executing SQL:
    2007/09/05 17:45:50 - SQL 1 - ERROR (version 2.5.1, build 1 from 2007/09/05 17:44:16) : select * into tmp_test from fact_acc
    2007/09/05 17:45:50 - SQL 1 - ERROR (version 2.5.1, build 1 from 2007/09/05 17:44:16) : The executeQuery method must return a result set.
    2007/09/05 17:45:50 - tst - Finished jobentry [SQL 1] (result=false)
    2007/09/05 17:45:50 - Chef - Job has ended.
    2007/09/05 17:47:07 - Spoon - edit job graph entry: SQL 1
    Last edited by kroma; 09-05-2007 at 10:53 AM.

  4. #4
    Join Date
    Jul 2007
    Posts
    2,498

    Default

    What did you expect? Select into doesn't return nothing... returning stuff is the first of the main goal of an EXTRACT-transform-load application
    Pedro Alves
    Meet us on ##pentaho, a FreeNode irc channel

  5. #5

    Default

    This is confusing... actually I just want to perform some maintenance tasks for the warehouse like drop unused tables, create dimension tables (with select into) etc. I don't expect any output from this JOB step (it is not a transformation, but job). Are there any other JOB step I can use to run maintenance SQL scripts?

    by the way "drop table" also doesn't return anything, but it is run without errors... and as I mentioned before this sql is working well directly in the SQl Server, so it is either Kettle or JDBC driver's problem.

    Regards,

    Romans

  6. #6
    Join Date
    May 2006
    Posts
    4,882

    Default

    Retry

    in Kettle you have input and output steps. What you want to do is make a transformation that reads from a table and then writes to another... no into

    Regards,
    Sven
    Last edited by sboden; 09-05-2007 at 01:47 PM.

  7. #7

    Default

    just in case anybody will face the same problem in the future workaround is following: create stored procedure with all your sql scripts and put the procedure name in the Kettle "SQL" job step - it works!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.