Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Add sequence step cant find a sequence

  1. #1
    Join Date
    Feb 2015
    Posts
    19

    Default Add sequence step cant find a sequence

    Hi,
    I am trying to use an 'Add Sequence' step from the DB.
    The DBMS is SqlServer 2016
    The connection works for other transforms and is using 'Ms SQL Server (Native) (JDBC) ,
    The Schema name can be entered or selected from the 'Schemas' button but the 'Sequences..' button remains greyed out.
    The sequence is in a schema that has 'Update' granted to this user.

    If I type the sequence name manually then when running it gives an error
    2017/01/25 16:00:08 - Add sequence.0 - ERROR (version 5.4.0.1-130, build 1 from 2015-06-14_12-34-55 by buildguy) : Because of an error, this step can't continue:
    2017/01/25 16:00:08 - Add sequence.0 - Error reading next value of sequence [dds_seq] from database
    2017/01/25 16:00:08 - Add sequence.0 -
    2017/01/25 16:00:08 - Add sequence.0 - Unable to get next value for sequence : "DATA".dds_seq
    2017/01/25 16:00:08 - Add sequence.0 - The statement did not return a result set.

    I have tried lots of variations with square brackets etc and it ends up the same.

    I see this problem has been reported before - years ago - and with different DBMSs but no 'solution' was shown.

    Any ideas ?
    Thanks in advance
    JC

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    It might help to use a qualified name for the sequence like schema.sequence or "schema"."sequence" or whatever keeps MSSQL happy.
    It seems that schemas are retrieved but never used besides in the drop-down list of the settings dialog.
    Good luck.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Feb 2015
    Posts
    19

    Default

    Thanks marabu - but no luck.

    Why is the 'Sequences' button greyed out ?

    In the native database tool the following 3 syntaxes work
    select next value for [Data].dds_seq;
    select next value for Data.dds_seq;
    select next value for [Data].[dds_seq];

    but not
    select next value for [Data.dds_seq];

    I have tried any number of combinations in the add sequence step - even the last resort of "Data].[dds_seq"
    From the log message I cant tell what its actually generating. for example the simple sequence name =Data.dds_seq
    gives error of
    2017/01/30 10:09:09 - Add sequence.0 - Error reading next value of sequence [Data.dds_seq] from database
    2017/01/30 10:09:09 - Add sequence.0 - Unable to get next value for sequence : "Data.dds_seq"
    where the first syntax is invalid.

    Thats what led me to try Data].[dds_seq which gives error
    2017/01/30 10:12:46 - Add sequence.0 - Error reading next value of sequence [Data].[dds_seq] from database
    2017/01/30 10:12:46 - Add sequence.0 - Unable to get next value for sequence : "Data].[dds_seq"

    How can I tell what its actually trying to issue to the DBMS?
    How can I be sure the JDBC thing handles it ?

    Thanks again
    JC

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by jc508 View Post
    Why is the 'Sequences' button greyed out ?
    I had to look at the source code to find out, that your version (5.4) doesn't support sequences with MSSQL.
    Support was introduced with version 6.1.
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Feb 2015
    Posts
    19

    Default

    Ah! that explains it then
    Looks like I cant postpone the upgrade any longer

    Thanks
    JC

Tags for this Thread

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.