5

I'm having problems with our MSSQL database set to any of the Turkish Collations. Becuase of the "Turkish I" problem, none of our queries containing an 'i' in them are working correctly. For example, if we have a table called "Unit" with a column "UnitID" defined in that case, the query "select unitid from unit" no longer works because the lower case "i" in "id" differs from the defined capital I in "UnitID". The error message would read "Invalid column name 'unitid'."

I know that this is occurring because in Turkish, the letter i and I are seen as different letters. However, I am not sure as to how to fix this problem? It is not an option to go through all 1900 SPs in the DB and correct the casing of the "i"s.

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

Greg
  • 8,695
  • 6
  • 45
  • 89
Madeleine
  • 2,067
  • 2
  • 23
  • 35
  • Can you post a link to a page that describes the "turkish i problem"? – Tomalak Apr 23 '09 at 08:09
  • 2
    @Tomalak, take a look at this page : http://www.moserware.com/2008/02/does-your-code-pass-turkey-test.html – Çağdaş Tekin Apr 23 '09 at 08:16
  • I know that page. But I'm not sure if you are doing the right thing. The "Turkey test" is about parsing data, but your problem description sounds like you use a Turkish i in the SQL query text - which you should not do in any case. – Tomalak Apr 23 '09 at 08:55
  • Our front end app parses data correctly, the problem is in the SQL stored procs that dont use the turkish 'I' they use the english 'i' and 'I' but when using the Turkish collation it tries to differentiate between lower case i and upper case I because they're seen as different letters... Hope i'm explaining myself properly? Thanks! – Madeleine Apr 23 '09 at 09:08
  • As far as I understand the problem is that he has a column named "UnitID" and the procedures that use "unitid" fails because in Turkish I and i are different characters. And he's asking how he can fix this without editing the stored procedures. – Çağdaş Tekin Apr 23 '09 at 10:02
  • 2
    Turkish got 2 "i" characters. "ı" = "I" and "i"="İ" that's why this problem is happening. As you see lowercase I is uppercase for "ı" not for "i". – dr. evil Apr 23 '09 at 10:14

7 Answers7

5

Turns out that the best solution was to in fact refactor all SQL and the code.

In the last few days I've written a refactoring app to fix up all Stored procs, functions, views, tablenames to be consistent and use the correct casing eg:

select unitid from dbo.unit

would be changed to

select UnitId from dbo.Unit

The app also then goes through the code and replaces any occurrences of the stored proc and its parameters and corrects them to match the case defined in the DB. All datatables in the app are set to invariant locale (thanks to FXCop for pointing out all the datatables..), this prevents the calls from within code having to be case sensitive.

If anyone would like the app or any advice on the process you can contact me on dotnetvixen@gmail.com.

Eric
  • 87,154
  • 48
  • 211
  • 332
Madeleine
  • 2,067
  • 2
  • 23
  • 35
1

I developed so many systems with Turkish support and this is well known problem as you said.

Best practice to do change your database settings to UTF-8, and that's it. It should solve the all problem.

You might run into problems if you want to support case-sensitivity in (ı-I,i-İ) that can be a problematic to support in SQL Server. If the whole entrance is from Web ensure that is UTF-8 as well.

If you keep your Web UTF-8 input and SQL Server settings as UTF-8 everything should goes smoothly.

dr. evil
  • 25,988
  • 28
  • 126
  • 198
0

Changing the Regional Settings of your machine to English(US) completely saves the day!

Mahmut C
  • 408
  • 4
  • 6
0

Perhaps I don't understand the problem here, but is this not more likely because the database is case sensitive and your query is not? For example, on Sybase I can do the following:

USE master
GO
EXEC sp_server_info 16
GO

Which tells me that my database is case-insensitive:

attribute_id   attribute_name     attribute_value 
          16   IDENTIFIER_CASE    MIXED
ninesided
  • 22,501
  • 13
  • 78
  • 106
  • Thanks for the comment. I have set it to case-insensitive but sadly in the Turkish language a small i and big I are actually seen as different characters altogether so its not a casing issue! Thanks a lot – Madeleine Apr 23 '09 at 08:55
0

If you can change the collation that you're using then try the Invariant locale. But make sure you don't impact other things like customer names and addresses. If a customer is accustomed to having case insensitive searching for their own name, they won't like it if ı and I stop being equivalent, or if i and İ stop being equivalent.

Windows programmer
  • 7,583
  • 20
  • 22
0

Can you change the database collation to the default: this will leave all your text columns with the Turkish colllation?

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

There are some gotchas with temp tables and table variables with varchar columns: you'll have to add COLLATE clauses to these

gbn
  • 394,550
  • 75
  • 549
  • 647
  • Unfortunately all our varchar columns are set to database_default so if the collation is changed to a normal latin option then the varchar columns will use latin. I'll try run a script to set the collation to turkish on all the varchar columns and set the db collation to latin and see what happens! Its a good idea although not very general as ideally we'd like the DB to work on any case insensitive collation! Thanks for the response – Madeleine Apr 23 '09 at 08:58
  • Changing the DB collation (ALTER DATABASE) should leave all the text columns as they are. It only affects system tables and the default – gbn Apr 23 '09 at 09:53
  • tried it out and does do the trick. Ran a script to update all column collation to turkish, then set DB collation to normal latin collation and things appeared to work. Ended up refactoring the code as can be seen in my answer as this solution isnt very maintainable. But definitely works in the short term. Thanks – Madeleine Apr 27 '09 at 19:43
0

I realize you don't want to go through all the stored procedures to fix the issue but maybe you'd be OK to use a refactoring tool to solve the problem. I say take a look at SQL Refactor. I didn't use it but looks promising.

Çağdaş Tekin
  • 16,322
  • 4
  • 46
  • 58
  • This looks like the best suggestion. The original poster said that fixing 1900 stored procedures isn't an option but keeping 1900 stored procedures broken isn't a good option. – Windows programmer Apr 26 '09 at 23:05