Czech collation & sorting & filtering

ichladil

Hello,

I have the NAV2009R2 RTC client. In database I have SQL Czech (CI,AS) collation. Let's say that I have in the table record with Lůšm in the description.

When I search in RTC for @*lusm* I would expect this record to be returned as in czech the s and š should be the same "accent sensitive" variants of s. However the RTC client (or NAV server???) translates this into SQL query

where Description LIKE '%[LlĹ弾Łł][UuÜüÚúŮůŰű][SsŚśŞş][Mm]%'

In [SsŚśŞş] there is no š character and therefore my record is not found.

And now to my questions:

Where are defined the sets of characters that the SQL query generated from RTC client is based on? How may I change it so that the correct "czech" accent sensitive variants of the same character are present?
Alternatively which collation may I use for Czech language so that it works correctly in RTC with "ch" character as well as with accent insensitive search.

Thanks ahead for your answers,
Igor

kine

I think this is good candidate to support request. It is same like when searching in classic for something with 'ch'. If you enable "Match Case", the string is sent in the query as is, thus without problems. But in RTC you do not have this option...

ichladil

Hi Kine,

yes, the Match case is one part of the issue that may sort out the whole problem provided it is in 2009 in RTC client (BTW how does it work in NAV2013 with unicode?).
The other issue I have is that I do not know what to do to force NAV to use correct interpretation in its "accent/case insensitive" interpretation when I search for @*š*. I really do not know where is the character set [SsŚśŞş] in sql like query coming from and what can I do to change it? I just know that I want it to be more like [SsŚśŞşŠš]. I can see these character sets it in service folder in nclsrt.stx files. However even if I change the nclsrt.stx for the Czech one it doesn't work as I would like it to work.

Do you know what it depends on? Is it SQL collation, some file at client, some file at service, something stored in database, some settings on the client (regional ones?), some settings on the server? Or is it some combination?

Thanks ahead for any answers,
Igor

ichladil

I have just tested my issue in NAV2013 and everything seems to work there fine (with nvarchar in place). I have ENU client, ENU server, "wrong" collation (Latin1_General_CS_AS) and NAV 2013 RTC client correctly found everything in case/accent insensitive way. I just hope that MS releases group 2 of NAV2013 soon .

Anyway my question about the NAV2009 RTC searching still stands.

Igor

kine

I assume that after you changed the nclsrt.stx you have restarted the services (stop both, start both).


hmmm... may be.... there is field "chartable" in the [$ndo$dbproperty] table of the database. May be it is somehow playing the role...

jostairs

Hi Igor,

In NAV 2013 we do not use our own hardcoded sets of equivalent characters, and instead use the SQL collate clause, where we specify case and accent insensitivity.

This has been backported to R2 and is available in KB 2701438.

I hope this helps.

Regards,
John