Collation confusion - Database collation is not used?
My host provider set the default collation on my SQL database to SQLLatin1GeneralCP1CIAS. This gives wrong sorting for Swedish texts so I asked them to switch. I tried sorting in a random local database where I had database collation Latin1GeneralCIAI. As it gave correct sorting I requested the host provider to apply that collation.
As you probably guess it made no difference. I guess these collations are more or less identical. The reason I got correct sorting when trying locally was that the table column had swedish sorting!
I felt a bit ashamed to bother the support team again, so I now tried to switch the collation of the table columns. No problem to change and sorting in Sql Server Management Studio was now correct!
BUT, when opening the Umbraco page where I retrieved and sorted the data it turned out that the sorting was still wrong ...!
How can that be? Does Umbraco data retrieval do some other trick to apply different sorting?
I repeat:
The database has wrong collation.
The table columns cmsDocument.text and umbracoNode.text now has correct sorting.
When sorting directly in SQL it gives correct sorting.
When applying OrderBy in Umbraco on node Name field it is still wrong
Collation confusion - Database collation is not used?
My host provider set the default collation on my SQL database to SQLLatin1GeneralCP1CIAS. This gives wrong sorting for Swedish texts so I asked them to switch. I tried sorting in a random local database where I had database collation Latin1GeneralCIAI. As it gave correct sorting I requested the host provider to apply that collation.
As you probably guess it made no difference. I guess these collations are more or less identical. The reason I got correct sorting when trying locally was that the table column had swedish sorting!
I felt a bit ashamed to bother the support team again, so I now tried to switch the collation of the table columns. No problem to change and sorting in Sql Server Management Studio was now correct!
BUT, when opening the Umbraco page where I retrieved and sorted the data it turned out that the sorting was still wrong ...!
How can that be? Does Umbraco data retrieval do some other trick to apply different sorting?
I repeat:
This really surprised me, but it seems that Umbraco sorting is sometimes decided by the culture chosen in the backend!!
I assumed the Language setting named "Edit Culture" would switch the displayed language in the backend and not affect the frontend at all.
Can it be that collections taken from the internal cache are sorted according to the Language setting?
is working on a reply...