Post reply

collation conflict with function that returns nvarchar

  • April 16, 2009 at 3:07 pm

    #130709

    I just converted to Sql 2005 and am now getting a collation conflict on the following:

    fnx_TitleCase(lastname) + ',' + master.dbo.fnx_TitleCase(firstname) as FullName

    fnx_TitleCase converts a name to 1st character uppercase, then remaining chars to lowercase.

    I have tried using CAST and CONVERT for the comma but i am still getting a 'collation conflict' error.

    If I don't use the function, fnx_TitleCase, then it works ok.

    Any thoughts would be very much appreciated.

  • April 16, 2009 at 3:13 pm

    #979265

    Hi

    You could try:

    fnx_TitleCase(lastname) + ',' COLLATE YOUR_SERVER_COLLATION + master.dbo.fnx_TitleCase(firstname) as FullName

    Anyway, if your master has another collation than your database it seems that the server does. You should consider to reinstall with correct collation if possible.

    Greets

    Flo

  • April 16, 2009 at 3:18 pm

    #979270

    Yes, I used COLLATE DATABASE_DEFAULT and the error went away.

    So there is definatelly a collation issue with the two different databases.

    Thanks a lot for your help.

  • April 16, 2009 at 3:31 pm

    #979278

    Hi

    If you want to use the database default collation you have to use on the function, not on the ",":

    fnx_TitleCase(lastname) COLLATE YOUR_SERVER_COLLATION + ',' + master.dbo.fnx_TitleCase(firstname) COLLATE YOUR_SERVER_COLLATION as FullName

    My other post specified the server collation. 😉

    Greets

    Flo

Viewing 4 posts - 1 through 4 (of 4 total)

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