Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Problem with (I thought) simple data move

  1. #1
    Join Date
    Jul 2012
    Posts
    3

    Default Problem with (I thought) simple data move

    Hello,

    I'm moving data between SQL Server Express and SQL Server 2005 using the Table Input - Table Output - Update method with the Update being the error handling step for the Table Output, but am having major problems with inserting any new records into the SQL Server 2005 table.

    The update works great, but the insert just won't function.

    I created the target table using a script generated from SQL Server Express so I know they match and I removed the identity on the PK column in the target table. I have a feeling I'm missing something ridiculously simple, but I've spent too much time trying to troubleshoot on my own.

    I have also tried the same using the Insert-Update with the same result.

    Any direction/guidance is appreciated.

    Thanks.

  2. #2
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi,

    are there unique indexes on the target tables? Or any other constraints?

    Otherwise: More details (e.g. error-message) might be helpful to investigate you issue.

    Cheers,

    Tom

  3. #3
    Join Date
    Jul 2012
    Posts
    3

    Default

    Both tables have an ID that is indexed, the source table has foreign key constraints, but not the target table and the update process creates an index as part of it's SQL.

    Weird thing is, there are no error messages that I can see in the logging, even with detail turned on.

    I was just hoping someone had run into this problem before and might have a suggestion.

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

    Default

    You don't see error messages, because there are no unexpected errors, I guess.
    Rows that can't be inserted in the target table are written to the error channel, where you can pick them up with your Update step.
    I would prefer the Insert / Update step over the Table Output / Update combo, just to avoid the failing insertions.
    Don't look for error messages, watch the error count in the step metrics, to get an idea of what's happening.

    BTW: I tested your scenario (Update step fed by the error channel) with MySQL and had no problems.

    You can enable tracing for the MS JDBC Type 4 driver: http://msdn.microsoft.com/en-us/library/ms378517.aspx
    Might give you some clues...
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi,

    also, to enable some more output:
    You can exchange the "javaw" executable with just "java" (inside the spoon.bat/sh)
    By doing this, a command-window will stay opened during running Spoon and might display additional output...

    Cheers,

    Tom

  6. #6
    Join Date
    Jul 2012
    Posts
    3

    Default

    Thank you. I'll give the tracing a try.

    I was just going the Output/Update combo option due to the theoretical speed increases, but I get the same result with the Insert/Update step.

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.