January 20, 2010 at 2:38 am
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
January 20, 2010 at 4:10 am
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