Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Unusual Collation Error Expand / Collapse
Author
Message
Posted Wednesday, January 20, 2010 2:38 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 3,009, Visits: 3,200
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
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
Post #850316
Posted Wednesday, January 20, 2010 4:10 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 3,009, Visits: 3,200
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
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
Post #850356
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse