Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: updating SQL server table by GUID

  1. #1
    Join Date
    Jun 2007
    Posts
    10

    Default updating SQL server table by GUID

    I am reading data from SQL server that includes uniqueidentifier (GUID) datatypes. But when I read the field from the table, it comes in as a "STRING" datatype.

    Unfortunately, if you want to do a transformation and then UPDATE the table, you get a syntax error because the field is actually a uniqueidentifier data type - NOT String.

    I can't believe no one has encountered this before - or maybe no one is using Kettle with MS SQL server (I'm using the 2000 version).

    Help!

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    LOL. I can't believe a lot of the things people are trying to do with Kettle ;-)

  3. #3

    Default

    Un-tested but I think this would work, not around my SQL Server machine at the moment, but this should give you a start:

    CREATE FUNCTION f_StringToGUID(
    @pID varchar(36)
    ) RETURNS uniqueidentifier
    AS
    BEGIN
    DECLARE @guidID uniqueidentifier
    SELECT @guidID = CAST(@pID as uniqueidentifier)

    RETURN @guidID
    END


    Then use that function in your transfromation, wish I had SQL Server here to test it for ya. HTH!

  4. #4

    Default

    Matt, we are having some training in Houston, TX with my company Rome Corporation from Jan 7th-11th 2008, are you coming or is some other smart person?

    Regards,

    -Marc Pike
    Professional Services Group
    ROME Corporation
    +1 713-965-0505 | office
    +1 713-965-9567 | fax
    +1 713-292-4278 | cell
    mpike@romecorp.com
    www.romecorp.com

    Turning Risk into Opportunity

  5. #5
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Hi Marc,

    It's not going to be me. You need a smart person for these trainings ;-)

    Seriously, it's probably going to be Bryan as he's from Texas.
    However, since these PDI courses are getting very popular, it could also be Nick, Jens, Dave ...

    From a personal note I still find it completely amazing how fast Pentaho, the community, the software and the number of customers is growing.
    It would have been impossible to do all of it alone and I'm proud to be part of the team you know ;-)

    Greetings from Orlando,

    Matt

  6. #6
    Join Date
    Jun 2007
    Posts
    10

    Default thanks marc

    I'll give that a try.

    Maybe in a future release Pentaho will be able to recognize the GUID datatype in SQL Server............

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.