April 22, 2009 at 4:40 am
this issue is killing me...
yesterday i was working on my application it had a simple select statement but it wasn't working well because as it turns out my database is case sensitive..i researched the issue and found out that because my collation is case sensitive...so i created another database with the following collation:"SQL_Latin1_General_CP1256_CI_AS" which is case insensitive and i copied my data and recreated my application..it took me alot of time and when i was done i tested it and guess what!!! still case sensitive...
for testing purposes i created a test table and queried it with deferent cases and it was insensitive..but when i query the first table...it is case sensitive
i checked the collation of the table itself and it is SQL_Latin1_General_CP1256_CI_AS so what can be the issue...
April 22, 2009 at 4:57 am
Though at the database level your collation is Case Insensitive , if the instance level default collation is Latin1_General_CS_AI which makes all the objects in database case sensitive.
This is the observed behaviour from SMS query editor tool, not sure the default setting when editor connects to database has something defined in it which causes this.
Do you see this behaviour when you run test query from ODBC connection to database as well ?
Lets see what the experts have to say.
-Sat
Cheer Satish 🙂
April 22, 2009 at 5:23 am
Please ignore my last post , it works fine.
ALTER DATABASE TESTV
COLLATE SQL_Latin1_General_CP1256_CI_AS
USE TESTV
select * from table1
Though table1 is in uppercase during creation
worked fine for me.
Cheer Satish 🙂
April 22, 2009 at 6:57 am
nope still same problem:angry: man this is killing me i have alot of work to do and i want to make sure there no more database creation and copiying before i go on i did the following
created this query and executed it succesfully ....
alter database gis COLLATE SQL_Latin1_General_CP1256_CI_AS
and in 1 table it is working fine in the other it isnt
April 22, 2009 at 9:40 pm
You've specified the 1256 code page, which is the Arabic case-insensitive code page. Keep in mind that you can set the collation at the instance level, database level, table level, and column level. If you've overridden your database settings at the table or column level, changing it at the database level will not result in the change you expect.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
April 22, 2009 at 9:54 pm
How did you create the table in the test database? I'm guessing you used the "select into" method, and so let the table be created in the background. However, it will have created the character fields in the destination table with the same collation as the source table (the same trap I have fallen for).
What you need to do is to script out the table, remove any "collate" clauses from the resultant script (or even better change the collation to force it to be exactly what you want it to be) then use the "insert into select from" statement to transfer the data.
April 23, 2009 at 3:52 am
this is weird when i right click on my table and see wrong the collation it is right but when i saw the script it was the wrong collation and i fixed it now thanks guys
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply