Post reply

New-Features SQL 2008

  • January 20, 2009 at 1:47 pm

    #204392

    hi people, i'm studied sql server 2008, and i've code:

    CREATE PROC dbo.usp_AddEmp

    @empid AS INT,

    @mgrid AS INT = NULL,

    @empname AS VARCHAR(25),

    @salary AS MONEY

    AS

    DECLARE

    @hid AS HIERARCHYID,

    @mgr_hid AS HIERARCHYID,

    @last_child_hid AS HIERARCHYID;

    IF @mgrid IS NULL

    SET @hid = HIERARCHYID::GetRoot();

    ELSE

    BEGIN

    SET @mgr_hid = (SELECT hid FROM dbo.Employees WHERE empid = @mgrid);

    SET @last_child_hid =

    (SELECT MAX(hid) FROM dbo.Employees

    WHERE hid.GetAncestor(1) = @mgr_hid);

    SET @hid = @mgr_hid.GetDescendant(@last_child_hid, NULL);

    END

    but been error, look:

    Msg 243, Level 16, State 4, Procedure usp_AddEmp, Line 12

    Type HIERARCHYID is not a defined system type.

    create table dbo.Employees

    (

    hid hierarchyid primary key ,

    empid INT,

    mgrid INT,

    empname varchar(25),

    salary money

    )

    anybody know problem is what?

    []'s

    Lucas Souza

  • January 20, 2009 at 2:01 pm

    #929960

    Is your database case sensitive? What's the DB collation?

    I know there are some strange behaviours around hierarchyid in case sensitive databases.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 21, 2009 at 6:58 am

    #930295

    There fore, What's command for view my collation? i'm do know...

  • January 21, 2009 at 7:24 am

    #930325

    It's in the database properties. From object explorer, right click the database and select properties.

    It's also probably available with one of the DATABASEPROPERTYEX options

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 21, 2009 at 2:24 pm

    #930720

    ok, is Latin1_General_CS_AS

    What´s Collation specified for used date type hierarchyID?

    thanks a lot!

  • January 21, 2009 at 2:45 pm

    #930733

    I don't have SQL Server 2008 (yet), but could the problem be the following:

    SET @hid = HIERARCHYID::GetRoot();

    If I counted right this would be line 12.

  • January 21, 2009 at 5:40 pm

    #930807

    good, thanks a lot! 🙂

  • January 22, 2009 at 12:10 am

    #930902

    lucassouzace (1/21/2009)

    ok, is Latin1_General_CS_AS

    Right. That's case sensitive (that's what the CS shows)

    For some strange reason, the hierarchyid data type is case sensitive in some places in a case sensitive database. Take the line that Lynn found for you and play with the case. Maybe all small case, maybe caps first letter and ID. I don't actually know which one's right and I don't have a case sensitive DB to play in

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 8, 2009 at 5:57 pm

    #974784

    Thanks, this helped my issue too...

    The original poster needed

    SET @hid = hierarchyid::GetRoot();

    in a case-sensitive database

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply