Unusual Collation Error

  • Every night we collect data from the reportserver database and archive it into another database (Utils). The Utils database has a collation of Latin1_General_CI_AS as does the table in the query below.

    Although this isn't a problem as we have resolved the issue, I am intrigued as to why when we run :-

    SELECT [ReportName]

    ,REPLACE([Path] ,[ReportName],'')

    ,[Path]

    ,[NumberofTimesRun]

    ,[LastRun]

    ,[avgDurationSecs]

    FROM [Utils].[dbo].[ReportUsageSummary]

    ORDER BY [Path]

    we get

    Msg 468, Level 16, State 9, Line 1

    Cannot resolve the collation conflict between "Latin1_General_CI_AS_KS_WS" and "Latin1_General_CI_AS" in the replace operation.

    This issue is resolved by the following :

    SELECT [ReportName]

    ,REPLACE([Path] COLLATE Latin1_General_CS_AS_KS_WS,[ReportName],'')

    ,[Path]

    ,[NumberofTimesRun]

    ,[LastRun]

    ,[avgDurationSecs]

    FROM [Utils].[dbo].[ReportUsageSummary]

    ORDER BY [Path]

    Can anyone explain why we get this error when only one table is queried?

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • OK - I've answered my own question.

    Although the database and table was set to Latin1_General_CI_AS one of the columns used was set to Latin1_General_CI_AS_KS_WS - hence the issue

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

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

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