Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Post Unanswered: Collation Conflict on sysname?? Really?

    Okely dokely, here it is.

    I have a database that has a differing collation to that of the tempdb. And obviously because of this I've run into problems when referencing table variables and temp tables.

    BUT! Given the following example, what am I doing wrong, or is there no solution to this.

    <Start Example>



    /*================================================= ================================================== ==========================
    fnPM_ForeignKey
    ================================================== ================================================== =========================*/

    IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnPM_ForeignKey]')) BEGIN
    DROP FUNCTION [dbo].[fnPM_ForeignKey]
    END
    GO

    CREATE FUNCTION fnPM_ForeignKey (@ChildTable as sysname, @ChildColumn as sysname)

    RETURNS @ForeignKey TABLE (
    FKName sysname,
    ParentTable sysname,
    ParentColumn sysname,
    DescriptionColumn sysname COLLATE SQL_Latin1_General_CP1_CI_AS
    )
    AS
    BEGIN

    INSERT INTO @ForeignKey (FKName, ParentTable, ParentColumn)
    SELECT FK.Name, Parent.Name, ParentCol.Name
    FROM sysforeignkeys
    INNER JOIN sysobjects FK on sysforeignkeys.constid = FK.ID
    INNER JOIN sysobjects Parent on sysforeignkeys.rkeyid = Parent.id
    INNER JOIN syscolumns ParentCol on ParentCol.id = Parent.id and sysforeignkeys.rkey = ParentCol.colid
    INNER JOIN sysobjects Child on sysforeignkeys.fkeyid = Child.id
    INNER JOIN syscolumns ChildCol on ChildCol.id = Child.id and sysforeignkeys.fkey = ChildCol.colid
    WHERE Child.Name = @ChildTable and ChildCol.Name = @ChildColumn

    --> UPDATE @ForeignKey Set DescriptionColumn = syscolumns.Name COLLATE SQL_Latin1_General_CP1_CI_AS
    FROM @ForeignKey ForeignKey
    INNER JOIN sysobjects on ForeignKey.ParentTable = sysobjects.Name
    INNER JOIN syscolumns on syscolumns.id = sysobjects.id
    WHERE syscolumns.Name like '%Name%'

    UPDATE @ForeignKey Set DescriptionColumn = syscolumns.Name
    FROM @ForeignKey ForeignKey
    INNER JOIN sysobjects on ForeignKey.ParentTable = sysobjects.Name
    INNER JOIN syscolumns on syscolumns.id = sysobjects.id
    WHERE ForeignKey.DescriptionColumn is null and syscolumns.Name like '%Description%'

    RETURN

    END
    GO

    < end example >

    So here I'm defining my function which basically returns the parent table details of a given foreign key relationship. But when trying to run the above script I get this,

    Server: Msg 446, Level 16, State 9, Procedure fnPM_ForeignKey, Line 22
    Cannot resolve collation conflict for equal to operation.

    First up I find this weird because isn't sysname equvalent to nvarchar(128)? And aren't nvarchar fields independant of collation? But it gets better,

    I thought, ok maybe I'm wrong about sysname needing collation, so I figured I'll check the collation of the syscolumns.name column,

    Heres the query

    select sysobjects.Name, syscolumns.name, syscolumns.collation
    from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id
    where sysobjects.name ='syscolumns' and syscolumns.name = 'name'

    This is what it returns,

    syscolumns name SQL_Latin1_General_CP1_CI_AS

    So, let me get this straight, I've explicitly stated that the table variable column is collation SQL_Latin1_General_CP1_CI_AS and the column I'm comparing it to also has a collation of SQL_Latin1_General_CP1_CI_AS, and it doesn't work. . . .

    I smell Microsoft, or is there something equally as hideous going on here.

    Regards

    Adam.
    Last edited by mrsquish; 11-09-06 at 01:17.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Under SQL 2000, a table variable is collated the same as tempdb. Under SQL 2005, table variables (and temp tables) are collated the same as the current DB context. In order to fix this, just add a collate statement to any join involving the table variable, and a system table. (in the above statement, you have the collate statement on the assignment)

    The collation is part code page, and mainly sort order. The code page part is the only part that nvarchar, nchar and ntext are "immune" to. They are still bound by the sort order and comparison rules. In a case sensitive or binary collation, "A" does not equal "a".

    Clear as mud?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •