Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Continuous error on failed Execute SQL Step

  1. #1

    Default Continuous error on failed Execute SQL Step

    Hi,

    I have a transform that reads a file then does the usual merge/insert step type processing based on key fields. After this I need to update a counter for each row which I do using an ExecuteSQL Step.

    This is a SQL Server database we're connecting to and sometimes as I'm fiddling about my transaction log is getting full. When this occurs on the ExecuteSQL step the errors being output never seem to stop. I left it processing as there are nearly 4000 rows in the file to see if I was getting one for every row and whether it would eventually stop, but I hit a Java Heap Space error before that happened.
    I have attached the top of the error log created by the transform, the entire file is 85M.

    I was wondering if there is any way I can handle this, this transform is part of a nightly load job that processes many files and if the Heap Space goes due to an error on the Execute SQL Step the process is going to halt with no feedback to the user to say why the job has failed. I guess they'd also need to kill the job?

    I'm using 3.0.0 GA Build 500

    Cheers,

    Nicole
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2007
    Posts
    216

    Smile

    Hi,

    Quote Originally Posted by stevens_ns View Post
    (...)
    I was wondering if there is any way I can handle this, this transform is part of a nightly load job that processes many files and if the Heap Space goes(...)
    Did you try to
    Code:
    (...)find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.database
    like specified in your log ?

    a+, =)
    -=Clément=-

  3. #3

    Default

    Hi Clement,

    Yes I know why the log write fails, its needs backup as it can't shrink.

    My question was more to do with the transform itself and why the Execute SQL Step seems to behave this way when other steps like the Insert/Update do not.

    One question I did have, are the transactional properties of the Execute SQL Step different to those of the other steps? This simple update fails but others which do not use the Execute SQL Step seem to finish successfully and I can't imagine they're creating any less log data.

    Thanks for your reply,

    Nicole

  4. #4
    Join Date
    Mar 2007
    Posts
    216

    Smile

    Hi,

    Quote Originally Posted by stevens_ns View Post
    (...)My question was more to do with the transform itself and why the Execute SQL Step seems to behave this way when other steps like the Insert/Update do not.
    I don't know the answer to these questions.
    I can just give you a workaround to try with.
    It consists in an Add constants step which add a field MyOffset=1 to each rows then a Calculator step which does newaddress_id=address_id+MyOffset, then drop address_id and MyOffset and rename newaddress_it to address_id...
    In french we call it "using a bazooka to kill a fly" but that's all I can do for you.
    You can find the sample attached to this post.

    a+, =)
    -=Clément=-
    Attached Files Attached Files

  5. #5

    Smile

    Hi Clement

    I didn't quite do it that way but your suggestion fired off another idea which is working well, so thanks alot!

    Nicole

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.