Problems with Turkish SQL Collation (Turkish "I")

Question

I am having problems with our MSSQL database installed in any of the Turkish collisions. Of course, the problem is "Turkish I", none of our queries containing "i" in them works correctly. For example, if we have a table called “Unit” with the “UnitID” column indicated in this case, the query “select unitid from unit” no longer works because the lowercase “i” in “id” is different from the given capital "UnitID". The error message will say "Invalid column name" unitid "."

I know that this is because in Turkish the letters I and I are treated as different letters. However, I am not sure how to solve this problem? It is not possible to go through all 1900 SPs in the database and fix the "i" shell.

Any help would be appreciated, even suggestions of other comparisons that could be used instead of Turkish, but would support their character set.

Answer 1

It turns out that the best solution was actually refactoring all of the SQL and code.

In the last few days, I wrote a refactoring application to capture all stored processes, functions, views, table names so that they are consistent, and use the correct body, for example:

select unitid from dbo.unit 

will be changed to

 select UnitId from dbo.Unit 

Then the application also looks at the code and replaces any occurrences of the stored proc and its parameters and corrects them in accordance with the case defined in the database. All datatables in the application are set to an invariant locale (thanks to FXCop for specifying all data types ..), this prevents calls from hanging inside the code.

If anyone likes the application or any advice on the process, you can contact me at [e-mail].

Answer 2

Perhaps I do not understand the problem here, but is it not more likely because the database is case sensitive and your query is not? For example, in Sybase, I can do the following:

 USE master GO EXEC sp_server_info 16 GO 

Which tells me that my database is not case sensitive:

 attribute_id attribute_name attribute_value 16 IDENTIFIER_CASE MIXED 

Answer 3

If you can change the sorting you are using, try the invariant locale. But make sure that you do not affect other things, such as customer names and addresses. If the client is used to the fact that the case-insensitive register is looking for its own name, it will not like it if ı and I stop being equivalent, or if I and İ stop being equivalent.

Answer 4

Can you change the database setting to the default value: will this leave all columns of text with Turkish colllation?

Queries will work, but the data will behave correctly. In theory...

There are some gotchas with temporary tables and table variables with varchar columns: you will have to add COLLATE clauses to these

Answer 5

I understand that you do not want to go through all the stored procedures to fix this problem, but it may be useful for you to use a refactoring tool to solve the problem. I say look SQL Refactor . I did not use it, but it looked promising.

Answer 6

I have developed so many systems with Turkey support, and this is a well-known issue, as you said.

It's best to change the database settings to UTF-8, and that is. He must solve the whole problem.

You may have problems if you want to maintain case sensitivity in (ı-I, i-İ), which can be problematic for support in SQL Server. If all input from the Web is also provided by UTF-8.

If you keep your UTF-8 login and SQL Server settings as UTF-8, everything should go smoothly.

Answer 7

Changing the regional settings of your device to English (USA) completely saves the day!